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

 

 


 

 

 

 

 

 

 

Linking and Unlinking from the Freelists

Oracle Tips by Burleson Consulting

As you 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   A SQL 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

  • update   A SQL 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 SQL 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 17-3.

Figure 3: 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 Remote DBA for short. The first block on the freelist chain also has a space in the block header to contain the Remote DBA for the next free block, and so on.

Let’s explore what happens internally during row operations.

Freelist Unlinks with SQL 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 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 17-4.

Figure 4: A freelist unlink operation

Freelist Re-links 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 a SQL 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.


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