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

 

 


 

 

 

 

 

 

 

Oracle Storage Parameters and Table Access Performance

Oracle Tips by Burleson Consulting

Let’s begin this section by introducing the relationship between object storage parameters and performance. Poor object performance within Oracle is experienced in several areas:

  • Slow inserts   Insert operations run slowly and have excessive I/O. This happens when blocks on the freelist only have room for a few rows before Oracle is forced to grab another free block.

  • Slow selects   Select statements have excessive I/O because of chained rows. This occurs when rows “chain” and fragment onto several data blocks, causing additional I/O to fetch the blocks.

  • Slow updates   Update statements run very slowly with double the amount of I/O. This happens when update operations expand a VARCHAR or BLOB column and Oracle is forced to chain the row contents onto additional data blocks.

  • Slow deletes   Large delete statements can run slowly and cause segment header contention. This happens when rows are deleted and Oracle must relink the data block onto the freelist for the table.

As you see, the storage parameters for Oracle tables and indexes can have an important effect on the performance of the database. Let’s begin our discussion of object tuning by reviewing the common storage parameters that affect Oracle performance.

The pctfree Storage Parameter

The purpose of pctfree is to tell Oracle when to remove a block from the object’s freelist. Since the Oracle default is pctfree = 10, blocks remain on the freelist while they are less than 90 percent full. As shown in Figure 6-8, once an insert makes the block grow beyond 90 percent full, it is removed from the freelist, leaving 10 percent of the block for row expansion. Furthermore, the data block will remain off the freelist even after the space drops below 90 percent. Only after subsequent delete operations cause the space to fall below the pctused threshold of 40 percent will Oracle put the block back onto the freelist.

Figure 8: The pctfree threshold

The pctused Storage Parameter

The pctused parameter tells Oracle when to add a previously full block onto the freelist. As rows are deleted from a table, the database blocks become eligible to accept new rows. This happens when the amount of space in a database block falls below pctused, and a freelist relink operation is triggered, as shown in Figure 6-9.

Figure 9: The pctused threshold

For example, with pctused = 60, all database blocks that have less than 60 percent will be on the freelist, as well as other blocks that dropped below pctused and have not yet grown to pctfree. Once a block deletes a row and becomes less than 60 percent full, the block goes back on the freelist. When rows are deleted, data blocks become available when a block’s free space drops below the value of pctused for the table, and Oracle relinks the data block onto the freelist chain. As the table has rows inserted into it, it will grow until the space on the block exceeds the threshold pctfree, at which time the block is unlinked from the freelist.

The freelists Storage Parameter

The freelists parameter tells Oracle how many segment header blocks to create for a table or index. Multiple freelists are used to prevent segment header contention when several tasks compete to insert, update, or delete from the table. The freelists parameter should be set to the maximum number of concurrent update operations.

Prior to Oracle8i, you must reorganize the table to change the freelists storage parameter. In Oracle8i, you can dynamically add freelists to any table or index with the alter table command. In Oracle8i, adding a freelist reserves a new block in the table to hold the control structures.


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