Control of Oracle Data Block Space Usage


The High-Water Mark

The high-water mark is equivalent to the physical file size. The high-water mark in a table is the last block ever used by a table. The high-water mark is not reset when data is deleted. When a full table scan is performed each block is read up to its high-water mark. Therefore if a new table with 100 free blocks has inserts filling 70 blocks followed by deletes freeing 20 blocks then that table's high-water mark is 70 blocks. Thus a full table scan on this table will read 70 blocks even though 20 of those 70 blocks have been deleted.

How Does Row Chaining Occur ?

When a row is updated so that its length increases, exceeding the current block's available free space, the data for the entire row moves, or migrates, to a new block; if the entire row can fit into the new block. If the entire row cannot fit into a new block then the row is split into multiple blocks, this is row chaining, ie. chaining of a single row between multiple blocks. Row Chaining as a result of row migration failure reduces I/O performance since Oracle must scan both the original and any other blocks to retrieve the data in the row. Note that Row Chaining is somewhat unavoidable. The result is a chain of linked data blocks.

Row chaining is caused when a row is updated and there is insufficient space available to insert the data. Row migration often happens with VARCHAR2 and NUMBER types which can vary in size; more likely with VARCHAR2. RAW, LONG and LONG RAW datatypes can also cause row chaining since they can vary greatly in size. However, VARCHAR2 is the most likely cause of row chaining since it is usually the most widely used variable by a great degree. Row chaining causes multiple disk reads for a single row. This disk I/O increase can cause extreme performance degradation. Setting PCTFREE low can reduce or eliminate row chaining and reduce storage. The most appropriate settings for PCTFREE and PCTUSED are determined by the situation. Note that the sum of PCTFREE and PCTUSED must be close to 100 otherwise an increase or decrease in one may have no affect on the other.

Correct Settings for PCTFREE and PCTUSED

When creating objects the DBA can control block space usage by specifying values for PCTFREE and PCTUSED.

When in doubt, opt for higher PCTFREE and PCTUSED values because lower values can cause performance problems.

Some Example Scenarios

The sum of PCTFREE and PCTUSED may not exceed 100. A high PCTUSED value can result in more efficient space utilisation but higher overhead since more work must be done to maintain the free block list. A high PCTFREE and low PCTUSED will result in more unused space in a block and inefficient storage. Once improperly specified, the solution is rebuilding of affected tables or clusters.

For optimum performance the combined value of PCTFREE and PCTUSED should be 100 - the percentage of block space occupied by the 2 or three most average rows in the table. If the average row in a table requires 10% of the block space then the sum of PCTFREE and PCTUSED should be between 70% and 80%. In general performance will suffer when the sum of PCTFREE and PCTUSED gets close to 100%.

PCTUSED

The amount of used space decreases due either to deletions or updates that decrease column value lengths. When occupied space is less than or equal to 39% new rows can once again be inserted into the block. PCTUSED is defaulted to 40%.

A Data Block - PCTUSED

PCTFREE

The value of PCTFREE specifies the amount of free space reserved for updates to rows already stored in a block. For instance, if PCTFREE = 20, rows may be inserted until the block is 80% full. The remaining 20% is reserved for updates to existing rows within the block. PCTFREE is defaulted to 10%.

A Data Block - PCTFREE

Block Space Requirements for Different Object Types

Read-Only and Static Objects

Block space is used differently by read-only objects in relation to objects with high amounts of insert, update and deletion activity requirements. Read-only objects are never altered thus most if not all of the space within a block in which a read-only object is stored can be utilised thus making the most of storage and minimising searching across blocks. Particularly for read-only data, PCTUSED can be set very high and PCTFREE can be set very low.

Static objects are objects which remain the same once having been created in the database. A static object is an object which is not changed very often.

Volatile Objects

Object volatility will affect the usage of block space greatly. Objects subject to large numbers of inserts, updates and deletes are volatile objects. Therefore the more volatile an object is in terms of updates and deletes then the lower PCTUSED should be set. In the case of PCTFREE set PCTFREE to a higher value depending on the numbers of record insertions.

The Parts of a Data Block

A Data Block is the smallest unit of storage in a database. The block format is similar regardless of the type of object the data block contains, a table, cluster, index, etc.

Fixed Header

The Fixed Header space contains general block information, eg. block address and segment type. The fixed block header is fixed in size. The size varies by segment type.

A Data Block - Fixed Header

Table Directory

The Table Directory space area stores information about the tables and rows stored in the block. Only clusters allow two or more tables to share a block.

A Data Block - Table Directory

Row Directory

The Row Directory area contains information about all rows in the block. Each row requires two bytes of overhead in this area. The row directory grows as rows are added to the block.

A Data Block - Row Directory

Free Space

The Free Space area is the amount of space available either for insertion of new rows into the block or updates to existing rows.

A Data Block - Free Space

Row Data

The Row Data space contains table or index data.

A Data Block - Row Data

Calculating Correct Settings for PCTFREE and PCTUSED

Calculating PCTFREE

PCTFREE represents the percentage of each data block which is reserved as free space. This space is used when a row that has already been stored in that data block grows in lingth, either by updates of previously NULL fields or by updates of existing values to longer values. PCTFREE controls the number of records which are stored in a table block.

Rows Per Block

Determine the number of rows in a block - use the ANALYZE command to determine the number of rows in a block in an existing table. In the following example the ANALYZE command will compute statistics and then the results must be retrieved from the database as shown below.

ANALYZE TABLE <table_name> COMPUTE STATISTICS;
SELECT num_rows, blocks, num_rows / blocks FROM USER_TABLES WHERE TABLE_NAME = <table_name>;

In the SQL SELECT statement above the num_rows / blocks gives the rows per block.

The number of rows per block may change between database updates. This may increase row record length or fill null values. This increase in record lengths can potentially move rows to new blocks. If rows are moved to new blocks then PCTFREE is not set to a high enough value. This is because when the space a row occupies in a block is not adequate for a row length change then row migration or chaining will occur. Row chaining occurs because there is not enough free space in the block for the change. Therefore row chaining can occur as a direct result of too little free block space. Therefore increase the value of PCTFREE to inhibit the amount of row chaining.

Average Bytes Free Per Block

SELECT avg_space FROM USER_TABLES WHERE TABLE_NAME = <table_name>;

If the value of avg_space is high then this means that there is a lot of wasted space in the blocks of the table analysed. Therefore PCTFREE is probably set to too high a value and its percentage should be decreased in order to provide for less free space per block since not enough space is utilised in each block.

Below are some of the columns in the USER_TABLES view. These columns can be of use in assessing the most efficient value of PCTFREE for a specific table.

describe user_tables;
Column Name                    Null?    Type
------------------------------ -------- ----
TABLE_NAME                     NOT NULL VARCHAR2(30)
PCT_FREE                                NUMBER
NUM_ROWS                                NUMBER
BLOCKS                                  NUMBER
EMPTY_BLOCKS                            NUMBER
AVG_SPACE                               NUMBER
CHAIN_CNT                               NUMBER
AVG_ROW_LEN                             NUMBER
LAST_ANALYZED                           DATE

Note that another method is by use of the ANALYZE command as shown below. It is possible to partially remove chained rows from a table by moving the chained rows to a temporary table, deleting them from the original table and then copying them back into the origianl table from the temporary table. Another method is changing the block size which requires database recreation and export plus import. This is a drastic measure and is best avoided by careful planning in the first place.

ANALYZE table name LIST CHAINED ROWS;
SELECT * FROM chained_rows WHERE table_name = 'table name';

PCTUSED

PCTUSED determines when a block is to be added to the list of blocks into which rows can once again be inserted. When rows are deleted from a block the free space in the block will not be reused until the block's used space falls below the PCTUSED percentage. The default for PCTUSED is 40% and for PCTFREE is 10%. Setting of PCTUSED and PCTFREE involves a process of trial and error and an experienced Oracle DBA. Many different texts state different combinations of values. Obviously the sum of the two should not exceed 100%. For an OLTP type database set PCTUSED to (95 - PCTFREE). Thus the default settings for PCTFREE and PCTUSED of 10% and 40% respectively makes no sense.

Setting INITRANS and MAXTRANS

INITTRANS and MAXTRANS determine the number of transactions allowed concurrently on a block at one time. This means that the number of sessions or users accessing accessing a single block at the same time can be controlled using these parameters. Effectively one can determine the number of locks which can be placed on a block. Thus space is reserved in each block regardless of the settings of PCTFREE and PCTUSED in order to somehow control locking. Unfortunately the Oracle manuals never seem to really explain how this works and why it exists. Does it mean that for each transaction accessing a block that another image of that data is created ? Surely this could lead to extensive overflow and row chaining ? What role to rollback segments play in this ? With the lack of explanation as to exactly what INITRANS and MAXTRANS control it may be best to leave these values at their default values.

INITRANS

Reserve space for INITRANS SQL-DML transactions ?

MAXTRANS

MAXTRANS places a limit on the number of transactions which can simultaneously access data in a block. Thus space allocatable to a transaction in a block can be limited. This is possibly to avoid transactions in the same block from overwriting each other.