Using DBMS_PROFILER To Identify PL-SQL Performance.

Note : In this articular I would like to describe what is DBMS_PROFILER and how to use it.

DBMS_PROFILER:- The DBMS_PROFILER package are use to allow developers to profile the run-time behaviour of PL/SQL code. Using this package you can find out each line execution time.

How to use ?

This package use three tables to collect statistic of PL-SQL code.

PLSQL_PROFILER_DATA - information on profiler runs
PLSQL_PROFILER_RUNS - information on each line profiled
PLSQL_PROFILER_UNITS - profiler data for each line profiled

1. Go to location (D:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN) to find two scripts.
     proftab.sql  -- For create profile tables.
     profload.sql -- For load package DBMS_PROFILER

2. Now connect sys user using sqlplus from  RDBMS\ADMIN Location.
  [D:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN>sqlplus sys/sys as sysdba]

3. Now run this
 SQL>@profload.sql

4. Now connect another user to check pl/sql performance and execute proftab.sql file. I connect RND user in my database.
SQL> conn rnd/rnd;
Connected.
SQL> @proftab.sql

5. Now check the performance .....

SQL> CREATE TABLE E
  2  AS SELECT * FROM EMP;

Table created.


SQL> CREATE OR REPLACE PROCEDURE DPR_EMP_INSERT
  2  IS
  3  BEGIN
  4  FOR I IN 1 .. 100 LOOP
  5   FOR I IN (SELECT * FROM E) LOOP
  6    INSERT INTO Emp(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
  7    VALUES (I.EMPLOYEE_ID, I.FIRST_NAME, I.LAST_NAME, I.EMAIL, I.PHONE_NUMBER, I.HIRE_DATE, I.JOB_ID, I.SALARY, I.COMMISSION_PCT, I.MANAGER_ID, I.DEPARTMENT_ID);
  8   END LOOP;
  9   END LOOP;
 10   COMMIT;
 11  END;
 12  /

Procedure created.

SQL>DECLARE
 2 v_result  BINARY_INTEGER;
 3 BEGIN
 4 v_result := DBMS_PROFILER.start_profiler;
 5 DPR_EMP_INSERT;
 6 v_result := DBMS_PROFILER.stop_profiler;
 7 END;

SQL>set line 2000
SQL> COLUMN RUN_OWNER FORMAT A10
SQL> COLUMN TEXT FORMAT A40
SQL> COLUMN LINE_TOTL_SECS FORMAT 99,99999999
SQL> COLUMN MIN_TIME FORMAT 99,99999999
SQL> COLUMN MAX_TIME FORMAT 99,99999999
SQL> COLUMN TOTL_RUN_SECS FORMAT 99,99999999
SQL>
SQL> SELECT C.LINE#, U.TEXT,C.TOTAL_OCCUR,
  2              TO_CHAR(C.TOTAL_TIME/1000000000,'99.999999999') LINE_TOTL_SECS,
  3              TO_CHAR(C.MIN_TIME/1000000000,'99.999999999') MIN_TIME,
  4              TO_CHAR(C.MAX_TIME/1000000000,'99.999999999') MAX_TIME,
  5              TO_CHAR(A.RUN_TOTAL_TIME/1000000000,'99.999999999') TOTL_RUN_SECS,
  6              A.RUN_OWNER
  7  FROM PLSQL_PROFILER_RUNS A,
  8            PLSQL_PROFILER_UNITS B,
  9            PLSQL_PROFILER_DATA C,
 10            USER_SOURCE U
 11  WHERE A.RUNID=B.RUNID
 12      AND A.RUNID=C.RUNID
 13      AND B.UNIT_NAME=U.NAME
 14      AND C.LINE#=U.LINE;

     LINE# TEXT                                     TOTAL_OCCUR LINE_TOTL_SEC MIN_TIME      MAX_TIME   TOTL_RUN_SECS RUN_OWNER
---------- ---------------------------------------- ----------- ------------- ------------- ------------- ------------- ----------
         1 PROCEDURE DPR_EMP_INSERT                           1    .000007488    .000007488    .000007488   1.937000000 TEST
         1 PROCEDURE DPR_EMP_INSERT                           0    .000000000    .000000000    .000000000   1.937000000 TEST
         2 IS                                                 0    .000000000    .000000000    .000000000   1.937000000 TEST
         3 BEGIN                                              1    .000000000    .000000000    .000000000   1.937000000 TEST
         3 BEGIN                                              2    .000022201    .000000599    .000021601   1.937000000 TEST
         4 FOR I IN 1 .. 100 LOOP                           101    .000034279    .000000137    .000000593   1.937000000 TEST
         4 FOR I IN 1 .. 100 LOOP                             1    .000003214    .000003214    .000003214   1.937000000 TEST
         5  FOR I IN (SELECT * FROM E) LOOP               10900    .425847635    .000000098    .000573357   1.937000000 TEST
         5  FOR I IN (SELECT * FROM E) LOOP                   0    .000000000    .000000000    .000000000   1.937000000 TEST
         6   INSERT INTO Emp(EMPLOYEE_ID, FIRST_NAM       10700   1.494573068    .000127229    .001236445   1.937000000 TEST
           E, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_

     LINE# TEXT                                     TOTAL_OCCUR LINE_TOTL_SEC MIN_TIME      MAX_TIME   TOTL_RUN_SECS RUN_OWNER
---------- ---------------------------------------- ----------- ------------- ------------- ------------- ------------- ----------
           DATE, JOB_ID, SALARY, COMMISSION_PCT, MA
           NAGER_ID, DEPARTMENT_ID)

         8  END LOOP;                                       100    .000000000    .000000000    .000000000   1.937000000 TEST
         9  END LOOP;                                         1    .000000000    .000000000    .000000000   1.937000000 TEST
        10  COMMIT;                                           1    .000343027    .000343027    .000343027   1.937000000 TEST
        11 END;                                               1    .000005140    .000005140    .000005140   1.937000000 TEST

14 rows selected.