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.
No comments:
Post a Comment