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

 

 


 

 

 

 

 

 

 

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.


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