| |
 |
|
Traditional Oracle Storage
Parameters
and Performance
Oracle Tips by Burleson
|
Let’s begin this chapter 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 we 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 10-5, 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 10-83: 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 10-6.
Figure 10-84: 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. To use this
feature, you must set the compatible parameter to 8.1.6 or
greater.
The freelist groups Storage Parameter
for OPS
The freelist groups parameter is used in
Oracle Parallel Server (Real Application Clusters). When multiple
instances access a table, separate freelist groups are allocated in
the segment header. The freelist groups parameter should be set
the number of instances that access the table. For details on segment
internals with multiple freelist groups, see Chapter 13.
NOTE: The variables are called pctfree and
pctused in the create table and alter table
syntax, but they are called PCT_FREE and PCT_USED in the dba_tables
view in the Oracle dictionary. The programmer responsible for this
mix-up was promoted to senior vice president in recognition of his
contribution to the complexity of the Oracle software.
Summary of Storage Parameter Rules
The following rules govern the settings for the
storage parameters freelists, freelist groups,
pctfree, and pctused. As you know, the value of pctused
and pctfree can easily be changed at any time with the alter
table command, and the observant DBA should be able to develop a
methodology for deciding the optimal settings for these parameters.
For now, accept these rules, and we will be discussing them in detail
later in this chapter.
There is a direct trade-off between effective
space utilization and high performance, and the table storage
parameters control this trade-off:
-
For efficient space reuse 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 freelist. Hence, these
blocks will be able to accept only a few rows before becoming full
again, leading to more I/O.
-
For high performance A low value for
pctused means that Oracle will not place a data block onto the
freelist 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. It takes fewer resources
for Oracle to extend a table than to manage freelists.
While we will go into the justification for
these rules later in this chapter, let’s review the general guidelines
for setting of object storage parameters:
-
Always set pctused to allow enough room
to accept a new row. We never want to have a free block that does
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 block size 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 freelist, and none of the other freelists will contain any free
blocks from the purge.
-
The freelist 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 insert
operations at any time, the customer table should have freelists=20.
-
The freelist groups parameter should be
set the number of Real Application Clusters instances (Oracle
Parallel Server in Oracle8i) that access the table.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
