Oracle's DBMS_PROFILER: PL/SQL Performance Tuning

By Amar Kumar Padhi

<!--content_start-->

An application can always be fine-tuned for better performance with the use of better alternatives or with the new features introduced with every release of Oracle.

Simply inspecting the code can bring out the bottlenecks eating up your processing time. Using explain plan to fine tune the SQL statements resolves issues most of the time. However, sometimes it may not be that simple. It is baffling when all the SQL statements are well tuned but the routine still takes noticeable time to execute.

DBMS_PROFILER Package
Oracle 8i provides a new tool called PL/SQL Profiler. This is a powerful tool to analyze a Program unit execution and determine the runtime behavior. The results generated can then be evaluated to find out the hot areas in the code. This tool helps us identify performance bottlenecks, as well as where excess execution time is being spent in the code. The time spent in executing an SQL statement is also generated. This process is implemented with DBMS_PROFILER package.

The possible profiler statistics that are generated:
   1. Total number of times each line was executed.
   2. Time spent executing each line. This includes SQL statements.
   3. Minimum and maximum duration spent on a specific line of code.
   4. Code that is actually being executed for a given scenario.

DBMS_PROFILER.START_PROFILER
The DBMS_PROFILER.START_PROFILER tells Oracle to start the monitoring process. An identifier needs to be provided with each run that is used later to retrieve the statistics.

e.g.: 
l_runstatus := dbms_profiler.start_profiler('am' || 
to_char(sysdate)); 

DBMS_PROFILER.STOP_PROFILER
The DBMS_PROFILER.STOP_PROFILER tells Oracle to stop the monitoring.

e.g.: 
l_runstatus := dbms_profiler.stop_profiler; 

DBMS_PROFILER.FLUSH_DATA
The data collected for an execution is held in the memory. Calling the DBMS_PROFILER.FLUSH_DATA routine tells Oracle to save this data in profiler tables and clear the memory.

e.g.: 
l_runstatus := dbms_profiler.flush_data; 

The above functions return the following status'.
   0 : Successful completion
   1 : Incorrect parameters passed (error_parm).
   2 : data flush operation failed (error_io).
   -1 : mismatch between package and database implementation (error_version).

 

 

EXAMPLE on using DBMS_PROFILER
This is a simple example that I am providing just as a reference on how to use the Profiler. I will run profiler and debug the following routine for performance. Customized scripts that are used in the example can be found at the end of this article.

1. Creating my procedure.

E.g.: 
create or replace procedure am_perf_chk (pi_seq     in            number, 
                                         pio_status in out nocopy varchar2) is 
  l_dat date := sysdate; 
begin 
  if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then 
    pio_status := 'OK'; 
  else 
    pio_status := 'Invalid tape loaded'; 
  end if; 
exception 
  when others then 
    pio_status := 'Error in am_perf_chek'; 
end; 

2. Calling the routine with profiler.
The above routine will be placed and called in the call_profiler.sql (script details given below). The pi_seq value is passed as 2.

	SQL> @d:\am\call_profiler.sql 
	Profiler started 
	Invalid tape loaded 
	PL/SQL procedure successfully completed. 
	Profiler stopped 
	Profiler flushed 
	runid:8 

3. Evaluating the execution time.
The evalute_profiler_results.sql is called to get the time statistics.

SQL> @d:\am\evaluate_profiler_results.sql 
Enter value for runid: 8 
Enter value for name: am_perf_chk 
Enter value for owner: scott 
      Line      Occur       Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
         1                       procedure am_perf_chk (pi_seq     in            number,
         2                                              pio_status in out nocopy varchar2) is
         3          2   43.05965   l_dat date := sysdate;
         4                       begin
         5          1   86.35732   if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
         6          0          0     pio_status := 'OK';
         7                         else
         8          1   8.416151     pio_status := 'Invalid tape loaded';
         9                         end if;
        10                       exception
        11                         when others then
        12          0          0     pio_status := 'Error in am_perf_chek';!
        13          1   2.410361 end;
13 rows selected.

Code% coverage
--------------
    66.6666667

As you can see, line 3 shows execution time as 86 msec which can be improved on. The if statement is altered (if pi_seq = 1 and trunc(l_dat) = '21-sep-02' then) and the above process is repeated. The following is the new result:

      Line      Occur       Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
         1                       procedure am_perf_chk (pi_seq     in            number,
         2                                              pio_status in out nocopy varchar2) is
         3          2  17.978816   l_dat date := sysdate;
         4                       begin
         5          1   8.419503   if pi_seq = 1 and trunc(l_dat) = '21-sep-02' then
         6          0          0     pio_status := 'OK';
         7                         else
         8          1   7.512684     pio_status := 'Invalid tape loaded';
         9                         end if;
        10                       exception
        11                         when others then
        12          0          0     pio_status := 'Error in !am_perf_chek';
        13          1    .731657 end;
13 rows selected.

Code% coverage
--------------
    66.6666667

As you can see, line 3 execution time is reduced from 86 msec to 8 msec for the tested scenario. The excess time was taken due to the trunc() built-in. Shifting this to the right prevents its execution if the first condition is false. This is a small example and you will be thrown more challenges when debugging bigger routines.

The profiler result also shows how much of the code was covered during execution. This would give us an idea of the extent of the code that was performance monitored. The idea is to try out various scenarios for executing the code and check on the profiler results to find out if any PL/SQL performance issues are encountered.

Logical analysis can be carried out if a particular piece of code is executed for a given scenario, when it should not be executing at all.

 

 

Creation of the environment
The DBMS_PROFILER package is not automatically created during default installation or creation of the database. Ask the DBA to create the package using the profload.sql script. Create tables for storing statistics either in one central user or in each individual user, using proftab.sql. If tables are created in one central user, like SYS, then grant DML privileges to all other users. Create public synonym on the tables with the same name.

The tables created are:
   PLSQL_PROFILER_RUNS: Run-specific information for the PL/SQL profiler
   PLSQL_PROFILER_UNITS: Information about each library unit in a run
   PLSQL_PROFILER_DATA: Accumulated data from all profiler runs.

A sequence PLSQL_PROFILER_RUNNUMBER provides the run id.

Running and Interpreting Profiler Data
Oracle provides three tables where statistics are populated for a run id. There are many third party tools available to provide customized reports based on this data. Oracle provides profrep.sql and profsum.sql to evaluate data (present in <oracle_home>\plsql\demo\). Below I have provided two simple scripts used in the examples above, to check instantly on a program unit execution time. The execution time is stored in milli-seconds.

-----------------------------------------------------------
Script: call_profiler.sql
-----------------------------------------------------------
set head off
set pages 0
select decode(dbms_profiler.start_profiler, '0', 'Profiler started', 'Profiler error')
from   dual;

--< place your routine in the below block >-- 
declare
  l_status varchar2(200);
begin
  am_perf_chk(2, l_status);
  dbms_output.put_line(l_status);
end;
/

select decode(dbms_profiler.stop_profiler, '0', 'Profiler stopped', 'Profiler error')
from   dual;
select decode(dbms_profiler.flush_data, '0', 'Profiler flushed', 'Profiler error')
from   dual;
select 'runid:' || plsql_profiler_runnumber.currval 
from   dual;
set head on
set pages 200

-----------------------------------------------------------
Script: evaluate_profiler_results.sql
-----------------------------------------------------------
undef runid
undef owner
undef name
set verify off
select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
from   all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
                             d.total_occur, d.total_time/1000000 total_time
                      from   plsql_profiler_data d, plsql_profiler_units u
                      where  u.runid = &&runid
                      and    u.runid = d.runid
                      and    u.unit_number = d.unit_number) p
where  s.owner = p.unit_owner (+)
and    s.name = p.unit_name (+)
and    s.type = p.unit_type (+)
and    s.line = p.line# (+)
and    s.name = upper('&&name')
and    s.owner = upper('&&owner')
order by s.line;
select exec.cnt/total.cnt * 100 "Code% coverage"
from  (select count(1) cnt
      from plsql_profiler_data d, plsql_profiler_units u
      where d.runid = &&runid
      and u.runid = d.runid
      and u.unit_number = d.unit_number
      and u.unit_name = upper('&&name')
      and u.unit_owner = upper('&&owner')) total,
     (select count(1) cnt
      from plsql_profiler_data d, plsql_profiler_units u
      where d.runid = &&runid
      and u.runid = d.runid
      and u.unit_number = d.unit_number
      and u.unit_name = upper('&&name')
      and u.unit_owner = upper('&&owner')
      and d.total_occur > 0) exec;
undef runid
undef owner
undef name

 

Conclusion
DBMS_PROFILER is a very powerful tool and the first of its kind to identify performance issues on the PL/SQL front. This utility can be best used in the development stages to fine tune code based on various applicable scenarios. It can also be used to fine tune routines that are already in production and are taking noticeable time to execute. Overall, this utility gives statistics for each line of code that will help us in evaluating and tuning at a finer level. Just as SQL statements are checked for performance, PL/SQL code should not be ignored but should be tuned for optimal results as well.

<!--content_stop--><!--content_stop-->