Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 20, 2007

Tuning an Oracle Procedure

By DatabaseJournal.com Staff

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 )
v_updatesql varchar2(1000);
  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);

when others then 

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;
--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.
   for c2 in (select ........... 
		from scott.stage_table where table_name = c1.table_name
                and   processed     IS NULL
    		order ................) loop 
      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; 		
        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    
   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.
	when others then
	execute immediate 'update scott.stage_table set processed = '''||'E'||''' ................;
 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   
when others then

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.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM