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

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.

Design to control Oracle Row Chaining and Row Migration

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 in the chain_cnt column of the dba_tables view. Note that you must analyze all of the tables in the Oracle database with the analyze table xxx estimate statistics command before you can see this value.

Once you have identified those tables with 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.

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

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.