Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle update tuning tips

Oracle Database Tips by Donald Burleson


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.

  • Run updates in batch mode
  • 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

Running updates in batch mode

If you can take your application offline during the monthly update, you use many update performance features to speed up the job performance:

  • Drop indexes/constraints and rebuild after mass update:  Dropping indexes before a mass update and rebuilding them afterwards can improve update performance significantly. Oracle removes index entries without re-balancing the index tree (a "logical delete"), but this is still time-consuming, especially if you have lots of indexes on the target table.  Also note that you can rebuild the dropped indexes nologging mode.

  • Parallelize the updates:  If you have an SMP server you can run Oracle parallel DML.    You can also manually parallelize the update by breaking them into multiple jobs and submit them simultaneously using dbms_scheduler, cron  or nohup.

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

MOSC 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:

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational