The DBMS_PROFILER 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.
With this interface, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level. 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.
You can create profiler tables by running proftab.sql, which is located under $ORACLE_HOME/rdbms/admin directory.
Let us say, with in a large loop, we decided to insert data into a table using INSERT…SELECT…CONNECT BY or INSERT…SELECT…FROM all_objects, and would like to find out, which one will be more efficient. The example is just to illustrate the usage of DBMS_PROFILER in its simple form.
- Create a test table t
SQL> CREATE TABLE t (f VARCHAR2(10), n NUMBER(10)); Table created.
- Create a test procedure proc1 and proc2
SQL> CREATE OR REPLACE PROCEDURE proc1
2 AS
3 BEGIN
4 DBMS_PROFILER.start_profiler('proc1');
5 DBMS_OUTPUT.put_line('Starting CONNECT BY Insert');
6 FOR i IN 1..1000
7 LOOP
8 INSERT INTO t (f, n) SELECT 'proc1', rownum FROM dual CONNECT BY level <= 100;
9 COMMIT;
10 END LOOP;
11 DBMS_OUTPUT.put_line('Finished CONNECT BY Insert');
12 DBMS_PROFILER.stop_profiler;
13 END;
14 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE proc2
2 AS
3 BEGIN
4 DBMS_PROFILER.start_profiler('proc2');
5 DBMS_OUTPUT.put_line('Starting ALL_OBJECTS Insert');
6 FOR i IN 1..1000
7 LOOP
8 INSERT INTO t (f, n) SELECT 'proc2', rownum FROM all_objects WHERE rownum <= 100;
9 COMMIT;
10 END LOOP;
11 DBMS_OUTPUT.put_line('Finished ALL_OBJECTS Insert');
12 DBMS_PROFILER.stop_profiler;
13 END;
14 /
Procedure created.
- Now, Let us execute the procedure proc1 and proc2
SQL> SET SERVEROUTPUT ON SQL> EXECUTE proc1; Starting CONNECT BY Insert Finished CONNECT BY Insert PL/SQL procedure successfully completed. SQL> EXECUTE proc2; Starting ALL_OBJECTS Insert Finished ALL_OBJECTS Insert PL/SQL procedure successfully completed. SQL>
- Examine the profiler data by running the below query:
SQL> BREAK ON runid ON run_owner ON run_comment ON run_secs
SQL> SELECT a.runid, a.run_owner, a.run_comment,
2 a.run_total_time / 1000000000 run_secs, c.total_occur,
3 c.total_time / 1000000000 line_total_secs, c.line#, u.text
4 FROM plsql_profiler_runs a,
5 plsql_profiler_units b,
6 plsql_profiler_data c,
7 user_source u
8 WHERE a.runid = b.runid
9 AND a.runid = c.runid
10 AND b.unit_name = u.NAME
11 AND c.line# = u.line
12 /
RUNID RUN_OWNER RUN_COMMEN RUN_SECS TOTAL_OCCUR LINE_TOTAL_SECS LINE# TEXT
----- --------- ---------- ---------- ----------- --------------- ----- -------------------------------------------------------------
1 PERUMAL proc1 15.17891 0 .00000 1 PROCEDURE proc1
0 .00000 4 DBMS_PROFILER.start_profiler('proc1');
1 .00037 5 DBMS_OUTPUT.put_line('Starting CONNECT BY Insert');
1001 .00984 6 FOR i IN 1..1000
1000 13.93305 8 INSERT INTO t (f, n) SELECT 'proc1', rownum FROM dual
CONNECT BY level <= 100;
1000 1.06284 9 COMMIT;
1 .00003 11 DBMS_OUTPUT.put_line('Finished CONNECT BY Insert');
1 .00002 12 DBMS_PROFILER.stop_profiler;
0 .00000 13 END;
2 PERUMAL proc2 121.93940 0 .00000 1 PROCEDURE proc2
0 .00000 4 DBMS_PROFILER.start_profiler('proc2');
1 .00031 5 DBMS_OUTPUT.put_line('Starting ALL_OBJECTS Insert');
1001 .01023 6 FOR i IN 1..1000
1000 119.87532 8 INSERT INTO t (f, n) SELECT 'proc2', rownum FROM all_objects
WHERE rownum <= 100;
1000 1.98504 9 COMMIT;
1 .00003 11 DBMS_OUTPUT.put_line('Finished ALL_OBJECTS Insert');
1 .00002 12 DBMS_PROFILER.stop_profiler;
0 .00000 13 END;
INSERT statement on proc1 took 13.93305 seconds for 1000 executions and proc2 took 119.87532 for 1000 executions. Imagine a PL/SQL procedure or function with thousands of lines of code, with this you can pinpoint the section of code that deserves your attention for a tuning. Refer to documentation “Oracle Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)” for more details on profiler.
You can download profiler.sql for Reporting PL/SQL Profiler data generated by DBMS_PROFILER in html format. Refer to metalink Document ID 243755.1 titled “Implementing and Using the PL/SQL Profiler“