||Oracle Tips by Burleson
Chapter 5 Oracle Tablespace
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
Design to control Oracle Row Chaining and
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
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
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
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
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
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
Packed with more than 690 ready-to-use Oracle scripts, this is the
definitive collection for every senior Oracle DBA.
would take man-years to develop these scripts from scratch,
making this download the best value in the Oracle industry.