 |
|
Oracle update performance speed best
practices
Oracle Tips by Burleson Consulting |
The SQL standard for DML
UPDATE statements can be complex and convoluted and there are best practices
that can help you to write efficient UPDATE statements.
-
Use CTAS in lieu of large updates
- Include the SET condition
in the WHERE clause
- Simplify the WHERE
predicates
- Have a small, separate
data cache for high DML tables
Use
CTAS in lieu of large updates
When you are updating the
majority of rows in a table, using Create Table As Select (CTAS) is often more
efficient performance than a standard update. For example, assume that the
following update changed 75% of the table rows:
update
mytab
set
status = 'new'
where
status = 'old;
In this case, a parallelized
CTAS may perform far faster (Note: Make sure that you have an SMP server
before using the
parallel
degree option):
create table new_mytab
NOLOGGING as
select /*+ full parallel(mytab,35)*/
decode (status,'new','old',status,
col2, col3, col4
from mytab;
-- rebuild indexes,
triggers and constraints to new_mytab
rename mytab to bkup_mytab;
rename new_mytab to mytab;
Include the SET
condition in the WHERE clause
This note
shows a case where the developer forgot to include the SET condition in the
UPDATE WHERE clause, causing high redo waits (log file parallel write waits, log
file sync waits). Simply including the existing state of the SET clause
can result in a huger performance improvement for UPDATE statements:
-- zillion row update
UPDATE HISTORY SET FLAG=0 WHERE CLASS='X'
-- hundred row update
UPDATE HISTORY SET FLAG=0 WHERE CLASS='X' AND FLAG!=0
The
select for update is not a
good locking
strategy because there are many things that can go wrong. Instead
of
select for update, savvy Oracle developers will adopt
alternatives mechanisms like a re-read upon update commit where the
transaction re-read the rows and ensure that it has not changed since the
original read.
Have a
small, separate data cache for high DML tables
As more people adopt 64-bit
servers with giant data buffers, we see a delay caused by the database writer
process having to scan through giant data buffers seeking dirty blocks.
Many shops are replacing their platter-style disks with
solid-state disks, and
creating a very small data buffer, just for the updates. The book Oracle
Tuning: The Definitive Reference notes that if you are still using traditional
disks, many shops
segregate high-updates objects (tables & indexes) into a separate blocksize
so that they can have a separate, small data buffer.
"By segregating high
activity tables into a separate, smaller data buffer, Oracle has far less
RAM frames to scan for dirty block, improving the throughput and also
reducing CPU consumption. This is especially important for high update
tables with more than 100 row changes per second."
MetaLink
Note:223299.1 also embraces the importance of
multiple blocksizes, listing the multiple buffer regions as among the most
important tuning parameters in Oracle9i.
Simplify the WHERE
clause predicates
The most common issue with
updates is the requirement to have a complex SELECT statement is the where
clause to identify the rows to be updated. The best techniques for
simplifying UPDATE where clauses include:
Also see my other notes on high
performance UPDATES and INSERTS:
