||Oracle Tips by Burleson
Chapter 5 Oracle Tablespace
This could be a serious issue for the Oracle
professional unless they remember that locally-managed tablespaces
with automatic space management ignore any specified values for NEXT
Before we explore the details of designing with
each of these options, it’s important to understand the segment
storage options and see how they relate to the tablespace options.
Let’s start with a review of the segment storage parameters.
Design for Oracle Segment
Since the earliest days of Oracle, we have the
following individual segment parameters to manage the growth of each
segment (table, index, IOT) within our Oracle database:
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 re-link 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
re-link 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 re-links 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.
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
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 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 re-link the data
block onto the free list for the table.
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.