How to use DBMS_PROFILER in PLSQL for running the procedure or function in Oracle Database

Ask Oracle DBA's to setup DBMS_PROFILER package before we use.

Create your PLSQL procedure or function in your instance.

SQL> execute dbms_profiler.start_profiler('YOUR_OBJECT');

PL/SQL procedure successfully completed.

SQL> exec YOUR_OBJECT;

PL/SQL procedure successfully completed.

SQL> execute dbms_profiler.stop_profiler;

PL/SQL procedure successfully completed.

See the execution timings by running this query:

select s.text ,
       p.total_occur ,
       p.total_time/1000000000 total_time,
       p.min_time/1000000000 min_time,
       p.max_time/1000000000 max_time
from plsql_profiler_data p, user_source s, plsql_profiler_runs r
where p.line# = s.line
and   p.runid = r.runid
and   r.run_comment = 'YOUR_OBJECT'
and   s.name ='YOUR_OBJECT'


Ex:

Step 1)
create table tab (col1 varchar2(30), col2 varchar2(30));

Step 2)
create or replace procedure TEST
is
 vNumber number;
begin
 for i in 1..100000 loop
   vNumber := dbms_random.random;
   insert into tab values (vNumber,vNumber);
 end loop;
end;


Step 3)
SQL> execute dbms_profiler.start_profiler('TEST');

PL/SQL procedure successfully completed.

SQL> exec TEST;

PL/SQL procedure successfully completed.

SQL> execute dbms_profiler.stop_profiler;

PL/SQL procedure successfully completed.

Step 4)
SQL> select s.text ,
  2         p.total_occur ,
  3         p.total_time/1000000000 total_time,
  4         p.min_time/1000000000 min_time,
  5         p.max_time/1000000000 max_time
  6  from plsql_profiler_data p, user_source s, plsql_profiler_runs r
  7  where p.line# = s.line
  8  and   p.runid = r.runid
  9  and   r.run_comment = 'TEST'
 10* and   s.name ='TEST'
SQL> /

TEXT                           TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
------------------------------ ----------- ---------- -------- --------
procedure binds                          1         .0       .0       .0
procedure binds                          3         .0       .0       .0
procedure binds                          0         .0       .0       .0
for i in 1..100000 loop             100001         .0       .0       .0
vNumber := dbms_random.random;      100000         .2       .0       .0
insert into t1 values (vNumber      100000        6.9       .0       .4
,vNumber);              
end;                                     1         .0       .0       .0
procedure binds                          2         .0       .0       .0

8 rows selected.