Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Traditional Freelists Management
and Oracle Objects


Oracle Tips by Burleson
 

One of the benefits of having Oracle is that it manages all of the free space within each tablespace. Oracle handles table and index space management for us and insulates humans from the inner workings of the Oracle tables and indexes. However, experienced Oracle tuning professionals need to understand how Oracle manages table extents and free data blocks.

Knowing the internal Oracle table management strategies will help you become successful in managing high-volume performance within Oracle. To be proficient at object tuning, you need to understand the behavior of freelists and freelist groups, and their relationship to the values of the pctfree and pctused parameters. This knowledge is especially imperative for enterprise resource planning (ERP) applications where poor table performance is often directly related to improper table settings.

The most common mistake for the beginner is assuming that the default Oracle parameters are optimal for all objects. Unless disk consumption is not a concern, you must consider the average row length and database block size when setting pctfree and pctused for a table such that empty blocks are efficiently placed back onto the freelists. When these settings are wrong, Oracle may populate freelists with “dead” blocks that do not have enough room to store a row, causing significant processing delays.

This dead block problem occurs when the setting for pctused allows a block to relink onto the freelist when it does not have enough free space to accept a new row. We will explain the relationship between average row length and freelist behavior later in this chapter.

Freelists are critical to the effective reuse of space within the Oracle tablespaces and are directly related to the pctfree and pctused storage parameters. When the database is directed to make blocks available as soon as possible (with a high setting of pctused ), the reuse of free space is maximized. However, there is a direct trade-off between high performance and efficient reuse of table blocks. When tuning Oracle tables and indexes, you need to consciously decide if you desire high performance or efficient space reuse, and set the table parameters accordingly. Let’s take a close look at how these freelists affect the performance of Oracle.

Whenever a request is made to insert a row into a table, Oracle goes to a freelist to find a block with enough space to accept a row. As you may know, the freelist chain is kept in the first block of the table or index, and this block is known as the segment header. The sole purpose of the pctfree and pctused table allocation parameters is to control the movement of blocks to and from the freelists. While the freelist link and unlink operations are simple Oracle functions, the settings for freelist link (pctused ) and unlink (pctfree) operations can have a dramatic impact on the performance of Oracle.

The default settings for all Oracle objects is pctused=40 and pctfree=10. As you may know from DBA basics, the pctfree parameter governs freelist unlinks. Setting pctfree=10 means that every block reserves 10 percent of the space for row expansion. The pctused parameter governs freelist relinks. Setting pctused=40 means that a block must become less than 40-percent full before being relinked on the table freelist.

Let’s take a closer look at how freelist management works, and how it affects the performance of Oracle. Many neophytes misunderstand what happens when a block is readded to the freelist. Once a block is relinked onto the freelist after a delete, it will remain on the freelist even when the space exceeds 60 percent. Only reaching pctfree will take the database block off of the freelist.

Linking and Unlinking from the Freelists

As we now know, the pctfree and pctused table parameters are used to govern the movement of database blocks to and from the table freelists. In general, there is a direct trade-off between performance and efficient table utilization because efficient block reuse requires some overhead when linking and unlinking blocks with the freelist. As you may know, linking and unlinking a block requires two writes: one to the segment header for the freelist head node, and the other to the new block to make it participate in the freelist chain. The following general rules apply to freelists:

  • insert An insert may trigger the pctfree threshold, causing a freelist unlink. Since insert operations always use the free block at the head of the freelist chain, there will be minimal overhead when unlinking this block.

  • update An update that expands row length is affected by pctfree, but it will not cause a freelist unlink since the target block would not be at the head of the freelist chain.

  • delete A delete of rows may trigger the pctused threshold and cause a freelist link.

You also need to understand how new free blocks are added to the freelist chain. At table extension time, the high-water mark for the table is increased, and new blocks are moved onto the master freelist, where they are, in turn, moved to process freelists. For tables that do not contain multiple freelists, the transfer is done five blocks at a time. For tables with multiple freelists, the transfer is done in sizes (5*(number of freelists + 1)). For example, in a table with 20 freelists, 105 blocks will be moved onto the master freelist each time that a table increases its high-water mark.

To see how this works, let’s review the mechanisms associated with freelist links and unlinks. For the purposes of the following examples, let’s use Figure 10-7.

Figure 10-85: A sample freelist chain

The segment header contains a space to hold a pointer to the first free block in the table. Inside Oracle, a pointer to a block is called a data block address, or DBA for short. The first block on the freelist chain also has a space in the block header to contain the DBA for the next free block, and so on.

Let’s explore what happens internally during row operations.

Freelist Unlinks with Insert Operations

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 20 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 (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 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 10-8.

Figure 10-86: 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 is a large number of “unavailable” blocks on the freelist. At runtime, Oracle will incur additional I/Os when reading these freelists, and 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 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, 8i and 9i SQL hint that will bypass freelist checking. By placing /*+ append */ immediately after the INSERT keyword, Oracle will be directed to increase the high-water mark for the table and place the row into a fresh empty block.

Now that we understand how freelists operate within each Oracle table and index, we are ready to dig deeper and look at the internals of table management.


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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter