Execution Plans Are For SQL Statements

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

$ORACLE_HOME/rdbms/admin/profload.sql

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.

$ORACLE_HOME/rdbms/admin/proftab.sql

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
as
begin
dbms_profiler.start_profiler(‘test_dbms_profile’);
series of statements
dbms_profile.stop_profiler;
end;
/

Alternatively you can start and stop the profile outside the procedure like the following:

execute dbms_profiler.start_profiler(‘test_dbms_profile’);
execute test_dbms_profile;
execute dbms_profiler.stop_profiler;

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,
plsql_profiler_units b,
plsql_profiler_data c,
user_source u
where a.runid=b.runid
and a.runid=c.runid
and b.unit_name=u.name
and c.line#=u.line;

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.

Advertisements

Tagged: ,

2 thoughts on “Execution Plans Are For SQL Statements

  1. Jeff Smith (@thatjeffsmith) May 1, 2013 at 18:11 Reply

    I thought I was the only geek that loved the profiler!

    • beckysoto96 May 8, 2013 at 15:31 Reply

      I believe in using every tool at my disposal until I find it useless. Performance isn’t always about the SQL.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: