PL/SQL TUNING

Following is the procedure as how to collect PL/SQL trace and tune them.




Step 1:Enable Specific Subprograms
Enable specific subprograms with one of the two methods:
Enable a subprogram by compiling it with the debug option:
Alter session set PLSQL_DEBUG=true;
Create or Replace ……
Recompile the specific subprogram with the debug option:

Step 2 and 3:Identify a Trace Level and Start Tracing
Specify the trace level by using
Dbms_trace.set_plsql_trace:
Execute the code to be traced:
Step 4:Turn Off Tracing
Remember to turn tracing off by using the
Dbms_trace.Clear_plsql_trace procedure.
Eg:EXECUTE DBMS_TRACE.CLEAR_PLSQL_TRACE
Step 5:Examine the Trace Information
Examine the trace information.
Call tracing writes out the program unit type,name and stack number.
Execption tracing write out the trace number.
Tune the SQL statements from the trace
Rewrite the procedure if its required.