DBMS PROFILER PLSQL Procedure for tuning in Oracle

Tune PL/SQL package with DBMS_PROFILER in Oracle performance issue

Step 1: Check that DBMS_PROFILER is installed.

SQL> DESC DBMS_PROFILER
If you then see the message:
ORA-04043: object dbms_profiler does not exist

Run the following script with sysdba for installation if not exists
sqlplus / as sysdba
$ORACLE_HOME/rdbms/admin/dbmspbp.sql
$ORACLE_HOME/rdbms/admin/prvtpbp.plb

Step 2: Connect with schema on which need to check for performance issues.

 -- Connected the HR Schema
 conn hr/hr

Execute the following script with schema so that it will create tables:

$ORACLE_HOME/rdbms/admin/proftab.sql

--Following table is created:
PLSQL_PROFILER_RUNS : Parent table is having one entry of each run.
PLSQL_PROFILER_UNITS : Program which involved in main program like function, procedure or other units executed in run.
PLSQL_PROFILER_DATA : It has complete information about the line wise code which line is taking how much time. 

Join Condition: 
PLSQL_PROFILER_RUNS and PLSQL_PROFILER_UNITS has RUNID column common.
PLSQL_PROFILER_RUNS and PLSQL_PROFILER_DATA has RUNID Column common.
PLSQL_PROFILER_UNITS and PLSQL_PROFILER_DATA has UNIT_NUMBER column common.

Step 3: Begin the DBMS PROFILER for tracing.

 exec DBMS_PROFILER.START_PROFILER ('modifiedsalary' );

Step 4: Execute the package having performance issues.

 exec hr.modifiedsalary;

Step 5: Stop the DBMS PROFILER.

 exec DBMS_PROFILER.STOP_PROFILER();

Step 6: Get report data from the three tables created.

SELECT * FROM PLSQL_PROFILER_RUNS;
SELECT * FROM PLSQL_PROFILER_UNITS; 
SELECT * FROM PLSQL_PROFILER_DATA; 

Check the Slowest SQL Query with the following command:

 SELECT TO_CHAR(p1.total_time/10000000,'99999999')
 || '-' || TO_CHAR (p1.total_occur) AS time_count,
 p2.unit_owner || '.' || p2.unit_name unit,TO_CHAR (p1.line#)
 || '-' || p3.text text
 FROM plsql_profiler_data p1, plsql_profiler_units p2,
 all_source p3, plsql_profiler_runs p4
 WHERE p2.unit_owner NOT IN ('SYS', 'SYSTEM')
 AND (p1.total_time >= p4.run_total_time / 100)
 AND p1.runid = p2.runid
 AND p2.unit_number = p1.unit_number
 AND p3.TYPE = 'PACKAGE BODY'
 AND p3.owner = p2.unit_owner
 AND p3.line = p1.line#
 AND p3.NAME = p2.unit_name
 ORDER BY p1.total_time DESC;

Leave a Reply

Fill in your details below or click an icon to log in:

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.