 |
|
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.
