Question:
What is the best practice for using
commit processing on Oracle PL/SQL Blocks? Is it best to commit
frequently to release database resources?
Answer: The best
oracle commit frequency is never to explicitly commit and
let the entire update complete as a single transaction, but there
are exceptions.
As a general rule, the best practice is to
dedicate an undo log (rollback segment) that is large enough to hold
all "before" row images.
SQL> connect scott/tiger
Connected.
SQL> set transaction use rollback segment big_rbs;
Session altered.
SQL> @huge_update.sql
With this method you can do a zillion row
update without any commit.
However, for updates that can run for many
hours, some DBA's will choose to commit during the huge
update to prevent a 5 hour update failing and then having to wait
two hours for the whole transaction to rollback.
In this case, the DBA must alter the batch
update to make the program re-startable, such that, in the case of
an abort, a re-start of the update will proceed from the last
commit checkpoint.
This re-startability can be done at the
PL/SQL program level, and some people write their PL/SQL to write
re-start details to a Oracle table or flat file. Upon an abort and
re-start, the PL/SQL will spin up to the point of the last
commit and then begin updating.
Again, using intermediate commits is
reserved only for huge batch jobs where an update will run for many
hours, and waiting for a rollback is not feasible due to the size of
the batch window.
In all other cases, the best practice in
PL/SQL is never to commit and dedicate a giant rollback
segment to the task!
Benchmarks suggest that the longer the commit
frequency, the faster the overall elapsed time for a update job.
Never use commit processing unless you have a multi-hour
unstable job (with a history of aborting) where there is not enough
time for a full-rollback and re-start.
Instead, the best commit frequency is none at all, using
very large undo segments to avoid an
ORA-01555 Snapshot Too Old error.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
