Tuning an Oracle Procedure

September 20, 2007

by JP Vijaykumar

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.