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










Tuning SQL DML Statements

Oracle Tips by Burleson Consulting

This chapter is concerned with the tuning of SQL statements that perform data manipulation. Known as data manipulation language, or DML, these are special types of SQL statements that perform update, insert, and delete operations. Unlike standard SQL select statements, DML performance is heavily influenced by the internal Oracle storage parameters, and understanding the relationship between DML and object parameters will be the primary focus of this chapter.

With only a few hints available for DML tuning (such as the append hint), the focus of this chapter is on how you can adjust the way you perform DML operations to minimize Oracle overhead. The topics in this chapter include:

  • Oracle storage parameters and DML performance

  • Freelists and DML performance

  • Long data columns and DML behavior

  • Setting PCTFREE and PCTUSED according to average row length

  • Buffer busy waits and DML contention

  • Reducing index overhead with DML operations

Oracle Storage Parameters and DML Performance

Letís begin by making it clear that the SQL insert, update and delete statements are very simple in nature, and by their structure, offer few opportunities for SQL tuning.  SQL inserts, for example, only process a single row at a time, and contain no where clause.

The largest opportunity for tuning Oracle DML is to exploit the relationship between object storage parameters and SQL performance. Poor SQL performance within Oracle DML 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 updates   Update statements run very slowly with double the amount of I/O. This happens when SQL update operations expand a VARCHAR or BLOB column and Oracle is forced to chain the row contents onto additional data blocks.

  • Slow deletes   Large SQL delete statements can run slowly and cause segment header contention. This happens when rows are deleted and Oracle must re-link 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 17-1, 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 1: 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 17-2.

Figure 2: 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 re-links 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.

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