DBMS_PROFILER: Overview and How to Install

DBMS_PROFILER package provides developer a way to profile PL/SQL program unit and determine the performance bottlenecks. DBMS_PROFILER allows database developers to analyze the run time behavior of PL/SQL code and helps you in identifying performance issues by providing you the "number of execution" and "time taken" by each line in the PL/SQL block.

DBMS_PROFILER generates following useful profiler statistics:
- Total elapsed time in execution of whole code.
- Total number of times each line of code was executed.
- Total time spent on execution of each line of code.
- Minimum/Maximum time spent on each line of code in single execution.
- The Code executed for a given scenario and conditions.

DBMS_PROFILER package provides us following 3 important procedures:  
- DBMS_PROFILER.START_PROFILER: start the monitoring process
- DBMS_PROFILER.STOP_PROFILER: stop the monitoring process
- DBMS_PROFILER.FLUSH_DATA: save profiler stats in tables and flush the memory.

Note: if you are using DBMS_PROFILER for the very first time, you may need to install it. The installation scripts are located at "$ORACLE_HOME/rdbms/admin"

How to install DBMS_PROFILER package:
Installation of DBMS_PROFILER package is just a 2 step process.
1. execute "@$ORACLE_HOME/rdbms/admin/profload.sql" as sys user
2. execute "@$ORACLE_HOME/rdbms/admin/proftab.sql" by the user on which you want to use DBMS_PROFILER.

Step 2 will create following tables where profiling data will get stored, from which we can easy extract the data to determine the performance bottlenecks.
- PLSQL_PROFILER_RUNS
- PLSQL_PROFILER_UNITS
- PLSQL_PROFILER_DATA

Step: 1 - profload.sql

C:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 13:29:55 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> @E:\oracle\app\nimish.garg\product\11.2.0\dbhome_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.

Step: 2 - proftab.sql

C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 13:33:18 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> @E:\oracle\app\nimish.garg\product\11.2.0\dbhome_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.

Read Next: DBMS_PROFILER: How to analyze PL/SQL performance


Related Links:
Related Posts:
- Query Optimization Tips for Oracle
- Append String to CLOB in Optimized way
- Oracle : TKPROF simple steps
- SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled