BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Reducing Freelist Re-link Operations

Oracle Tips by Burleson Consulting

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. Freelist re-links can be reduced in two ways:

  • "Turn down" freelist re-links by setting pctused to 1. Setting pctused to a low value means that data blocks are not re-linked 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 SQL hint that will bypass freelist checking. 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.

Now that you understand how freelists operate within each Oracle table and index, you are ready to dig deeper and look at long data columns and DML behavior.

Long Data Columns and DML Behavior

One of the most confounding problems with some Oracle tables is the use of large columns. The main problem with RAW and LONG RAW, BLOB and CLOB datatypes is that they often exceed the block size, and whenever a column is larger than the database block size, the column will fragment onto an adjacent data block. This causes Oracle to incur two I/Os instead of one I/O every time the row is accessed. This block-chaining problem is especially prevalent in tables where column lengths grow to thousands of bytes. Of course, it is a good idea to use the maximum supported db_block_size for your version of Oracle (usually 8,192 bytes) in an effort to minimize this chaining.

In order to avoid fragmentation of a row, Oracle will always insert table rows containing a RAW or LONG RAW column onto a completely empty block. Therefore, on insert, Oracle will not attempt to insert below the high-water mark (using freelists) and will always bump the high-water mark, pulling the free blocks from the master freelist. Since Oracle pulls free blocks by raising the high-water mark, Oracle will not reuse blocks once they have been placed on the freelist chain. Actually, free blocks below the high-water mark (i.e., blocks on the freelists) may be used for inserting LONG columns, but only if the block is completely empty. If the block is partially used but still below the pctused mark, it will not be used to insert the LONG data.

Remember, multiple freelists can waste a significant amount of disk space. Tables with dozens of freelists may exhibit the “sparse table” phenomenon as the table grows and each freelist contains blocks that are not known to the other freelist chains. If these tables consume too much space, the Oracle administrator faces a tough decision. To maximize space reuse, you would want the table to be placed onto a freelist as soon as it is capable of receiving more than two new rows. Therefore, a fairly high value for pctused is desired. On the other hand, this would result in slower run-time performance, since Oracle will be able to insert only a few rows before having to perform an I/O to get another block.

There are cases when large row lengths and an improper setting of pctfree can cause performance degradation during SQL insert operations. The problem occurs when a block becomes too full to accept another row while the block remains on the freelist. As rows are inserted, Oracle must fetch these blocks from the freelist, only to find that there is not enough room for a row. Fortunately, Oracle will not continue fetching freelist blocks forever. After retrieving five too-small blocks from the freelist, Oracle assumes that there will be no blocks on the freelist that have enough space for the row, and Oracle will grab an empty block from the master freelist, as shown in Figure 17-7.

Figure 5: Oracle eventually abandons the freelist and raises the high-water mark

This problem usually occurs when the row length for the table exceeds the space reserved on the block. For example, with 4K blocks and pctfree=10, the reserved space will equal 410 bytes (not counting block header space). Therefore, we may see this problem for any rows that are more than 410 bytes in length.

Now that you understand the internal freelist linking mechanism, let’s see how to monitor Oracle to identify when Oracle is waiting because of freelist contention.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter