RelationalDBDesign RelationalDBDesign 

Data Blocks  «Prev

Oracle PCTFREE review and Creating Tables for Good Performance


The PCTFREE table parameter determines when to perform a freelist un-link. This happens when a block becomes full due to SQL INSERTS and the percent full exceeds the PCTFREE parameter. At this point the block is removed from the table's freelist.
If PCTFREE is set to 20, this means that 20% of each data block in this table's data segment will be set aside and free for possible updates to the rows already existing within each block.
New rows can be added to the row data area, and corresponding transaction information can be logged in the variable portions of the block overhead area, until the row data and overhead are 80% of the total block size.

Creating Tables for Good Performance

When you create a segment, such as a table, Oracle allocates space in the database for the data. If subsequent database operations cause the data volume to increase and exceed the space allocated, then Oracle extends the segment. When installing applications, an initial step is to create all necessary tables and indexes. This operation is by itself relatively fast, and not much is gained by doing it in parallel. However, some things require attention:
  1. Using Automatic Segment-Space Management: Oracle automatically manages segment space for best performance.
  2. Setting Storage Options: Applications should carefully set storage options for the intended use of the table or index. This includes setting the value for PCTFREE. (Using automatic segment-space management eliminates the need to specify PCTUSED).
    Note: Use of free lists is no longer encouraged. To use automatic segment-space management, create locally managed tablespaces, with the segment space management clause set to AUTO.
  3. Setting INITRANS: Each datablock has a number of transaction entries that are used for row locking purposes. Initially, this number is specified by the INITRANS parameter, and the default value (1 for tables, 2 for indexes) is generally sufficient. However, if a table or index is known to have many rows for each block with a high possibility of many concurrent updates, then it is beneficial to set a higher value. This must be done at the CREATE TABLE/CREATE INDEX time to ensure that it is set for all blocks of the object.