Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Utilities Tips

 

The dbms_profiler Utility

PL/SQL developers are always trying to optimize their code to perform more efficiently. As of Oracle 8.1.5, a utility exists that assists developers with this process. The dbms_profiler is one of the most under-utilized utilities within Oracle.

The basic idea behind profiling is for the developer to understand where their code is spending the most time, so they can detect and optimize it. The profiling utility allows Oracle to collect data in memory structures and then dumps it into tables as application code is executed. dbms_profiler is to PL/SQL, what tkprof and Explain Plan are to SQL.

Installation

The profiling tools are not a part of the base installation of Oracle, so that will require more on the part of the developer. Two tables need to be installed along with the Oracle supplied PL/SQL package.

In the $ORACLE_HOME/rdbms/admin directory, two files exist that create the environment needed for the profiler to execute.

• proftab.sql - Creates three tables and a sequence and must be executed before the profload.sql file.

• profload.sql - Creates the package header and package body for DBMS_PROFILER. This script must be executed as the SYS user.

Once the environment is established, the three tables created by proftab.sql contain the vital information needed to benchmark PL/SQL performance. Queries against these tables will provide the insight needed to optimize the PL/SQL code.

The plsql_profiler_runs table contains information related to a profiling session. Things, such as when the run was started, who started it, and how long the run lasted are contained in this table. This table has the following important columns:

• runid - This is the unique run identifier given to each profiler execution.

• related_run - Runid of related run that can be called by the programmer.

• run_owner - User who started the run.

• run_date - Timestamp of the date of the run.

• run_comment – User provided text concerning anything about this run that they wish to specify. This is used mainly for documentation, since run_id is hard to remember.

• run_total_time – Total elapsed time for this run.
The plsql_profiler_units table defines each PL/SQL component (unit) that was executed during a profiler run. Benchmarks for each of the units are stored in this table in the following columns:

• runid - References plsql_profiler_runs(runid).

• unit_number - Internally generated library unit number.

• unit_type - Library unit type (PACKAGE, PROCEDURE, etc).

• unit_owner - Library unit owner name (the owner of the object).

• unit_name - Library unit name (the name of the object as defined in the user_objects view).

• unit_timestamp – Time when the unit was created. The “unit”, being the procedural object (procedure, function, package). This column holds the same data as the created column in the user_objects view.

• total_time – Total time used by this unit for the given run.

The primary key for this table is runid, unit_number.

The plsql_profiler_data table is where the real performance benchmarks are stored. This table contains the execution statistics for each line of code contained in our PL/SQL unit. This table can be joined to the user_source view and can extract the actual line of code for each benchmark. The primary key includes runid, unit_number, and line#.

The plsql_profiler_data table has the following important columns as indicated by the results of the following query:

select runid, unit_number, line#, total_occur, total_time,
min_time, max_time
from plsql_profiler_data;


RUNID UNIT_NUMBER LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
---------- ----------- ---------- ----------- ---------- ---------- ----------
1 1 8 3 33284677 539733 28918759
1 1 80 2 1134222 516266 617955
1 1 89 0 0 0 0
1 1 90 0 0 0 0
1 1 92 0 0 0 0
1 1 95 0 0 0 0
1 1 103 0 0 0 0
1 1 111 0 0 0 0
1 1 112 0 0 0 0
1 1 116 1 1441523 1441523 1441523
1 1 119 0 0 0 0
1 1 121 1 1431466 1431466 1431466
1 1 123 1 136330 136330 136330
1 1 132 1 978895 978895 978895
1 1 140 0 0 0 0
1 1 141 0 0 0 0
1 1 143 0 0 0 0
1 1 146 1 2905397 2905397 2905397
1 1 152 2 1622552 574374 1048177
1 1 153 0 0 0 0
1 1 157 1 204495 204495 204495
1 1 160 0 0 0 0


The line# above is used to tie these execution benchmarks back to a line of source in the user_source view.

The profload.sql file contains calls to two other files:

• dbmspbp.sql – This file creates the actual sys.dbms_profiler package. This must be created as the SYS user, which is the main drawback of this utility.

• prvtpbp.plb – This file creates the sys.dbms_profiler_lib library object and it is wrapped. Again, this must be executed as the SYS user.

Figure 8.1 depicts the relationships between the three profiler tables, as well as the indirect relationship to the dba_source or user_source view (Source). Note that everything begins with a RUN and drills down to the real performance data for a particular PL/SQL line of code.

The environment is now configured, and the profiling utility is ready to be put to work.
 


To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.


 

  ”call


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.