How to use DBMS_PROFILER package?

DBMS_PROFILER: It is the PL/SQL code tuning technique. It allows you to check the run time behavior of your PL/SQL code and helps you in identifying the areas where the performance issue is. The output of DBMS_PROFILER package is very easy to read as it gives the execution time for each line of code and from there you can easily
identify the bottleneck.

Profiling is for the developer to understand where the PL/SQL code is spending the most time, so they can detect and optimize it. DBMS_PROFILER is to PL/SQL, what tkprof and Explain Plan are to SQL.

DBMS_PROFILER package has some subprograms like:

1. FLUSH_DATA Function and Procedure: Flushes profiler data collected in the user's session.
2. GET_VERSION Procedure: Gets the version of this API.
3. INTERNAL_VERSION_CHECK Function: Verifies that this version of the DBMS_PROFILER package can work with the implementation in the database.
4. PAUSE_PROFILER Function and Procedure: Pauses profiler data collection.
5. RESUME_PROFILER Function and Procedure: Resumes profiler data collection.
6. START_PROFILER Functions and Procedures: Starts profiler data collection in the user's session.
7. STOP_PROFILER Function and Procedure: Stops profiler data collection in the user's session.
   

To execute the DBMS_PROFILER package we need to do certain settings in our oracle environment. There are two files that create the environment setting for DBMS_PROFILER:

• .proftab.sql : This file creates three tables (PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, PLSQL_PROFILER_DATA) and sequence and must be executed first from the oracle user from which the profiling is to be done. This needs to be executed before profload.sql.
• .profload.sql : This file creates the specification and the body of DBMS_PROFILER package. This needs to be executed from sys user only. Some public synonyms also needs to be created for the tables to for the other oracle user (from which the profiling is to be done).

Following are the steps to do the settings and the execution of the DBMS_PROFILER (using a UNIX platform):

(We will consider an example, Oracle user "TRACETEST" needs profiling to be done on a sample package "TEST_PROFILER")

Step 1: Go to the admin directory under ORACLE_HOME/rdbms using the command "cd $ORACLE_HOME/rdbms/admin".

Step 2: Connect as tracetest (Please make sure that the user has CREATE SEQUENCE, CREATE TABLE and CREATE PROCEDURE privilege) the following commands:
sqlplus /nolog
connect tracetest/tracetest

Step 3: Run proftab.sql file using the command
@$ORACLE_HOME/rdbms/admin/proftab.sql

Step 4: Grant some privileges to PUBLIC for the tables created using the following commands:
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

Step 5: Create a test_profiler procedure:
CREATE OR REPLACE PROCEDURE test_profiler AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. 50 LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
/

Step 6: Connect to SYSDBA user to run proftab.sql file using command "connect / as sysdba".

Step 7: Run profload.sql using the command
@$ORACLE_HOME/rdbms/admin/profload.sql

Step 8: Create PUBLIC SYNONYM using the following commands for "tracetest user":
CREATE PUBLIC SYNONYM plsql_profiler_runs FOR tracetest.plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR tracetest.plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR tracetest.plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR tracetest.plsql_profiler_runnumber;

Now, we are all set to run the DBMS_PROFILER package to check the runtime execution of the procedure TEST_PROFILER.

Step 9: To run the profiler:

DECLARE
l_result BINARY_INTEGER;
BEGIN
l_result := DBMS_PROFILER.start_profiler(run_comment => 'test_profiler_execution: ' || SYSDATE);
test_profiler;
l_result := DBMS_PROFILER.stop_profiler;
END;
/

Step 10: Syntax to see which run happened: (RUNID is the unique identifier associated to each run)

SET LINESIZE 200
SET TRIMOUT ON

COLUMN runid FORMAT 99999
COLUMN run_comment FORMAT A50
SELECT runid,
run_date,
run_comment,
run_total_time
FROM plsql_profiler_runs
ORDER BY runid;



Step 11: Syntax to see the details of runid of the runid you got from the above query:

COLUMN runid FORMAT 99999
COLUMN unit_number FORMAT 99999
COLUMN unit_type FORMAT A20
COLUMN unit_owner FORMAT A20

SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time,
d.min_time,
d.max_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = 1
ORDER BY u.unit_number, d.line#;





Here we can see that the line no. 5 executed less times then line no. 4 but took lots of time.

Step 12: To check the line numbers of the source code:

SELECT line||' : ' ||text
FROM all_source
WHERE owner = 'TRACETEST'
AND type = 'PROCEDURE'
AND name = 'TEST_PROFILER';

LINE||' : '||TEXT
---------------------------------------------------
1 : CREATE OR REPLACE PROCEDURE test_profiler AS
2 : l_dummy NUMBER;
3 : BEGIN
4 : FOR i IN 1 .. 50 LOOP
5 : SELECT l_dummy + 1
6 : INTO l_dummy
7 : FROM dual;
8 : END LOOP;
9 : END;

We can easily identify that the loop didn't took much time in execution while the SQL query itself
took more time to execute. So, we can conclude that SQL_TRACE or other facilities can be used to tune the SQL further.

PS: Syntax for other DBMS_PROFILER options are:

DBMS_PROFILER.FLUSH_DATA;
DBMS_PROFILER.PAUSE_PROFILER;
DBMS_PROFILER.RESUME_PROFILER;
DBMS_PROFILER.INTERNAL_VERSION_CHECK
RETURN BINARY_INTEGER;
DBMS_PROFILER.GET_VERSION (
major OUT BINARY_INTEGER,
minor OUT BINARY_INTEGER);