Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

Oracle tuning:
Working with individual objects

May 10, 2001
Donald Burleson
2001 TechRepublic, Inc
.

In our previous articles, we've looked at how you tune the overall Oracle instance. Now it's time to start looking at the individual objects within the Oracle database. 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.  This article will address several table and index storage parameters:
 
  • 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 relink 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 relink 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 relinks 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.

The popular Ion tool is the easiest way to analyze Oracle table behavior (average CPU cost per table access shown above)  and Ion allows you to spot hidden table-related performance trends.  Ion is our favorite Oracle tuning tool, and the only 3rd party tool that we use.

 

Examining storage parameters

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 migrated/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 relink the data block onto the free list for the table.
As you can see, the storage parameters for Oracle tables and indexes can have an important effect on the performance of the database. Let's take a look at the common storage parameters that affect Oracle performance.

Setting PCTFREE and PCTUSED

As any experienced DBA understands, 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 relink full data onto the table free list. A relink 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 relinked data block.

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 relinked on the table free list.

Let's take a closer look at how the PCTUSED operator works and how it affects the operation of relinks onto the table free list. As I said, a data block becomes available for reuse when its free space drops below the value of PCTUSED for the table, which triggers a free list relink operation.

There is a trade-off between the setting for PCTUSED and database performance on INSERT operations. In general, the higher the setting for PCTUSED, the less free space will be on reused data blocks at INSERT time. Hence, INSERT tasks will need to do more frequent I/Os than they would if they were inserting into empty blocks. In short, 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.

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.

Herein lies the 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 relinked free block may have only enough space for single rows before causing another I/O operation.

Remember this: 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. We've created such a script, pctused.sql, which appears in this sidebar. It allows you to adjust the setting for PCTFREE and PCTUSED as a function of the number of rows you want to store between I/Os, and it generates the table alteration syntax. It also allows you to define the blocksize and the number of rows you want to leave room for after the block relinks onto the free list.

Note that this script provides only general guidelines; you will want to leave the default PCTUSED=40 unless your system is low on disk space or unless the average row length is very large.

The output file from pctused.lst will contain the ALTER TABLE syntax to reset PCTUSED to an appropriate value.

Now let's take a close look at free lists and see how a free list shortage can cause performance slowdowns.

Free list management for Oracle objects
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. As you may know, Oracle reserves the first block in a table (the segment header) to keep control information, including the header link for a list of free blocks 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.

In sum, 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.

Managing row chaining in Oracle

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.

The space reserved for row expansion is controlled by the PCTFREE parameter. Row chaining is especially problematic in cases where a row with many VARCHAR datatypes is stored with NULL values, and subsequent update operations populate the VARCHAR columns with large values.

Fortunately, row chaining is relatively easy to detect. We've created a script that will run against the Oracle data dictionary and produce a report showing those tables with excessive migrated/chained rows. Note that you must analyze all of the tables in the Oracle database with the analyze table xxx estimate statistics command before running this script.

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.

Tips for object tuning

For efficient space reuse, you want to set a high value for PCTUSED. 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.

For better performance you want to set a low value for PCTUSED. A low value for PCTUSED means that Oracle will not place a data block onto the free list until it is nearly empty. The block will be able to accept many rows until it becomes full, thereby reducing I/O at insert time. Remember that it is always faster for Oracle to extend into new blocks than to reuse existing blocks. For superfast space acquisition on SQL INSERTs, you can turn off free list link/unlinks. It takes fewer resources for Oracle to extend a table than to manage free lists.

In effect, free lists can be "turned off" by setting PCTUSED to 1. This will cause the free lists to be populated exclusively from new extents. Of course, this approach requires lots of extra disk space, and the table must be reorganized periodically to reclaim space within the table.

Let's review the general guidelines for setting object storage parameters.
  • Always set PCTUSED to allow enough room to accept a new row. We never want to have free blocks that do not have enough room to accept a row. If we do, this will cause a slowdown since Oracle will attempt to read five "dead" free blocks before extending the table to get an empty block.
  • The presence of migrated/chained rows in a table means that PCTFREE is too low or that DB_BLOCK_SIZE is too small. In most cases within Oracle, RAW and LONG RAW columns make huge rows that exceed the maximum blocksize for Oracle, making migrated/chained rows unavoidable.
  • If a table has simultaneous INSERT SQL processes, it needs to have simultaneous DELETE processes. Running a single purge job will place all of the free blocks on only one free list, and none of the other free lists will contain any free blocks from the purge.
  • The FREELISTS parameter should be set to the high-water mark of updates to a table. For example, if the customer table has up to 20 end users performing INSERTs at any time, then the customer table should have FREELISTS=20.
  • FREELIST GROUPS should be set to the number of Oracle Parallel Server instances that access the table.  For partitioned objects and cases of segment header contention, freelist_groups my be set for non-RAC systems.
Now that we understand how to tune Oracle objects, we're almost ready to wind up our series on Oracle tuning. In the final installment, we'll show how Oracle experts tune SQL statements, often with huge performance gains.


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational