Oracle SQL Tuning: Setting PCTFREE for Optimal Performance

As we discussed earlier, Oracle makes a direct tradeoff between efficient space usage and high performance. This is particularly true for the PCTFREE parameter, especially as it applies to chained rows. Chained rows occur when, as a result of an UPDATE, the row cannot fit into any available data block.

• Migrated and Chained Rows:
  If you notice poor performance in your Oracle database, two causes might be
  1. row chaining and
  2. migration.
  We can prevent some of them by properly designing and diagnosing the database. 1) Row Migration and 2) Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance. The main considerations are:
  1. What is Row Migration & Row Chaining ?
  2. How to identify Row Migration & Row Chaining ?
  3. How to avoid Row Migration & Row Chaining ?
  Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans. An update or insert always increases row length. The table below describes row migration and chaining.

You can detect migrated or chained rows by checking the number of table fetch continued row statistic in V$SYSSTAT. A small number of chained rows (less than 1%) is unlikely to impact system performance. However, a large percentage of chained rows can affect performance. Chaining on rows larger than the block size is inevitable. You might want to consider using tablespace with larger block size for such data. However, for smaller rows, you can avoid chaining by using sensible space parameters and good application design. For example, do not insert a row with key values filled in and nulls in most other columns, then update that row with the real data, causing the row to grow in size. Rather, insert rows filled with data from the start. If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle tries to find another block with enough free space to hold the entire row.

• Migrating Table Row If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block. This is called chaining a row. Rows can also be chained when they are inserted.Migration and chaining are especially detrimental to performance with the following:
  
  1. UPDATE statements that cause migration and chaining to perform poorly
  2. Queries that select migrated or chained rows because these must perform additional input and output

The definition of a sample output table named CHAINED_ROWS appears in a SQL script available on your distribution medium. The common name of this script is UTLCHN1.SQL, although its exact name and location varies depending on your platform. Your output table must have the same column names, datatypes, and sizes as the CHAINED_ROWS table.Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.

• The Performance Impact of Migrated and Chained Rows:
  PCTFREE determines when to perform a freelist un-link, and so decides whether or not a row will migrate or chain.
  If the data is migrated or chained, it is stored in different database blocks, and must be read from many database blocks to retrieve the row, resulting in many unnecessary I/Os. The following series of images below looks at PCTFREE, row chaining, and row migration.

A migrated row is a row that was moved to another block due to an update which made the row too large to fit on its original block where the other rows already existed. We are unable to "move" the row since we have lots of indexes pointing to the original block. We therefore leave behind a forwarding address on the original block and migrate the updated row to a new block. When you access that row, we discover it is not really there. The row has migrated and we read the other block to get it. A chained row is a row that is too large to fit on a single block. If you have a 2k blocksize but your row size is 4k, we will use 3 blocks to store that row in pieces. Any table with a long raw will have chained rows. Any table whose rowsize exceeds the blocksize will have chained rows. Any table with more then 255 columns will have chained rows (we break really wide tables up).
How do you know if the migrated or chained rows have been fixed?
Repeat the process of ANALYZE TABLE ...
LIST CHAINED ROWS. If new rows are created in the CHAINED_ROWS table then this means you most likely have chained rows (and not migrated rows) and these can only be resolved by moving the table and adjusting PCTFREE to a lower value or moving the table to a tablespace that has a larger block size .
Every row in every table has a physical address. The address of a row is determined from a combination of the following:

1. Datafile number
2. Block number
3. Location of the row within the block
4. Object number

You can display the address of a row in a table by querying the ROWID pseudo-column.
For example:

SQL> select rowid, emp_id from emp;

Here is some sample output:

ROWID EMP_ID
------------------ ----------
AAAEtQAAEAAAACDAAA 100
AAAEtQAAEAAAACDAAB 101

The ROWID pseudo-column value is not physically stored in the database. Oracle calculates its value when you query it. The ROWID contents are displayed as base-64 values that can contain the characters

A-Z, a-z, 0-9, +, and /.

You can translate the ROWID value into meaningful information via the DBMS_ROWID package. For example, to display the file number, block number, and row number in which a row is stored, issue this statement:

select emp_id
,dbms_rowid.rowid_to_absolute_fno(rowid,schema_name=>
'MV_MAINT',object_name=>'EMP') file_num
,dbms_rowid.rowid_block_number(rowid) block_num
,dbms_rowid.rowid_row_number(rowid) row_num
from emp;

Here is some sample output:

EMP_ID FILE_NUM BLOCK_NUM ROW_NUM
---------- ---------- ---------- ----------
100 4 131 0
101 4 131 1

You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the ROWID uniquely identifies a row. However, it is possible to have rows in different tables that are stored in the same cluster and so contain rows with the same ROWID (like with a clustered table).

If PCTFREE is small, the data will fully occupy each database block. As a result, PCTFREE can have an important impact on performance if it is set too low. By planning for row expansion and reserving enough space on the data block, we ensure that minimal row chaining occurs. Because migrating and chaining are very harmful to performance levels, PCTFREE should be set so that they occur infrequently. Now that we understand the importance of the PCTFREE parameter as it applies to chained rows and migrated rows, let us look at setting PCTFREE.

If you seldom use UPDATE and the following conditions apply, then set PCTFREE to zero.

1. Your tables do not contain VARCHAR data type columns.
2. You always store full-sized default values in table columns.
3. You never update a table row.

If these criteria do not describe your tables, then you must set PCTFREE to allow for Oracle to reserve enough room for the rows to expand. Oracle monitors the number of chained rows in a list table statistics.

Oracle lists the following table statistics in the DBA_INDEXES, USER_INDEXES and ALL_INDEXES data dictionary views:

1. Number of rows
2. Number of chained rows
3. Exact number of data blocks storing data
4. Exact number of data blocks allocated, but never used
5. Average row length, including row overhead
6. Average available free space in each data block

Like all other segments, indexes may need to have additional extents created or have extra extents deallocated to free up disk space. Oracle adds extents dynamically for indexes, but it is always better to pre-allocate extents to avoid expensive dynamic extent allocation. Here is the syntax for manually allocating extents:

ALTER INDEX [schema.]index
ALLOCATE EXTENT ([SIZE integer [K|M]]
[DATAFILE ‘filename’])

If the SIZE option were not specified, the size used would be the size of the next extent if it were created automatically. The size of the next extent is stored in the NEXT_EXTENT column of the DBA_INDEXES data dictionary view.
Manually deallocating space enables you to deallocate to a specific size or to deallocate down to the high-water mark of an index. You can never deallocate space below the high-water mark. Reorganizing the index is the only way to lower the high-water mark. Here is the syntax for deallocating extents:

ALTER INDEX [schema.]index
DEALLOCATE UNUSED ([KEEP integer [K|M]])

1. DBA_TAB_MODIFICATIONS
2. ALL_TAB_MODIFICATIONS
3. USER_TAB_MODIFICATIONS

The views listed above describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours).

You can check for chained rows with the ANALYZE command. The ANALYZE command can be used to either populate the chained_rows column of DBA_TABLES, or you can use the LIST CHAINED ROWS option to pipe the output into the CHAINED_ROW table as defined by the utlchain.sql utility. In summary, our goal as a DBA is to ensure that we keep enough space on the data block to ensure that minimal row chaining occurs. The next lesson looks at PCTUSED.