Oracle PL/SQL Tutorial/System Packages/DBMS PROFILER

Call dbms_profiler.start_profiler to start profiler

   <source lang="sql">

SQL> SQL> SQL> exec dbms_profiler.start_profiler( "factorial iterative" ) BEGIN dbms_profiler.start_profiler( "factorial iterative" ); END; SQL> SQL> SQL> create or replace

 2  function fact_iterative( n int ) return number
 3  as
 4          l_result number default 1;
 5  begin
 6          for i in 2 .. n
 7          loop
 8                  l_result := l_result * i;
 9          end loop;
10          return l_result;
11  end;
12  /

Function created. SQL> SQL> begin

 2      for i in 1 .. 50 loop
 3          dbms_output.put_line( fact_iterative(50) );
 4      end loop;
 5  end;
 6  /

30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 PL/SQL procedure successfully completed. SQL> SQL> SQL> exec dbms_profiler.stop_profiler PL/SQL procedure successfully completed. SQL></source>

Capture DBMS_PROFILER information for the specified script

   <source lang="sql">

SQL> define script="&1"; Enter value for 1: SQL> set verify off; SQL> SQL> declare

 2
 3  n_run_number                          number;
 4
 5  begin
 6    DBMS_PROFILER.start_profiler("&script"||" on "||to_char(SYSDATE, "YYYYMMDD HH24MISS"),
 7      " ",
 8      n_run_number);
 9
10    DBMS_OUTPUT.PUT_LINE("DBMS_PROFILER run_number = "||to_char(n_run_number));
11  end;
12  /

SQL> SQL> SQL> execute DBMS_PROFILER.stop_profiler; PL/SQL procedure successfully completed. SQL> execute DBMS_PROFILER.flush_data; PL/SQL procedure successfully completed. SQL> SQL> set verify on; SQL> SQL></source>

DBMS_PROFILER.get_version

   <source lang="sql">

SQL> declare

 2      n_major number;
 3      n_minor number;
 4
 5  begin
 6    DBMS_PROFILER.get_version(n_major, n_minor);
 7
 8    DBMS_OUTPUT.PUT_LINE("DBMS_PROFILER Version "||to_char(n_major)||"."||to_char(n_minor));
 9
10    DBMS_OUTPUT.PUT_LINE(DBMS_PROFILER.internal_version_check);
11  end;
12  /

PL/SQL procedure successfully completed. SQL> SQL></source>

dbms_profiler.start_profiler

   <source lang="sql">

SQL> SQL> create or replace procedure do_mod

 2  as
 3      cnt number := 0;
 4  begin
 5      dbms_profiler.start_profiler( "mod" );
 6
 7      for i in 1 .. 500000
 8      loop
 9          cnt := cnt + 1;
10          if ( mod(cnt,1000) = 0 )
11          then
12              commit;
13          end if;
14      end loop;
15
16      dbms_profiler.stop_profiler;
17  end;
18  /

Procedure created. SQL> SQL> create or replace procedure no_mod

 2  as
 3      cnt number := 0;
 4  begin
 5      dbms_profiler.start_profiler( "no mod" );
 6      for i in 1 .. 500000
 7      loop
 8          cnt := cnt + 1;
 9          if ( cnt = 1000 )
10          then
11              commit;
12              cnt := 0;
13          end if;
14      end loop;
15      dbms_profiler.stop_profiler;
16  end;
17  /

Procedure created.

SQL> SQL> SQL></source>

Demonstrate DBMS_PROFILER

   <source lang="sql">

SQL> SQL> create or replace procedure modtest

 2  as
 3  begin
 4    dbms_profiler.start_profiler("mod");
 5    for x in 1..1000 loop
 6      if mod(x,100) = 0 then null; end if;
 7    end loop;
 8    dbms_profiler.stop_profiler;
 9  end;
10  /

SQL> SQL></source>

Profiler for recursive function

   <source lang="sql">

SQL> SQL> exec dbms_profiler.start_profiler( "factorial recursive" ) BEGIN dbms_profiler.start_profiler( "factorial recursive" ); END; SQL> SQL> create or replace

 2  function fact_recursive( n int ) return number
 3  as
 4  begin
 5          if ( n = 1 )
 6          then
 7                  return 1;
 8          else
 9                  return n * fact_recursive(n-1);
10          end if;
11  end;
12  /

Function created. SQL> begin

 2      for i in 1 .. 50 loop
 3          dbms_output.put_line( fact_recursive(50) );
 4      end loop;
 5  end;
 6  /

30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 PL/SQL procedure successfully completed. SQL> SQL> SQL> exec dbms_profiler.stop_profiler PL/SQL procedure successfully completed. SQL></source>

Use DBMS_PROFILER to compare the performance differences between row-at-a-time processing and bulk processing

   <source lang="sql">

SQL> SQL> create table t1 as select * from dba_objects where 0=1; Table created. SQL> create table t2 as select * from dba_objects where 0=1; Table created. SQL> SQL> SQL> create or replace procedure row_at_a_time_test

 2  as
 3  begin
 4      dbms_profiler.start_profiler("row test");
 5      for x in ( select * from all_objects )
 6      loop
 7          insert into t1 values X;
 8      end loop;
 9      dbms_profiler.stop_profiler;
10  end;
11  /

Procedure created. SQL> show errors No errors. SQL> SQL> create or replace procedure bulk_test

 2  as
 3    type array is table of all_objects%rowtype;
 4    l_data array;
 5    cursor c is select * from all_objects;
 6  begin
 7    dbms_profiler.start_profiler("bulk test");
 8    open c;
 9    loop
10       fetch c bulk collect into l_data LIMIT 100;
11
12       forall i in 1 .. l_data.count
13           insert into t2 values l_data(i);
14
15       exit when c%notfound;
16    end loop;
17    dbms_profiler.stop_profiler;
18  end;
19  /

Procedure created. SQL> show errors No errors. SQL> SQL> SQL> SQL> drop table t1; Table dropped. SQL> drop table t2; Table dropped. SQL> SQL></source>