Tunning PL/SQL through DBMS_PROFILER

What is DBMS_PROFILER

dbms_profiler is oracle supplied package which use for tunning plsql application.
through this package we can tune our plsql procedure, trigger,funtions. and findout where plsql spent more time to execute.

How to install dbms_profiler package

In Basic Oracle Installation "dbms_profiler" package is not created. We have to manually create this packages running below scripts.

conn with SYS user and run "PROFLOAD.SQL"
Located: $ORACLE_HOME/rdbms/admin folder


SQL> conn sys@hgc as sysdba
Enter password:
Connected.
SQL> @d:\oracle\product\10.1.0\db_1\rdbms\admin\profload.sql

Package created.


Grant succeeded.


Synonym created.


Library created.


Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

Conn to application user and run "PROFTAB.SQL", this script create three tables.

1. PLSQL_PROFILER_RUNS
2. PLSQL_PROFILER_UNITS
3. PLSQL_PROFILER_DATA

SQL> conn scott/tiger@hgc
Connected.
SQL> @d:\oracle\product\10.1.0\db_1\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist


drop table plsql_profiler_units cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist


drop table plsql_profiler_runs cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist


drop sequence plsql_profiler_runnumber
*
ERROR at line 1:
ORA-02289: sequence does not exist



Table created.


Comment created.


Table created.


Comment created.


Table created.


Comment created.


Sequence created.

How can use dbms_profiler ?


1. Connect with application user which you want to optimize.
Start dbms_profiler

SQL> exec dbms_profiler.start_profiler('Test procedure by Scott');

Information is store in memory so flash profiler to update their repository.

SQL> exec dbms_profiler.flush_data();

Stop dbms_profiler

SQL> exec dbms_profiler.stop_profiler();


View Information Generated by dbms_profiler

Query in below views
1. plsql_profiler_runs
2. plsql_profiler_units
3. plsql_profiler_data

For more Reference
OracleUtilities
Oracle Documentation