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.
Step 5: Stop the DBMS 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;
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;