Question: I want my PL/SQL program to be
restartable, such that if there is an abort from a non-zero return
code, the PL/SQL program will restart from the last commit
checkpoint.
What is the best way to make a PL/SQL batch update job
restartable?
Answer: Obviously, the best way to make
PL/SQL DML jobs restartable is to never use a commit, and re-run the
whole job.
Sadly, the exact PL/SQL to make a PL/SQL update/insert/delete is
difficult because there is no preset order for DML to inspect and
change all rows within a table.
You cannot save the current cursor where the updated aborted, so
twiddling a cursor is not a solution, and you must alter your code
to ignore previous updates and restart.
Also change your dbms_scheduler to mark your job
as restartable.
To make a scheduled PL/SQL job restartable with
dbms_scheduler, set the operation flag to retry_run.
This says that the job is being retried because the previous run
resulted in an error and RESTARTABLE is set to TRUE.
This will roll back all database changes and the PL/SQL is
automatically restartable.
However, in cases where it is not feasible to wait many
hours for a zillion rows update to rollback and re-start the batch
job, more creative restart methods are desired.
Again, The best solution to make a PL/SQL DML job
re-startable is to never place intermediate commitstatements and let
the job run, where it will either abort and fully rollback else of
commit at end of job (EOJ) time. When a job is too long to run
with no intermediate commits (e.g. a 12 hour night update job), then
there are several ways to make PL/SQL update/insert/delete
restartable:
- Have a last_updated_flag column and have your
PL/SQL test to see which rows have already been changed.
- If the DML changes can be identified programmatically,
simply re-run the PL/SQL update, bypassing row values that have
already been updated.
- There is a dbms_xmlgen.restartQuery(ctx IN ctxHandle)
package. The restartquery procedure will restarts a read-only
query and generate the XML from the first row. It can be used to
start executing the query again, without having to create a new
context.
- For query-only PL/SQL (only for super-large PL/SQL reports
that write to flat files), you can make them restartable by
using utl_file to read the last report row written and
then spin the code until you reach that point. However, most
PL/SQL reports are sorted, so the entire query must be re-run to
find the point where the code aborted.
In sum, making PL/SQL is highly dependent on the nature of your
specific PL/SQL code.
If you want to rent a 3rd party programmer to make your PL/SQL
program restartable, call 800-766-1884.
 |
|
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.
|
