PCTFREE property for tables and indexes

Why databases aren’t really fast

When most Oracle databases are inspected to see exactly what they are doing, most often it is disk I/O access. Databases read and write a lot of data, and because magnetic drive technology is slow, often the database spends a majority of its time waiting for disk read/write requests to complete.

How Oracle manages data

The main way that Oracle manages data (in datafiles), is using blocks. Each block is read, and is written, as a single object; Oracle does not read or write partial blocks. This means that if a tablespace’s block size is set at 8k (8192 bytes), whenever information is required to be read or written, it will do it in a number of 8k chunks.

The PCTFREE property of a segment

There is a property of a table or index that can be set, that will force Oracle to pack more rows into each block. This can have a very dramatic affect on the throughput of read and write requests, because if more rows of data are packed into each block, the database requires fewer read requests to satisfy the requirements of a query.

Oracle has provided a way to control how full a block becomes before it starts to use a new block to store rows of data; this is called thePCTFREE parameter. You can see the current value for tables by inspecting dba_tables.pct_free, or dba_indexes.pct_free. ThePCTFREE parameter is set at table creation time, which will affect all rows inserted or changed in that table. If a given table/index’sPCTFREE parameter is changed after it’s creation, no existing blocks are changed, so the existing data in the table is not packed tighter.

By default, Oracle uses a value of 10 for PCTFREE. This means that it will add data to a block until it is 90% full; after this it will create a new block to hold information. If you set PCTFREE to 1, then Oracle will fill up blocks to 99% full, before using another block; This allows you to store more data in fewer blocks. If you set PCTFREE to 99, then Oracle will fill up blocks to 1% full before using another block. Usually this has the general effect of causing each row to go into a single block.

Using a low PCTFREE

Low PCTFREE effects for reads

For reads, especially scans (including full table scans), it almost always will reduce the amount of physical reads required to satisfy a read-only query (ie a select SQL statement).

Low PCTFREE for data changes

However, for row updates, this can really hurt performance, because if a field is changed in a row (via an update statement), and this requires a larger row size than is free in an existing block, then the database needs to ’split’ the row among 2 or more blocks. This is called ‘row chaining’, and can definitely affect performance adversely when those rows are read, as >1 block will need to be retrieved.

For inserts, having a low PCTFREE will cause them to go faster, as more rows will be inserted for each block written.

Note that if an index is built with a low pctfree, it may take longer for the index to be updated when corresponding rows are updated.

Low PCTFREE applications

The ideal place to use a very low value for PCTFREE include:

• Log tables that are never updated (only inserts, and deletes in rough order of inserts)
• auditing tables which should never be updated,
• Tables used to support Data warehouses, esp if rows are not updated,

Table examples where you probably should not use a very low value for PCTFREE include:

• Tables where the rowsize expands over the life of a row via many updates,
• Tables that have a lot of active transactions going against them,
• Tables where inserts and deletes happen that are not in the order of inserts
• Tables which have lots of updates in general.

Using a high PCTFREE

Above the discussion has been about having a particularly low value for PCTFREE. There are cases where a higher than default value forPCTFREE is recommended. If it is known that a rowsize will be very small upon the 1st insert, and then updated a lot (say to swell from 20 bytes to 16k bytes), and especially if the table had a high transaction rate, then forcing Oracle to store fewer rows (upon insert) will really minimize row chaining. If a table will be updating an in-row (B/C)LOB (again increasing row-length) having a low value for PCTFREE again would increase performance.

Conclusion

If you’re interested in changing your PCTFREE, try to of course test it first in a development or QA environment. The default value (10) is for many cases quite satisfactory. Using the wrong one can really hurt performance, and sometimes this happens a long time after rows are inserted.

By Jay Stanley, Sr. Staff Consultant