[Laskey99] Section 11.2. Oracle Tablespaces

11.2.

The tablespace is a logical construct used by Oracle to manage disk space. Tablespaces store various 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 operating system files through the use of the CREATE TABLESPACE command. Every Oracle database consists of at least one tablespace, called SYSTEM.

When a tablespace is created, the names of the operating system files that will make up that tablespace are specified. For example, suppose that 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 created in /disk05/oracle/oradata. This file will have a size of 50 megabytes and will be initialized by Oracle into Oracle blocks. The size of each Oracle block is determined by the parameter DB_BLOCK_SIZE, which is specified at the time the database is created. This is one of the only database parameters that cannot be changed, so take care when specifying the blocksize.

Once the tablespace is created, all access to the underlying datafiles is controlled by Oracle. No user has any reason to read from or write to Oracle datafiles, so file protections should be set so that only the Oracle owner account has access to these files. In a Unix environment, Oracle datafiles should be owned by the account "oracle", assigned to group DBA, and have their protection mode set to 700.

11.2.1. Oracle Blocks

When a datafile is assigned to a tablespace, Oracle formats the file into blocks for the actual storage of data. The size of each Oracle block is determined by the DB_BLOCK_SIZE parameter, which is specified when the database is created. The Oracle block is the basic unit of storage into which all Oracle objects (tables, indexes, rollback segments, etc.) are stored.

The Oracle block is divided into three distinct segments:

• The fixed block header

• The variable block header

• The data storage area

Figure 11.2 illustrates the structure of an Oracle block.

Figure 11.2. Oracle block structure

11.2.1.1. Fixed block header

The fixed block header will be exactly the same size for every block of a table, regardless of the Oracle blocksize. The size of the fixed block header will be:

57 + (23 × INITRANS)

where INITRANS is the value specified for the INITRANS parameter in the CREATE TABLE statement when the table is created (the default value is 1).

11.2.1.2. Variable block header

The variable block header immediately follows the fixed block header in the block, and is dependent on the number of rows stored in the block. The size of the variable block header may be calculated as:

4 + (2 × number of rows in the block)

The initial 4 bytes contain the table directory, and there is a 2-byte row directory entry for each stored row.

As you can see, the fixed portion of the block header remains a constant 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 the remainder of the block than will fewer, larger rows. In addition, since the fixed portion is indeed fixed, better utilization of blocks can be expected with larger blocksizes. For example, a database having an Oracle blocksize of 2048 (2K) will lose about 3.9% of each block (57 + 23 bytes) to the fixed block header, while a database having an Oracle blocksize of 8192 (8K) will lose only .97% of each block. This is one way in which larger Oracle blocksizes may be more efficient.

11.2.1.3. Data storage area

The balance of the block is used for data storage—for example, to store the actual rows of a table. The calculation of the available storage in each block is not straightforward, since it is dependent on several factors, including:

• Oracle blocksize (DB_BLOCK_SIZE)

• Percent free space (PCTFREE)

• Average row length

• Number of rows stored per block

The average row length can be estimated as:

3 bytes row header

+ 1 byte per non-LONG column

+ 3 bytes per LONG column

+ average length of all table columns

The header space per row can be calculated as:

3 + (number of non-LONG columns) +

3 × (number of LONG columns)

The number of rows per block can then be calculated as:

((blocksize - (57 + 23 × INITRANS))

- (blocksize - (57 + 23 × INITRANS))

× (PCTFREE/100) - 4 - 2 × rows per block)

/ (average row length + header space per row)

Finally, the available space in the block can be calculated as:

(blocksize - (57 + 23 × INITRANS))

- ((blocksize - (57 + 23 × INITRANS)) × PCTFREE × 100)

- 4 - 2 × rows per block

Even this description simplifies the calculations a bit, since we do not take into account the possibility of trailing NULLs, LONG strings, and so on, which may have an impact on the exact calculation.

11.2.2. Storage Allocation Parameters

As you can see from the formulas presented above, the utilization of space within an Oracle block is complex. One major factor in the use of this space is the value set for PCTFREE. This parameter, which is normally set when an object is created but can be modified later, controls how much empty space is reserved in each block when a row is inserted. PCTFREE is specified as a percentage of the data storage area of a block reserved for future updates to rows in that block.

For example, a PCTFREE value of 10 would reserve 10% of the space remaining in a block after allocation of block headers for updates (note that this is not 10% of the total blocksize). This space is used by Oracle to allow rows stored in the block to grow in size. If there is not enough space left in a block to contain all of the data for an updated row, Oracle must allocate an additional block to hold the overflow. This is called row chaining , and should be avoided, since multiple, non-sequential I/Os will be required to read a single row of data. Of course, it is possible to have a single row, or even a single column, that is larger than the available space in a block. In this situation, block chaining is inevitable, although the DBA might consider a larger DB_BLOCK_SIZE, if warranted.

11.2.3. How Oracle Allocates Storage

Although Oracle stores rows of data in Oracle blocks, which exist in the context of a particular operating system file, the DBA has little control over which particular block is used for a particular database object. However, the DBA does control which tablespace an object is created in and how much space within that tablespace is allocated to the object.

When creating an object in the database, the CREATE statement usually specifies the tablespace in which the object will be stored (see the TABLESPACE clause of the CREATE commands in Chapter 13). If a TABLESPACE clause is not included in the CREATE command, the default tablespace for that user will be used for object creation.

As a DBA, you should be careful always to specify a DEFAULT TABLESPACE for each user, since Oracle will assign the SYSTEM tablespace as the default if you omit the specification, and in most cases you will not want non-system objects created in the SYSTEM tablespace.

Oracle allocates storage in logical units called extents . An extent is simply an amount of storage that is rounded up to the next 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 (the INITIAL extent) and the amount of space to be allocated when the INITIAL allocation is used and more space is required (the NEXT extent). A typical CREATE command contains a STORAGE clause in the following format:

STORAGE (INITIAL i  NEXT n  MINEXTENTS m  PCTINCREASE p)

The following sections examine each parameter of the STORAGE clause for this statement.

11.2.3.1. INITIAL

The INITIAL parameter in the STORAGE clause determines how much space will be allocated to the first extent when an object is created. This parameter may be specified in bytes, kilobytes, or megabytes. For example, the following all specify the same amount of storage to be allocated to the INITIAL extent:

INITIAL 1048576
INITIAL 1024K
INITIAL 1M

The default value for INITIAL is the INITIAL value established in the DEFAULT STORAGE for the tablespace in which the object is being created.

Extent sizes should be specified as integer multiples of the Oracle blocksize; otherwise, the allocation is rounded up to the next Oracle block. For example, with a 2K (2048) blocksize, a request for INITIAL 4097 results in three Oracle blocks being allocated.

11.2.3.2. NEXT

The NEXT parameter in the STORAGE clause determines how much space will be allocated for the second and subsequent extents allocated for an object. The NEXT parameter is specified in the same way as the INITIAL parameter. Although this parameter may be set to a specific value, that value may be modified dynamically if the PCTINCREASE parameter is set to a non-zero value. The default value for NEXT is the NEXT value established in the DEFAULT STORAGE for the tablespace in which the object is being created.

11.2.3.3. MINEXTENTS

The MINEXTENTS parameter in the STORAGE clause determines how many storage extents will be allocated to the object at the time of initial creation. Typically, this parameter is set to 1, but if it is set to a value greater than 1, the second and subsequent extents will use the NEXT parameter and will be subject to dynamic modification if PCTINCREASE is set to a non-zero value. The default value for MINEXTENTS is the MINEXTENTS value established in the DEFAULT STORAGE for the tablespace in which the object is being created.

11.2.3.4. PCTINCREASE

The PCTINCREASE parameter in the STORAGE clause determines the degree to which Oracle will automatically increase the size of subsequent extent allocations. This value is expressed as an integer percentage, and is applied to the then-current value of the NEXT parameter. For example, if an object is created with INITIAL 81920, NEXT 81920, and PCTINCREASE 10, this means that after each new extent is allocated, Oracle will dynamically increase the value of the NEXT parameter for that object by 10%. Table 11.1 illustrates the situation in a database with a 2048-byte blocksize.

Table 11.1. How Oracle Allocates Extents

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 will not automatically coalesce smaller but contiguous extents back into larger extents in that tablespace. As a result, Oracle may be unable to allocate a particular extent even when sufficient contiguous blocks are available. Setting PCTINCREASE to a value of 1 will overcome this problem.

11.2.4. A Space Allocation Example

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:

1. Oracle will look in the free space pool for 8K of space to assign as the INITIAL extent for the table. Assuming a database with a 4K blocksize, two blocks would be allocated.

2. After providing for the block headers, 10% of the remaining space would be reserved for growth of data rows, as indicated by the PCTFREE 10 parameter.

3. 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).

4. When the third block is filled, additional space is required. This time Oracle allocates two blocks, since the current value of NEXT is 4506, and this value is rounded up to the next Oracle block.

11.2.5. Object Storage Sizing

One particularly important task for the DBA is to properly size the storage of objects in the database. This sizing primarily deals with the number and size of extents occupied. There is a widely held myth that multiple extents have a negative effect on performance; however, this is not necessarily the case. In fact, multiple extents, when properly planned and sized in a Parallel Query environment, can dramatically improve the overall performance of the database.

11.2.5.1. Free lists

When an object (for example, a table) is created, one or more Oracle blocks are allocated to that object; as we've mentioned, each allocation is called an extent. Storage is allocated according to the current STORAGE parameters. Oracle maintains a list of blocks available in each tablespace called the free block list. As blocks are added to a table, either through the allocation of an additional extent or by deleting data from an existing block, they are added to the endof the free block list. Since Oracle allocates data to blocks by searching for a free block starting at the beginning of the free block list, these newly freed or allocated blocks will be the last blocks used.

11.2.5.2. The highwater mark

For each object, Oracle also maintains a record of the highest relative block of the table used to hold data. This highwater mark is maintained in multiples of five blocks and is not reset unless the TRUNCATE command is executed.

When Oracle performs operations requiring a full table scan, such as SELECT COUNT(*), all blocks up to and including the highwater mark are read. If a table is created with 50,000 rows occupying 10,000 blocks, and those rows are subsequently deleted, the highwater mark will remain at 10,000, and a SELECT COUNT(*) command will read all 10,000 blocks even though they are all empty.

An even worse scenario is possible. Suppose that a table contains 50,000 rows, and the first 49,000 rows are then deleted. The blocks corresponding to the deleted data are placed at the end of the free block list. When the next INSERT statement is executed, Oracle finds the first block on the free block list, which is beyond the highwater mark. The effect is that all the free space (49,000 rows worth) is ignored, and the physical table becomes bigger. Full table scans and other similar operations still have to read all the empty blocks, and performance is significantly impacted. If you use SQL*Loader with the direct path option, these loads always begin at the highwater mark, so the table size may grow while leaving significant amounts of free space unused.

To easily determine the current value of the highwater mark, use the following formula after analyzing the table:

highwater mark = total blocks - empty blocks - 1

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 highwater mark) can be obtained with this query:

SELECT empty_blocks
FROM dba_tables
WHERE owner='&Owner'
AND segment_name='Tablename';