Developers are well aware of the need to understand the execution plan for a given SQL statement. Their training has them always looking for the lowest cost, but not really analyzing the access path. However, when a given statement includes a function or is an PL/SQL anonymous block some still try to find the cost through an execution plan. That’s not the right tool. It’s important to understand the difference and use the right tool or you just spin your wheels.
An execution plan is only for SQL statements. It provides valuable information on all the access paths chosen to arrive at a given answer for a given statement. There’s a wealth of information available the covers how to analyze execution plans to determine whether the optimizer has all the rights statistics and the access paths are the most efficient. But execution plans are only for SQL statements.
So if execution plans are only for SQL statements how can pl/sql code be reviewed and analyzed to determine if its efficient, or find the bottlenecks in the code? The answer is DBMS_PROFILER.
What’s captured? the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
DBMS_PROFILE must first be installed. Your DBA can check and install it if necessary as SYSDBA
The tables will then need to be created in the schema that is executing the procedures. If you have more than one user than of course, you will need to create them under each of the users. Alternatively you could create a test account that owns the DBMS_PROFILE table as well.
To utilize DBMS_PROFILE its as simple as modifying the interesting anonymous block, procedure, package or function like the following:
create or replace procedure test_dbms_profile
series of statements
Alternatively you can start and stop the profile outside the procedure like the following:
The above method is great when you are working in production or another environment where modifying the code may not be easy or desirable. As well as when you may need to execute a series of pl/sql.
Execute the procedure.
Now to review the data from dbms_profile you can use a simple query such as the following:
BREAK ON runid on run_owner, on run_comment on run_secs
select a.runid, a.run_owner, a.run_comment,
a.run_total_time /1000000000 run_secs, c.total_occur,
c.total_time/1000000000 line_total_secs, c.line#, u.text
from plsql_profiler_runs a,
Alternatively there is a SQL that will allow the report to be formatted in HTML.
In a future post I will delve into using DBMS_PROFILER to assist in tuning PL/SQL within production environments.