dbms_profiler

connect / as sysdba
@?\rdbms\admin\profload.sql

connect rene/rene
@?\rdbms\admin\proftab.sql

create table tbl_prof_test (
  yes_or_no char(1),
  i         number
);

create or replace procedure test_profiler_1(p_i number) as
  v_dummy integer;
  hs1     integer;
  hs2     integer;
begin
  insert into tbl_prof_test values('t',p_i);
  if p_i = 1003 then

   --select hsecs into hs1 from sys.v_$timer;
   hs1 := dbms_utility.get_time;
   loop
     --select hsecs into hs2 from sys.v_$timer;
     hs2 := dbms_utility.get_time;

     exit when hs2 = hs1 + 100;
 
   end loop;
    
 --   sys.dbms_lock.sleep(1);
 --   sys.dbms_lock.sleep(10);
    select count(*) into v_dummy from dual; 
  end if;
end test_profiler_1;
/
show errors

create or replace procedure test_profiler as
  v_i   number;
  v_dummy number;
begin


  for v_i in 1 .. 10 loop
    if mod(v_i,3) = 0 then
      v_dummy := 0;
      insert into tbl_prof_test values ('y',v_i);
      test_profiler_1(v_i+1000); 
      v_dummy := 6;
    else
      v_dummy := 9;
      insert into tbl_prof_test values ('n',v_i);
      test_profiler_1(v_i-1000); 
      v_dummy := 3;
    end if;
  end loop;

end;
/

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

set serveroutput on

declare 
  success binary_integer;
begin
  success := dbms_profiler.start_profiler(run_comment => 'test_run');

  if success <> 0 then
    dbms_output.put_line('could not start profiler');
  else
    dbms_output.put_line('Profiler started!');
  end if;
end;
/

exec test_profiler;

select decode(
  dbms_profiler.stop_profiler,
  '0', 'Profiler Stopped',
  'Could not stop profiler')
from
  dual;

exec dbms_profiler.flush_data;

select runid from plsql_profiler_runs where run_comment = 'test_run';

select unit_number, unit_type, unit_name, unit_timestamp, total_time from plsql_profiler_units where runid = &runid;

select * from tbl_prof_test;

drop table tbl_prof_test;

--@?\plsql\demo\profsum