Oracle PL/SQL Tutorial/System Packages/DBMS PROFILER

Материал из SQL эксперт
Перейти к: навигация, поиск

Call dbms_profiler.start_profiler to start profiler

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>


Capture DBMS_PROFILER information for the specified script

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>


DBMS_PROFILER.get_version

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>


dbms_profiler.start_profiler

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>


Demonstrate DBMS_PROFILER

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>


Profiler for recursive function

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>


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

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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/System_Packages/DBMS_PROFILER&oldid=3321»