Performance Measurement of PLSQL code by DBMS_PROFILER

If you are not already configured DBMS_PROFILE package look the following script in Oracle Home->rdbms->admin

SCRIPT : PROFLOAD.SQL and PROFTAB.SQL

SETUP :

U:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 14 13:26:03 2003

SQL> connect sys/***** as sysdba

Connected.

SQL> @D:\oracle\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.

Create the profiler tables

SQL> connect hr/hr

Connected.

SQL> @D:\oracle\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.

The above script will create the following table

PLSQL_PROFILER_DATA TABLE

PLSQL_PROFILER_RUNS TABLE

PLSQL_PROFILER_UNITS TABLE

Let us create a sample table and a procedure for demonstration

SQL> create table testemp(emp number);

Table created.

CREATE OR REPLACE PROCEDURE hr.testproc

i NUMBER := 0;

vempno NUMBER;

BEGIN

WHILE i < 100000

LOOP

INSERT INTO hr.testemp

(emp

)

VALUES (i

);

COMMIT;

i := i + 1;

END LOOP;

SELECT COUNT (*)

INTO vempno

FROM hr.testemp;

DBMS_OUTPUT.put_line ('No of record' || vempno);

DELETE FROM hr.testemp;

COMMIT;

FOR i IN 1 .. 100000

LOOP

INSERT INTO hr.testemp

(emp

)

VALUES (i

);

COMMIT;

END LOOP;

END testproc;

The following PL/SQL will call the profile

SQL> Set serveroutput on;

SQL>

SQLDECLARE

vrun NUMBER;

BEGIN

vrun := SYS.DBMS_PROFILER.start_profiler ('TESTRUN1');

DBMS_OUTPUT.put_line ('START PROFILER STATUS ' || vrun);

hr.testproc; -- calling procedure

vrun := SYS.DBMS_PROFILER.stop_profiler;

DBMS_OUTPUT.put_line ('STOP PROFILER STATUS ' || vrun);

DBMS_OUTPUT.put_line ('0 successful');

DBMS_OUTPUT.put_line ('1 incorrect parameter');

DBMS_OUTPUT.put_line ('2 data flush operation failed');

DBMS_OUTPUT.put_line ('-1 version mismatch between package and tables');

END;

Expected Output

START PROFILER STATUS 0

No of record100000

STOP PROFILER STATUS 0

0 successful

1 incorrect parameter

2 data flush operation failed

-1 version mismatch between package and tables

PL/SQL procedure successfully completed.

Now run the sql to find out the most extensive part of the plsql

SET linesize 132

COL unit format a20

COL tc format 9999999 heading "Exection"

COL stext format a40

SELECT u.unit_owner || '.' || u.unit_name unit, line#,

ROUND (d.total_time / 1000000000) total_time_s, d.total_occur tc,

SUBSTR (s.text, 1, 40) stext

FROM plsql_profiler_runs r,

plsql_profiler_units u,

plsql_profiler_data d,

all_source s

WHERE r.run_comment = 'TESTRUN1'

AND r.runid = u.runid

AND d.runid = u.runid

AND u.unit_number = d.unit_number

AND s.owner = u.unit_owner

AND s.TYPE = u.unit_type

AND s.NAME = u.unit_name

AND s.line = d.line#

ORDER BY line#

Expected Output

UNIT LINE# TIME[s] Exection STEXT

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

HR.TEST 3 0.000 1 i number := 0;

HR.TESTPROC 6 58.000 100001 while i < 100000

HR.TESTPROC 8 4,951.000 100000 insert into hr.testemp(emp ) values (i);

HR.TESTPROC 9 2,901.000 100000 commit;

HR.TESTPROC 10 131.000 100000 i := i + 1;

HR.TESTPROC 12 6.000 1 select count(*) into vempno from hr.test

HR.TESTPROC 13 0.000 2 dbms_output.put_line('No of record'||vem

HR.TESTPROC 14 1,461.000 1 delete from hr.testemp;

HR.TESTPROC 15 0.000 1 commit;

HR.TESTPROC 16 70.000 100001 for i in 1..100000

HR.TESTPROC 18 5,371.000 100000 insert into hr.testemp(emp ) values (i);

HR.TESTPROC 19 2,955.000 100000 commit;

Use Time [s] value to determine only those areas where performance is slow. It may not be equal to the elapsed time.

Hope the above information will help …

-- Author

Sameer Wadhwa

Disclaimer:

The author does not guarantee that this information is error-free.

If any errors are found, please report them to author at Wadhwa_s@hotmail.com or SamOracle@Yahoo.com

Ask and Solve Database Problems at OraTechSupportGroup

What do you think about Oracle Techniques?