PCT FREE/USED

This could be a serious issue for the Oracle professional unless they remember that locally-managed tablespaces with automatic space management ignore any specified values for NEXT and FREELISTS.

Before we explore the details of designing with each of these options, it's important to understand the segment storage options and see how they relate to the tablespace options.  Let's start with a review of the segment storage parameters.

Design for Oracle Segment Storage

Since the earliest days of Oracle, we have the following individual segment parameters to manage the growth of each segment (table, index, IOT) within our Oracle database:

• PCTFREE—This storage parameter determines when a block can be unlinked from the free list. You must reserve enough room on each data block for existing rows to expand without chaining onto other blocks. The purpose of PCTFREE is to tell Oracle when to remove a block from the object's free list. Since the Oracle default is PCTFREE=10, blocks remain on the free list while they are less than 90 percent full. Once an insert makes the block grow beyond 90 percent full, it is removed from the free list, leaving 10 percent of the block for row expansion. Furthermore, the data block will remain off the free list even after the space drops below 90 percent. Only after subsequent deletes cause the space to fall below the PCTUSED threshold of 40 percent will Oracle put the block back onto the free list. 
   
  
• PCTUSED—This storage parameter determines when a block can re-link onto the table free list after DELETE operations. Setting a low value for PCTUSED will result in high performance. A higher value of PCTFREE will result in efficient space reuse but will slow performance. As rows are deleted from a table, the database blocks become eligible to accept new rows. This happens when the amount of space in a database block falls below PCTUSED, and a free list re-link operation is triggered. For example, with PCTUSED=60, all database blocks that have less than 60 percent will be on the free list, as well as other blocks that dropped below PCTUSED and have not yet grown to PCTFREE. Once a block deletes a row and becomes less than 60 percent full, the block goes back on the free list. As rows are deleted, data blocks become available when a block's free space drops below the value of PCTUSED for the table, and Oracle re-links the data block onto the free list chain. As the table has rows inserted into it, it will grow until the space on the block exceeds the threshold PCTFREE, at which time the block is unlinked from the free list.
   
  
• FREELISTS—Oracle allows table and indexes to be defined with multiple free lists. All tables and index free lists should be set to the high-water mark of concurrent INSERT or UPDATE activity. Too low a value for free lists will cause poor Oracle performance.
  

There is a direct trade-off between the setting for PCTUSED and efficient use of storage within the Oracle database. For databases where space is tight and storage within the Oracle data files must be reused immediately, the Oracle database administrator will commonly set PCTUSED to a very high value. This ensures the blocks go on the free list before they are completely empty.

However, the downside to this approach is that every time the data block fills, Oracle must unlink the data block from the free list and incur another I/O to get another free data block to insert new rows. In sum, the DBA must strike a balance between efficient space usage and the amount of I/O in the Oracle database.

Let's begin our discussion by introducing the relationship between object storage parameters and performance. Poor object performance within Oracle occurs in several areas:

• Slow INSERTs—INSERT operations run slowly and have excessive I/O. This happens when blocks on the free list have room for only a few rows before Oracle is forced to grab another free block. 
   
  
• Slow SELECTs—SELECT statements have excessive I/O because of chained rows. This occurs when rows "chain" and fragment onto several data blocks, causing additional I/O to fetch the blocks. 
   
  
• Slow UPDATEs—UPDATE statements run very slowly with double the amount of I/O. This happens when updates expand a VARCHAR or BLOB column and Oracle is forced to chain the row contents onto additional data blocks.
   
  
• Slow DELETEs—Large DELETE statements run slowly and cause segment header contention. This happens when rows are deleted and the database must re-link the data block onto the free list for the table.