DBMS_PROFILER

The package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.
This package enables the collection of profiler (performance) data for performance improvement or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts.
This information includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
The PROFTAB.SQL script creates tables with the columns, datatypes, and definitions as shown in the following tables

·       PLSQL_PROFILER_RUNS

·       PLSQL_PROFILER_UNITS

·       PLSQL_PROFILER_DATA

In 10g and beyond, the DBMS_PROFILER package is loaded automatically when the database is created, and PROFLOAD.SQL is no longer needed.

DBMS_PROFILER Operational Notes

These notes describe a typical run, how to interpret output, and two methods of exception generation.

Typical Run

Improving application performance is an iterative process. Each iteration involves the following steps:

1.     Running the application with one or more benchmark tests with profiler data collection enabled.

2.     Analyzing the profiler data and identifying performance problems.

3.     Fixing the problems.

The PL/SQL profiler supports this process using the concept of a "run". A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the ending of a run by calling the START_PROFILER and STOP_PROFILER functions.
The user must first create database tables in the profiler user's schema to collect the data. The PROFTAB.SQL script creates the tables and other data structures required for persistently storing the profiler data.
Note that running PROFTAB.SQL drops the current tables. The PROFTAB.SQL script is in the RDBMS/ADMIN directory. Some PL/SQL operations, such as the first execution of a PL/SQL unit, may involve I/O to catalog tables to load the byte code for the PL/SQL unit being executed. Also, it may take some time executing package initialization code the first time a package procedure or function is called.
To avoid timing this overhead, "warm up" the database before collecting profile data. To do this, run the application once without gathering profiler data.
You can allow profiling across all users of a system, for example, to profile all users of a package, independent of who is using it. In such cases, the SYSADMIN should use a modified PROFTAB.SQL script which:

·       Creates the profiler tables and sequence

·       Grants SELECT/INSERT/UPDATE on those tables and sequence to all users

·       Defines public synonyms for the tables and sequence

Note:

Do not alter the actual fields of the tables.

A typical run then involves:

·       Starting profiler data collection in the run.

·       Executing PL/SQL code for which profiler and code coverage data is required.

·       Stopping profiler data collection, which writes the collected data for the run into database tables

Note:

The collected profiler data is not automatically stored when the user disconnects. You must issue an explicit call to the FLUSH_DATA or the STOP_PROFILER function to store the data at the end of the session. Stopping data collection stores the collected data.

As the application executes, profiler data is collected in memory data structures that last for the duration of the run. You can call the FLUSH_DATA function at intermediate points during the run to get incremental data and to free memory for allocated profiler data structures. Flushing the collected data involves storing collected data in the database tables created earlier.

Interpreting Output

The table plsql_profiler_data contains one row for each line of the source unit for which code was generated. The line# value specifies which source line. If the row exists, and the total_occur value in that row is > 0, some code associated with that line was executed. If the row exists, and total_occur value is 0, no code associated with that line was executed. If the row doesn't exist in the table, no code was generated for that line, and therefore it should not be mentioned in reports
If the source of a single statement is on a single line, any code generated for that statement will be attributed to that line number. (In some cases, such as a simple declaration, or because of optimization, no code will be needed). To get coverage information, units should be compiled with PLSQL_OPTIMIZE_LEVEL=1.
If a statement spans multiple lines, any code generated for that statement will be attributed to some line in the range, but it is not guaranteed that every line in the range will have code attributed to it. In such a case there will be gaps in the set of line# values. In particular, multi-line SQL-related statements may appear to be on a single line (usually the first). This is because PL/SQL passes the processed text of the cursor to the SQL engine; therefore, as far as PL/SQL is concerned, the entire SQL statement is a single indivisible operation.
When multiple statements are on the same line, the profiler will combine the occurrences for each statement. This may be confusing if a line has embedded control flow. For example, if 'then ...' and 'else ...' are on the same line, it will not be possible to determine whether the 'then' or the 'else' was taken.
In general, profiler and coverage reports are most easily interpreted if each statement is on its own line.

Two Methods of Exception Generation

Each routine in this package has two versions that allow you to determine how errors are reported.

·       A function that returns success/failure as a status value and will never raise an exception

·       A procedure that returns normally if it succeeds and raises an exception if it fails

In each case, the parameters of the function and procedure are identical. Only the method by which errors are reported differs. If there is an error, there is a correspondence between the error codes that the functions return, and the exceptions that the procedures raise.

FLUSH_DATA Function and Procedure

This function flushes profiler data collected in the user's session. The data is flushed to database tables, which are expected to preexist.

Note:

Use the PROFTAB.SQL script to create the tables and other data structures required for persistently storing the profiler data.

Syntax

DBMS_PROFILER.FLUSH_DATA 

  RETURN BINARY_INTEGER;

DBMS_PROFILER.FLUSH_DATA;

GET_VERSION Procedure

This procedure gets the version of this API.

Syntax

DBMS_PROFILER.GET_VERSION ( 

   major  OUT BINARY_INTEGER, 

   minor  OUT BINARY_INTEGER); 

Parameter	Description
major	Major version of DBMS_PROFILER.
minor	Minor version of DBMS_PROFILER.

 

INTERNAL_VERSION_CHECK Function

This function verifies that this version of the DBMS_PROFILER package can work with the implementation in the database.

Syntax

DBMS_PROFILER.INTERNAL_VERSION_CHECK 

  RETURN BINARY_INTEGER; 

 

PAUSE_PROFILER Function and Procedure

This function pauses profiler data collection.

Syntax

DBMS_PROFILER.PAUSE_PROFILER 

  RETURN BINARY_INTEGER; 

DBMS_PROFILER.PAUSE_PROFILER; 

 

RESUME_PROFILER Function and Procedure

This function resumes profiler data collection.

Syntax

DBMS_PROFILER.RESUME_PROFILER 

  RETURN BINARY_INTEGER; 

DBMS_PROFILER.RESUME_PROFILER; 

 

START_PROFILER Functions and Procedures

This function starts profiler data collection in the user's session.
There are two overloaded forms of the START_PROFILER function; one returns the run number of the started run, as well as the result of the call. The other does not return the run number. The first form is intended for use with GUI-based tools controlling the profiler.

Syntax

DBMS_PROFILER.START_PROFILER(

   run_comment   IN VARCHAR2 := sysdate,

   run_comment1  IN VARCHAR2 :='',

   run_number    OUT BINARY_INTEGER)

 RETURN BINARY_INTEGER;

DBMS_PROFILER.START_PROFILER(

   run_comment IN VARCHAR2 := sysdate,

   run_comment1 IN VARCHAR2 :='')

RETURN BINARY_INTEGER;

DBMS_PROFILER.START_PROFILER(

   run_comment   IN VARCHAR2 := sysdate,

   run_comment1  IN VARCHAR2 :='',

   run_number    OUT BINARY_INTEGER);

DBMS_PROFILER.START_PROFILER(

   run_comment IN VARCHAR2 := sysdate,

   run_comment1 IN VARCHAR2 :='');

Parameters

Table 124-7 START_PROFILER Function Parameters

Parameter	Description
run_comment	Each profiler run can be associated with a comment. For example, the comment could provide the name and version of the benchmark test that was used to collect data.
run_number	Stores the number of the run so you can store and later recall the run's data.
run_comment1	Allows you to make interesting comments about the run.

STOP_PROFILER Function and Procedure

This function stops profiler data collection in the user's session.
This function has the side effect of flushing data collected so far in the session, and it signals the end of a run.

Syntax

DBMS_PROFILER.STOP_PROFILER 

  RETURN BINARY_INTEGER; 

DBMS_PROFILER.STOP_PROFILER;

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.

DBMS_PROFILER: How to analyze pl/sql performance

To analyze the PL/SQL code and identifying performance issues using DBMS_PROFILER, 

• we need to first start the profiler using DBMS_PROFILER.START_PROFILER, 

• then we can execute the our pl/sql procedure we want monitored 

• and at last we need to simply call DBMS_PROFILER.STOP_PROFILER to stop the profiler.

 We do not need to call DBMS_PROFILER.FLUSH_DATA explicitly as DBMS_PROFILER.STOP_PROFILE flush profiler data automatically.

To analyze PL/SQL and identify bottlenecks, we can break the use of DBMS_PROFILER in following steps:

1.      Collect Profiler data for PL/SQL Block

2.      Identify RUNID using PLSQL_PROFILER_RUNS

3.      Identify UNIT_NUMBER using PLSQL_PROFILER_UNITS

4.      Identify PL/SQL Line Number which may have performance issue by PLSQL_PROFILER_DATA

5.      Get the Line of Code by USER_SOURCE

Step 1: Collect Profiler data  

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 14:21:35 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> exec dbms_profiler.start_profiler('Test SP_CREATE_CSV');

PL/SQL procedure successfully completed.

SQL> exec SP_CREATE_CSV;

PL/SQL procedure successfully completed.

SQL> exec dbms_profiler.stop_profiler;

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

 

Step 2. Identify RUNID using PLSQL_PROFILER_RUNS

SQL> select runid, run_owner, run_date, run_total_time

  2  from plsql_profiler_runs

  3  where run_comment='Test SP_CREATE_CSV';

     RUNID RUN_OWNER                        RUN_DATE  RUN_TOTAL_TIME

---------- -------------------------------- --------- --------------

         1 SCOTT                            26-FEB-13     2.3313E+10

Step 3. Identify UNIT_NUMBER using PLSQL_PROFILER_UNITS

SQL> select unit_number, unit_timestamp, total_time

  2  from plsql_profiler_units

  3  where runid=1 and unit_name='SP_CREATE_CSV';

UNIT_NUMBER UNIT_TIME TOTAL_TIME

----------- --------- ----------

          3 26-FEB-13          0

Step 4. Identify problematic PL/SQL Line Number by PLSQL_PROFILER_DATA

SQL> select line#, total_occur, total_time, min_time, max_time,

  2  round(total_time/total_occur,0) avg_time

  3  from plsql_profiler_data

  4  where runid=1 and unit_number=3

  5  order by avg_time desc;

     LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME   AVG_TIME

---------- ----------- ---------- ---------- ---------- ----------

         3           1     113561        372     112488     113561

         7           3     206807         96     193721      68936

         6           1      19143      19143      19143      19143

        17           1      11097      11097      11097      11097

         1           1      10643      10643      10643      10643

         9          14      14857        640       5963       1061

        11          14      11386        673       1891        813

        12          14       8631        553        673        617

        10          14       6616        412        995        473

        13          14       6024        369        496        430

        16           1        336        336        336        336

        14          14       4273         75        396        305

Now we have all the details required to get bottlenecks of SP_CREATE_CSV. Now we know that line number 3,7 of SP_CREATE_CSV are the top two time consuming statements on an average (basis of single execution). We just need to check what code of lines are they.

Step 5. Get the Line of Code by USER_SOURCE

SQL> column text format a60;

SQL> select line, text from user_source

  2  where name='SP_CREATE_CSV'

  3  and line in (3,7);

      LINE TEXT

---------- ---------------------------------------------------------

         3 CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME

           FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;

        7  FOR C1_R IN C1