In this article I will discuss the tuning of individual objects within the Oracle database. This article will address several table and index storage parameters

Oracle Objects:

Inside Oracle, an object can be either a table or an index, and the parameters that are used when creating tables and indexes can have a dramatic impact on the amount of I/O within the Oracle database. 

Determining Unlinked Block:

The PCTFREE 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.

Determining Block Re-linking:

The PCTUSED 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.

Defining Tables and Indexes:

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.

PCTUSED – Do or Don't:

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.

Storage Parameters:

The storage parameters for Oracle tables and indexes can have an important effect on the performance of the database.

PCTUSED and PCTFREE for Database Performance:

The settings for PCTUSED can have a dramatic impact on the performance of an Oracle database. But many new Oracle DBAs fail to realize that PCTUSED is only used to re-link full data onto the table free list. A re-link occurs only when a DELETE or UPDATE statement has reduced the free space in the data block. The setting for PCTUSED will determine the amount of row space in this newly re-linked data block.

PCTUSED for Space Utilization:

The default setting for all Oracle tables is PCTUSED=40. This setting means that a block must become less than 40 percent full before being re-linked on the table free list. The value for PCTUSED should be set above 40 only when the database is short on disk space and it must make efficient reuse of data block space.

Customizing Values:

It should now be very clear that the average row length needs to be considered when customizing the values for PCTFREE and PCTUSED. You want to set PCTFREE such that room is left on each block for row expansion, and you want to set PCTUSED so that newly linked blocks have enough room to accept rows.

Space Usage vs. Performance:

There is a trade-off between effective space usage and performance. If you set PCTUSED to a high value, say 80, then a block will quickly become available to accept new rows, but it will not have room for a lot of rows before it becomes logically full again. In the most extreme case, a re-linked free block may have only enough space for single rows before causing another I/O operation.

The lower the value for PCTUSED, the less I/O your system will have at INSERT time and the faster your system will run. The downside, of course, is that a block will be nearly empty before it becomes eligible to accept new rows. Because row length is a major factor in intelligently setting PCTUSED, a script can be written that allows the DBA to specifically control how many rows will fit onto a reused data block before it unlinks from the free list.

Buffer Busy Waits:

In systems where multiple tasks are concurrently inserting or deleting records from an Oracle database, it is not uncommon to see "buffer busy waits" within the database engine. A buffer busy wait is a condition where Oracle is waiting to access the segment header block for the table. When multiple tasks are attempting to simultaneously insert information into an Oracle table, tasks will have to wait their turn to get access to the segment header.

Free Lists for Database Performance:

Any time buffer busy waits occur, the Oracle database administrator must try to find those tables or indexes that are experiencing the segment header contention and increase the freelists or freelist_groups parameters. The freelist_groups parameter allows an Oracle table to have several segment headers, so that multiple tasks can insert into the table. The setting for the FREELISTS parameter should be set equal to the high-water mark of the number of concurrent inserts for the target table.

Database Fragmentation:

Improper settings for PCTFREE and PCTUSED can also cause database fragmentation. Whenever a row in an Oracle database expands because of an update, there must be sufficient room on the data block to hold the expanded row. If a row spans several data blocks, the database must perform additional disk I/O to fetch the block into the SGA. This excessive disk I/O can cripple the performance of the database.

Row Chaining:

Row chaining is relatively easy to detect. Once you have identified those tables with migrated/chained rows, you must increase PCTFREE for the table and then export and reload the table to remove the chains. While there are several third-party products for reorganizing tables, table reorganization is most commonly done by running Oracle export-import utilities.

Efficient Space Reuse:

A high value for PCTUSED will effectively reuse space on data blocks, but at the expense of additional I/O. A high PCTUSED means that relatively full blocks are placed on the free list. Hence, these blocks will be able to accept only a few rows before becoming full again, leading to more I/O.

Better Performance:

 More Database Articles
   Database Security: Step by step guideline
   Beware of Worse Oracle performance after Migration!!
   Boost Oracle Performance by forcing Query Order!!
   Monitoring Oracle Parallel Queries!!
   Adding Custom Messages to Oracle Alert Log!!