Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Apr 28, 2003

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

By Amar Kumar Padhi

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM