SQL & PL/SQL

Hi All,

I have one plsql procedure with many cursors. I have added the sql optimizer hints to all the cursors to tune it. I have followed the below steps in testing.

The test cases are really confusing and I was unable to find out whether query tuning worked or not. The research shows that it is due to cache and many other things like oracle optimizer might use a different path etc.

Then what is the correct way to test the tuned and untuned versions of a procedure?

Test case 1:

1. Ran the Oracle EBS Payroll run. (The SQL cursors query refers to the tables data populated by payroll run)
2. Executed the untuned plsql procedure. The performance is 2 hrs and 30 mins
3. Executed the tuned plsql procedure. The performance is 45mins
4. Rolled back the payroll run for the next test case. ( It will delete the data from all the tables which got inserted in Step 1)

Test Case 2:

1. Ran the Oracle EBS Payroll run once again. (The SQL cursors query refers to the tables data populated by payroll run)
2. Executed the tuned plsql procedure. The performance is 1hrs 40 mins
3. Executed the untuned plsql procedure. The performance is 45 mins.
4. Rolled back the payroll run for the next test case. ( It will delete the data from all the tables which got inserted in Step 1)

The Prototype of the plsql procedure as follows:

untuned version:

create or replace procedure xx_untuned_proc is

cursor c_get_run_details is

select * from pay_run_results1 a,pay_run_results1 b

where a.run_id=b.run_id;

Cursor c_get_run_values is

select * from pay_run_results_values1 a,pay_run_results_values1 b

where a.run_value_id=b.run_value_id;

Begin

for rec_get_run_details in c_get_run_details loop

     insert into temps1 values ( rec_get_run_details.run_id);

end loop; 

  for rec_get_run_values in c_get_run_values loop

     insert into temps2 values ( c_get_run_values.run_result_id);

end loop;    

commit;

end;

Tuned Version

create or replace procedure xx_tuned_proc is

cursor c_get_run_details is

select /* + neccessary hints */ * from pay_run_results1 a,pay_run_results1 b

where a.run_id=b.run_id;

Cursor c_get_run_values is

select /* + neccessary hints */  * from pay_run_results_values1 a,pay_run_results_values1 b

where a.run_value_id=b.run_value_id;

Begin

for rec_get_run_details in c_get_run_details loop

     insert into temps1 values ( rec_get_run_details.run_id);

end loop;  

  for rec_get_run_values in c_get_run_values loop

     insert into temps2 values ( c_get_run_values.run_result_id);

end loop;    

commit;

end;

Thanks in advance,

Ramesh Selvam.