DBMS PROFILER PLSQL Procedure for tuning in Oracle

DBMS_PROFILER PL/SQL Procedure for tuning in Oracle

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 issue.

-- 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 of runs
PLSQL_PROFILER_UNITS : Program units executed in run
PLSQL_PROFILER_DATA : Profiling data for each line in a program unit

Step 3: Begin the DBMS PROFILER for tracing.

BEGIN
DBMS_OUTPUT.PUT_LINE (
DBMS_PROFILER.START_PROFILER (
'modifiedsalary ' ||
TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS')
)
);

Step 4: Execute the package having performance issue.

exec hr.modifiedsalary;

Step 5: Stop the DBMS PROFILER.

DBMS_OUTPUT.PUT_LINE (
DBMS_PROFILER.STOP_PROFILER);
END;

Step 6: Get report from following scripts.

$ORACLE_HOME/plsql/demo/profrep.sql
$ORACLE_HOME/plsql/demo/profsum.sql

Check the Slowest SQL Query with 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_grand_total p4
WHERE p2.unit_owner NOT IN ('SYS', 'SYSTEM')
AND p1.runid = &&firstparm
AND (p1.total_time >= p4.grand_total / 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;

Advertisements