The tablespace is a logicalconstruct used by Oracle to manage disk space. Tablespaces storevarious types of objects, including:
Tables
Indexes
Stored procedures, functions, and packages (in the SYSTEM tablespace)
Rollback segments
Temporary segments
A tablespace is constructed by Oracle from one or more operatingsystem files through the use of the CREATE TABLESPACE command. EveryOracle database consists of at least one tablespace, calledSYSTEM.
When a tablespace is created, the names of the operating system filesthat will make up that tablespace are specified. For example, supposethat the following command is executed in a Unix environment:
CREATE TABLESPACE user_data
DATAFILE '/disk05/oracle/oradata/userdata01.dbf' SIZE 50M;
A datafile called userdata01.dbf will be createdin /disk05/oracle/oradata. This file will have asize of 50 megabytes and will be initialized by Oracle into Oracleblocks. The size of each Oracle block is determined by the parameterDB_BLOCK_SIZE, which is specified atthe time the database is created. This is one of the only databaseparameters that cannot be changed, so take care when specifying theblocksize.
Once the tablespace is created, all access to the underlyingdatafiles is controlled by Oracle. No user has any reason to readfrom or write to Oracle datafiles, so file protections should be setso that only the Oracle owner account has access to these files. In aUnix environment, Oracle datafiles should be owned by the account"oracle", assigned to group DBA, and have theirprotection mode set to 700.
When adatafile is assigned to a tablespace, Oracle formats the file intoblocks for the actual storage of data. The sizeof each Oracle block is determined by the DB_BLOCK_SIZE parameter, which isspecified when the database is created. The Oracle block is the basicunit of storage into which all Oracle objects (tables, indexes,rollback segments, etc.) are stored.
The Oracle block is divided into three distinct segments:
Figure 11.2 illustrates the structure of an Oracleblock.
The fixedblock header will be exactly the same size for every block of atable, regardless of the Oracle blocksize. The size of the fixedblock header will be:
where INITRANS is the value specified for theINITRANS parameter in the CREATETABLE statement when the table is created (the default value is 1).
The variable block header immediately followsthe fixed block header in the block, and is dependent on the numberof rows stored in the block. The size of the variable block headermay be calculated as:
The initial 4 bytes contain the table directory, and there is a2-byte row directory entry for each stored row.
As you can see, the fixed portion of the block header remains aconstant size for every block of a particular table, and, in reality,for most tables, unless INITRANS is specified. The variable portion,however, is dynamic, and depends on the number of rows stored. More,smaller rows in a table will result in less available space in theremainder of the block than will fewer, larger rows. In addition,since the fixed portion is indeed fixed, better utilization of blockscan be expected with larger blocksizes. For example, a databasehaving an Oracle blocksize of 2048 (2K) will lose about 3.9% of eachblock (57 + 23 bytes) to the fixed block header, while a databasehaving an Oracle blocksize of 8192 (8K) will lose only .97% of eachblock. This is one way in which larger Oracle blocksizes may be moreefficient.
The balance of the block isused for data storage—for example, to store the actual rows ofa table. The calculation of the available storage in each block isnot straightforward, since it is dependent on several factors,including:
Number of rows stored per block
The average row length can be estimated as:
The header space per row can be calculated as:
The number of rows per block can then be calculated as:
Finally, the available space in the block can be calculated as:
Even this description simplifies the calculations a bit, since we donot take into account the possibility of trailing NULLs, LONGstrings, and so on, which may have an impact on the exactcalculation.
As you can see from the formulaspresented above, the utilization of space within an Oracle block iscomplex. One major factor in the use of this space is the value setfor PCTFREE. This parameter, which isnormally set when an object is created but can be modified later,controls how much empty space is reserved in each block when a row isinserted. PCTFREE is specified as a percentage of the data storagearea of a block reserved for future updates to rows in that block.
For example, a PCTFREE value of 10 would reserve 10% of the spaceremaining in a block after allocation of block headers for updates(note that this is not 10% of the total blocksize). This space isused by Oracle to allow rows stored in the block to grow in size. Ifthere is not enough space left in a block to contain all of the datafor an updated row, Oracle must allocate an additional block to holdthe overflow. This is called rowchaining, and should be avoided, sincemultiple, non-sequential I/Os will be required to read a single rowof data. Of course, it is possible to have a single row, or even asingle column, that is larger than the available space in a block. Inthis situation, block chaining is inevitable, although the DBA mightconsider a larger DB_BLOCK_SIZE, if warranted.
AlthoughOracle stores rows of data in Oracle blocks, which exist in thecontext of a particular operating system file, the DBA has littlecontrol over which particular block is used for a particular databaseobject. However, the DBA does control which tablespace an object iscreated in and how much space within that tablespace is allocated tothe object.
When creating an object in the database, the CREATEstatement usually specifies the tablespace in which the object willbe stored (see the TABLESPACE clause of the CREATE commands in Chapter 13). If a TABLESPACE clause is not included inthe CREATE command, the default tablespace for that user will be usedfor object creation.
Oracle allocates storage in logical units calledextents.An extent is simply an amount of storage that is rounded up to thenext multiple of the Oracle blocksize. When an object is created,that object has associated with it, either explicitly or implicitly,the amount of storage to be allocated upon object creation (theINITIAL extent) and the amount of space to be allocated when theINITIAL allocation is used and more space is required (the NEXTextent). A typical CREATE command contains a STORAGE clause in thefollowing format:
STORAGE (INITIAL i NEXT n MINEXTENTS m PCTINCREASE p)
The following sections examine each parameter of the STORAGE clausefor this statement.
The INITIAL parameter in the STORAGEclause determines how much space will be allocated to the firstextent when an object is created. This parameter may be specified inbytes, kilobytes, or megabytes. For example, the following allspecify the same amount of storage to be allocated to the INITIALextent:
INITIAL 1048576
INITIAL 1024K
INITIAL 1M
The default value for INITIAL is the INITIAL value established in theDEFAULT STORAGE for the tablespace in which the object is beingcreated.
|
The NEXT parameter in the STORAGE clausedetermines how much space will be allocated for the second andsubsequent extents allocated for an object. The NEXT parameter isspecified in the same way as the INITIAL parameter. Although thisparameter may be set to a specific value, that value may be modifieddynamically if the PCTINCREASE parameter is set to a non-zero value.The default value for NEXT is the NEXT value established in theDEFAULT STORAGE for the tablespace in which the object is beingcreated.
The MINEXTENTS parameter in the STORAGEclause determines how many storage extents will be allocated to theobject at the time of initial creation. Typically, this parameter isset to 1, but if it is set to a value greater than 1, the second andsubsequent extents will use the NEXT parameter and will be subject todynamic modification if PCTINCREASE is set to a non-zero value. Thedefault value for MINEXTENTS is the MINEXTENTS value established inthe DEFAULT STORAGE for the tablespace in which the object is beingcreated.
The PCTINCREASEparameter in the STORAGE clause determines the degree to which Oraclewill automatically increase the size of subsequent extentallocations. This value is expressed as an integer percentage, and isapplied to the then-current value of the NEXT parameter. For example,if an object is created with INITIAL 81920, NEXT 81920, andPCTINCREASE 10, this means that after each new extent is allocated,Oracle will dynamically increase the value of the NEXT parameter forthat object by 10%. Table 11.1 illustrates thesituation in a database with a 2048-byte blocksize.
| Extent | Size in Bytes | Size in Blocks | Comment |
|---|---|---|---|
| Initial extent | 81920 | 40 blocks | Uses INITIAL parameter |
| 2nd extent | 81920 | 40 blocks | Uses specified NEXT value |
| 3rd extent | 90112 | 40 blocks | NEXT increased by 10% |
| 4th extent | 100352 | 49 blocks | NEXT increased by another 10% and rounded to next block |
If the default PCTINCREASE for a tablespace is set to 0, Oracle willnot automatically coalesce smaller butcontiguous extents back into larger extents in that tablespace. As aresult, Oracle may be unable to allocate a particular extent evenwhen sufficient contiguous blocks are available. Setting PCTINCREASEto a value of 1 will overcome this problem.
Consider the following SQL statement:
CREATE TABLE dept (
deptno NUMBER(4),
deptname VARCHAR(30)
location VARCHAR(20))
TABLESPACE users
PCTFREE 10
STORAGE (INITIAL 8K NEXT 4K MINEXTENTS 1 PCTINCREASE 10)
Oracle will allocate space for the DEPT table as follows:
Once the INITIAL extent is filled (in this case, both blocks are filled), Oracle looks to the NEXT extent parameter to determine how much additional space to allocate. In this case, a value of 4K is specified. Since this database has a blocksize of 4K, a single block will be allocated and added to this table's space allocation. After this extent is allocated, the value of NEXT will be dynamically increased to 4506 because PCTINCREASE is set to 10 (4096 × 1.10).
One particularlyimportant task for the DBA is to properly size the storage of objectsin the database. This sizing primarily deals with the number and sizeof extents occupied. There is a widely held myth that multipleextents have a negative effect on performance; however, this is notnecessarily the case. In fact, multiple extents, when properlyplanned and sized in a Parallel Query environment, can dramaticallyimprove the overall performance of the database.
When anobject (for example, a table) is created, one or more Oracle blocksare allocated to that object; as we've mentioned, eachallocation is called an extent. Storage is allocated according to thecurrent STORAGE parameters. Oracle maintains a list of blocksavailable in each tablespace called the free blocklist. As blocks are added to a table, either through theallocation of an additional extent or by deleting data from anexisting block, they are added to the endof thefree block list. Since Oracle allocates data to blocks by searchingfor a free block starting at the beginning of the free block list,these newly freed or allocated blocks will be the last blocks used.
For each object, Oracle also maintains a record of the highestrelative block of the table used to hold data. Thishighwater mark is maintained in multiples of five blocksand is not reset unless the TRUNCATE command is executed.
When Oracle performs operations requiring a full table scan, such asSELECTCOUNT(*), all blocks up to and including the highwater mark are read.If a table is created with 50,000 rows occupying 10,000 blocks, andthose rows are subsequently deleted, the highwater mark will remainat 10,000, and a SELECT COUNT(*) command will read all 10,000 blockseven though they are all empty.
An even worse scenario is possible. Suppose that a table contains50,000 rows, and the first 49,000 rows are then deleted. The blockscorresponding to the deleted data are placed at the end of the freeblock list. When the next INSERT statement is executed, Oracle findsthe first block on the free block list, which is beyond the highwatermark. The effect is that all the free space (49,000 rows worth) isignored, and the physical table becomes bigger. Full table scans andother similar operations still have to read all the empty blocks, andperformance is significantly impacted. If you use SQL*Loader with thedirect path option, these loads always begin at the highwater mark,so the table size may grow while leaving significant amounts of freespace unused.
To easily determine the current value of the highwater mark, use thefollowing formula after analyzing the table:
Total blocks for a table can be obtained by using the following query:
SELECT blocks
FROM dba_segments
WHERE owner='&Owner'
AND segment_name='Tablename';
Likewise, the number of empty blocks (blocks above the highwatermark) can be obtained with this query:
SELECT empty_blocks
FROM dba_tables
WHERE owner='&Owner'
AND segment_name='Tablename';
联系客服
