Oracle's DBMS_PROFILER: PL/SQL Performance Tuning - Page 3

April 28, 2003

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.

» See All Articles by Columnist Amar Kumar Padhi