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.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
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.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
