 |
|
Freelist Unlinks with Insert Operations
Oracle Tips by Burleson Consulting |
As new rows are inserted, the block may be
removed from the freelist if the free space becomes less than the
bytes specified by pctfree. Since the block being inserted is
always at the head of the freelist chain, only two blocks will be
affected. In our example, let’s assume that the insert has
caused block 106 to be removed from the freelist chain:
1.
Oracle detects that free space is less than pctfree
for block 20 and invokes the unlink operation. Since block 20 is the
first block on the freelist chain, Oracle reads the data block
address (Remote DBA) inside the block header and sees that the next free
block is block 60.
2.
Oracle next adjusts the freelist header node and moves the
Remote DBA for block 60 to the head of the freelist in the segment header.
Block 20 no longer participates in the freelist chain, and the first
entry in the freelist is now block 60, as shown in Figure 6-11.
Figure 11: A freelist unlink operation
Freelist Relinks with Update Statements
As updates to existing rows cause the row to
expand, the block may be unlinked from the freelist if the free
space in the block becomes less than pctfree. Of course, this
will only happen if the row contains VARCHAR, RAW, or LONG RAW
column datatypes, since these are the only datatypes that could
expand upon update. Because the updated block is not at the
head of the freelist chain, the prior block’s freelist pointer
cannot be adjusted to omit the block. Note that the dead block
remains on the freelist even though it does not have room to accept
a row.
The dead block remaining on the list will
cause additional Oracle overhead, especially if there are a large
number of “unavailable” blocks on the freelist. At run time, Oracle
will incur additional I/Os when reading these freelists, and it will
try the freelist as many as five times attempting to find a block
with enough room to store the new row. After five attempts, Oracle
will raise the high-water mark for the table.
Reducing Freelist Relinks
Either of these techniques will cause the
freelists to be populated largely from new extents. Of course, this
approach requires lots of extra disk space, and the table must be
reorganized periodically to reclaim the wasted storage. Freelists
relinks can be reduced in two ways:
-
Freelists relinks can be “turned down” by
setting pctused to 1. Setting pctused to a low value
means that data blocks are not relinked onto the freelist chain
unless they are they are completely empty.
-
Use the APPEND hint when adding rows. By
using APPEND with inserts, you tell Oracle to bypass the freelists
and raise the high-water mark for the table to grab a fresh,
unused data block.
Tip:
Remember the cardinal rule of object tuning. There is a direct
trade-off between efficient space reuse and fast performance of
insert statements. If high performance is more important than
space reuse, you can use an Oracle8 SQL hint that will bypass
freelist usage. By placing /*+ append */ immediately after
the INSERT keyword, you direct Oracle to increase the high-water
mark for the table and place the row into a fresh empty block (see
Figure6-12). This hint ensures that all inserts are made into empty
blocks, thereby improving the speed.
Figure 12: Using the APPEND option with SQL
inserts
Next, let’s wrap up this chapter with a
review of the major points.
Conclusion
When considering table access methods, you
must remember that the number one goal of Oracle tuning is to
carefully examine all large-table full-table scans and determine if
the query will be better served by using an index. To fully
investigate a SQL query, you must understand the creation of the
execution plan and the relationship between the execution plan and
the table access method. You must also remember that there are many
reorganization and indexing techniques that will improve the ability
of Oracle to perform an optimal table access. Remember, for any SQL
statement there exists only one optimal table access method, and it
is your job to locate that method and make it persistent.
Next we will take a closer look at the steps
and goals of Oracle SQL tuning and provide a methodology for
ensuring that all SQL statements are tuned and stay performing at
optimal levels.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
