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 Management and DML Performance

Oracle Tips by Burleson Consulting

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 re-link onto the freelist when it does not have enough free space to accept a new row. I 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 are pctused=40 and pctfree=10. As you may know from Remote 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 re-added to the freelist. Once a block is re-linked 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.


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