I received mail from one of
our developers to tune a procedure that is taking about eight hours to
complete. Before, I start tuning the procedure, I want to understand the
procedure and its background.
The Scenario
-
The job dynamically generates
update statements, through an Informatica workflow and executes each update
statement, calling a pl/sql a procedure. -
On an average, 20000 update
statements are executed per job. During these 20000 updates, multiple tables
are updated multiple times. -
Each update statement updates
between 0 to 300000 rows. -
Each update statement is committed
after execution. That means the commits are occurring after 0 updates or 300000
updates. -
All the updates are occurring on
child tables. During the updates, Oracle locks the parent tables. -
The users’ expectation is that the
procedure should complete within an hour. -
All these bottlenecks are consuming
nearly 8:00 hours for the procedure’s completion.
A Look at the Old Procedure
create or replace procedure old…………..procedure (
col1_nm in varchar2),
col2_nm in varchar2,
col3_nm in integer,
tab_nm in varchar2,
col1_val in integer,
col2_val in integer,
col3_val in integer,
colw_nm in integer,
colw_val in integer,
o_status out integer,
o_sql out varchar2 )
is
v_updatesql varchar2(1000);
begin
v_updatesql := ‘update scott.’ || tab_nm|| ‘ ‘ ||
‘ set ‘ || col1_nm || ‘ = ‘ || col1_val || ‘, ‘ ||
‘ col2_nm = ”’ || col1_val || ”’, ‘ ||
‘col3_nm = ”’ || col3_val || ”’ ‘ ||
‘where ‘ || colw_nm || ‘ = ‘ || colw_val;
Execute immediate (v_updatesql);
o_sql:=v_updatesql;
o_status:=0;
exception
when others then
o_sql:=v_updatesql;
o_status:=1;
end;
This is a simple procedure.
The procedure takes some input parameters and returns status and sql statement as
output parameters. A test run of the procedure with 20000 updates, took almost 8:00 hours.
My Quotations
-
It is better to be three hours
earlier than one minute late — Charles Dickens. -
Better late than never
— Proverb - A thing well begun is half done –Proverb
DBA Jargon
-
Let the procedure take its own
time dude, why hurry. -
If you want your job to complete in
time, start it a day ahead. -
Tuning all the inefficient code
in the world is a pain, dude.
Strategize Tuning Process
- Understand the application.
- Define your tuning goals.
- Eliminate un-wanted/wasted work.
- Eliminate locking issues.
- Build efficient logic.
-
Explore scope for process
re-engineering -
Don’t be over enthusiastic in
tuning. -
Try to achieve reasonably
acceptable goals.
Tuning Strategy
01 Load a temporary table
with all the variable parameters for the dynamic update statements to be
executed. Process the updates in the order of table_name, column and their values specified in the
where clause.
02 Skip the execution of updates,
that are updating 0 rows.
03 Commit every 5000+ updates.
04 Write logic only to
disable the referential integrity constraints on the child tables when the updates
are occurring. Avoid
disabling and re-enabling the referential integrity constraints on the child
tables multiple times. Be
specific in your logic to disable and re-enable referential integrity
constraints once per table, even though
multiple updates are executed on the same tables multiple times.
05 Test and use the
parameters for further tuning the performance of the procedure:
- cursor_sharing
- cursor_space_for_time
- session_cached_cursors
- open cursors
-
analyzing the staging table after
every load of update parameters -
building indexes on child tables,
wherever necessary
create or replace procedure new……….procedure as
–Written by JP Vijaykumar
–Date 09-14-2007
v_str varchar2(2000);
v_sql varchar2(2000);
v_cons number;
v_num1 number;
v_num2 number:=0;
begin
–v_str:=’alter session set cursor_sharing=FORCE’;
—execute immediate v_str;
–v_str:=’alter session set nls_date_format=”’||’ mm-dd-yyyy’||””;
–execute immediate v_str;
for c1 in (select distinct table_name
from scott.stage_table where processed IS NULL
order by table_name) loop
/**********************************************************************************
Upfront, I want to load all of the parameters for updates in a temporary table, and process, so that I can
execute the updates in the order of table_name, columns in the where clause.
***********************************************************************************/
v_cons:=0;
for c2 in (select ………..
from scott.stage_table where table_name = c1.table_name
and processed IS NULL
order …………….) loop
begin
v_str:=’select count(*) from scott.’||c1.table_name|| where ‘||…………………..;
execute immediate v_str into v_num1;
if (v_num1 > 0 ) then
if (v_cons = 0) then
/*************************************************************************************
If there are updates to be performed on a table, the referential integrity constraints on the table are disabled.
Only those tables, where the referential integrity constraints were disabled, are re-enabled at the end.
*************************************************************************************/
for c3 in (select constraint_name from all_constraints
where owner=’SCOTT’ and table_name=c1.table_name
and constraint_type =’R’) loop
v_str:=’alter table scott.’||c1.table_name||’ disable constraint ‘||c3.constraint_name;
execute immediate v_str;
v_cons:=1;
end loop; –c3
end if;
v_sql:=’update scott.’||c1.table_name||’ set……………..where…………….’;
execute immediate v_sql;
v_num2:=v_num2 + v_num1;
end if;
if (v_num2 >= 5000) then
commit;
v_num2:=0;
end if;
v_str:=’update scott.stage_table set processed = ”’||’Y’||”’ where ……………..;
execute immediate v_str;
/*************************************************************************************
Here I opened an exception handler to capture the error messages. But my procedure should continue
without exiting out of the loop.
*************************************************************************************/
exception
when others then
execute immediate ‘update scott.stage_table set processed = ”’||’E’||”’ …………….;
end;
end loop; –c2
if (v_cons = 1) then
for c4 in (select constraint_name from all_constraints
where owner=’…….’ and table_name=c1.table_name
and constraint_type =’R’) loop
v_str:=’alter table scott.’||c1.table_name||’ enable constraint ‘||c4.constraint_name;
execute immediate v_str;
end loop; –c4
end if;
end loop; –c1
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
The original procedure with
20000 updates is taking 6:30 hrs to 8:00 hrs.
After tuning the procedure, 97139
updates completed within 3:00 hrs..
Every procedure is unique. Understand
the procedure, what it is doing and how. Based on your observation, frame your
strategy.