Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Chapter 5 Oracle Tablespace Design

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.


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

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.

Let's take a closer look at how the PCTUSED operator works and how it affects the operation of re-links 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 re-link 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 re-linked 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.

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.  Now let's take a close look at free lists and see how a free list shortage can cause performance slowdowns.

Freelist 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

Got Oracle Scripts?

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 690 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.