||Oracle Tips by Burleson
Chapter 5 Oracle Tablespace
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
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
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
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
In sum, any time buffer busy waits occur, the Oracle database
administrator must try to find those tables or
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.