 |
|
Oracle Storage
Parameters and Table Access Performance
Oracle Tips by Burleson Consulting |
Let’s begin this section by introducing the
relationship between object storage parameters and performance. Poor
object performance within Oracle is experienced in several areas:
-
Slow inserts Insert
operations run slowly and have excessive I/O. This happens when
blocks on the freelist only have room for 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 update operations expand a VARCHAR or BLOB
column and Oracle is forced to chain the row contents onto
additional data blocks.
-
Slow deletes Large
delete statements can run slowly and cause segment header
contention. This happens when rows are deleted and Oracle must
relink the data block onto the freelist for the table.
As you see, the storage parameters for Oracle
tables and indexes can have an important effect on the performance
of the database. Let’s begin our discussion of object tuning by
reviewing the common storage parameters that affect Oracle
performance.
The pctfree Storage Parameter
The purpose of pctfree is to tell
Oracle when to remove a block from the object’s freelist. Since the
Oracle default is pctfree = 10, blocks remain on the freelist
while they are less than 90
percent full. As shown in Figure 6-8, once an insert
makes the block grow beyond 90
percent full, it is removed from the freelist, leaving
10 percent
of the block for row expansion. Furthermore, the data block will
remain off the freelist
even after the space drops below 90
percent. Only after subsequent delete
operations cause the space to fall below the pctused
threshold of 40
percent will Oracle put the block back onto the
freelist.
Figure 8: The pctfree threshold
The pctused Storage Parameter
The
pctused parameter tells Oracle when to add a previously full
block onto the freelist. 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 freelist relink operation is triggered, as shown in Figure
6-9.
Figure 9: The pctused threshold
For example, with pctused = 60, all
database blocks that have less than 60 percent will be on the
freelist, 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
freelist. When rows are deleted, data blocks become available when a
block’s free space drops below the value of pctused for the
table, and Oracle relinks the data block onto the freelist 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 freelist.
The freelists Storage Parameter
The freelists parameter tells Oracle
how many segment header blocks to create for a table or index.
Multiple freelists are used to prevent segment header contention
when several tasks compete to insert, update, or
delete from the table. The freelists parameter should be
set to the maximum number of concurrent update operations.
Prior to Oracle8i, you must reorganize
the table to change the freelists storage parameter. In
Oracle8i, you can dynamically add freelists to any table or
index with the alter table command. In Oracle8i, adding a
freelist reserves a new block in the table to hold the control
structures.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
