使用DBMS_PROFILER進行PL/SQL效能分析

可以使用DBMS_PROFILER進行PL/SQL效能分析，或者進行覆蓋性分析。測試如下：

建立儲存資料所需表：
?/rdbms/admin/PROFTAB.SQL,在收集資訊的使用者下執行,建立所需表和儲存過程.
或者?/rdbms/admin/PROFLOAD.SQL,在系統使用者下執行,建立所需表和儲存過程，並將SELECT/INSERT/UPDATE許可權授予所有使用者，並建立同義詞。

許可權:
需要有create許可權才能收集物件資訊的,只有execute許可權是不能收集物件資訊的;
被分析物件是否需要debug方式編譯?不需要,但建議;這樣可以獲取額外資訊.

--測試資料庫版本：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1,分析簡單儲存過程

1.1建立測試表
drop table p;
create table p
as
select object_id as id from dba_objects where object_id is not null;

alter table p
  add constraint pk_p primary key(id);

drop table c;
create table c
as
select object_id as id from dba_objects where object_id is not null and rownum<500;

alter table c
  add constraint fk_c_r_p foreign key(id) references p(id) on delete cascade;

create or replace procedure prc_del
is
begin
  for x in 1..1000 loop
    delete from p where id=x;
  end loop;
  commit;
end;
/

1.2 開始分析
exec dbms_profiler.start_profiler;
SQL> exec dbms_profiler.start_profiler;
BEGIN dbms_profiler.start_profiler; END;

*
ERROR at line 1:
ORA-06528: Error executing PL/SQL profiler
ORA-06512: at "SYS.DBMS_PROFILER", line 123
ORA-06512: at "SYS.DBMS_PROFILER", line 132
ORA-06512: at line 1

==〉奇怪的錯誤程式碼

SQL> @?/rdbms/admin/proftab.sql

exec dbms_profiler.start_profiler;

exec prc_del;

--exec dbms_profiler.flush_data;

exec dbms_profiler.stop_profiler;

1.3分析結果查詢
SQL> select runid,run_total_time from mh.plsql_profiler_runs;

     RUNID RUN_TOTAL_TIME
---------- --------------
         1     1.7716E+11

1 row selected.

SQL> set linesize 200
SQL> r
  1  select u.runid, unit_number, unit_type, unit_name
  2    from mh.plsql_profiler_runs r, mh.plsql_profiler_units u
  3*  where r.runid = u.runid

     RUNID UNIT_NUMBER UNIT_TYPE                        UNIT_NAME
---------- ----------- -------------------------------- --------------------------------
         1           1 ANONYMOUS BLOCK                 
         1           2 ANONYMOUS BLOCK                 
         1           3 PROCEDURE                        PRC_DEL
         1           4 ANONYMOUS BLOCK                 
         1           5 ANONYMOUS BLOCK                 

5 rows selected.

col line# for 999
col total_occur for 9999999
col total_time for 99999999999
col min_time for 99999999999
col max_time for 99999999999
col text for a40
select d.line# , d.total_occur , d.total_time ,
       d.min_time, d.max_time , s.text
  from mh.plsql_profiler_units u, mh.plsql_profiler_data d, all_source s
 where u.runid = u.runid
   and u.unit_number = d.unit_number
   and s.owner = u.unit_owner
   and s.name = u.unit_name
   and s.TYPE = u.unit_type
   and s.line = d.line#;
LINE# TOTAL_OCCUR   TOTAL_TIME     MIN_TIME     MAX_TIME TEXT
----- ----------- ------------ ------------ ------------ ----------------------------------------
    1           0         9000         4000         5000 procedure prc_del
    4        1003      1001000         1000         4000   for x in 1..1000 loop
    5        1002    296954000        60000     18913000     delete from p where id=x;
    7           1       221000       221000       221000   commit;
    8           1         5000         5000         5000 end;

5 rows selected.

1.4重新建立測試表，獲取測試資料如下：
select d.line# , d.total_occur , d.total_time ,
       d.min_time, d.max_time , s.text
  from mh.plsql_profiler_units u, mh.plsql_profiler_data d, all_source s
 where u.runid = u.runid
   and u.unit_number = d.unit_number
   and s.owner = u.unit_owner
   and s.name = u.unit_name
   and s.TYPE = u.unit_type
   and s.line = d.line#
   and u.runid=2
   and u.runid=d.runid;
LINE# TOTAL_OCCUR   TOTAL_TIME     MIN_TIME     MAX_TIME TEXT
----- ----------- ------------ ------------ ------------ ----------------------------------------
    1           0        19000        19000        19000 procedure prc_del
    4        1001      1082000         1000        70000   for x in 1..1000 loop
    5        1000    247706000        59000     10843000     delete from p where id=x;
    7           1       207000       207000       207000   commit;
    8           1         6000         6000         6000 end;

5 rows selected.

1.5 重新建立測試表，子表建立索引，獲取測試資料如下：
create index idx_c_id;

select d.line# , d.total_occur , d.total_time ,
       d.min_time, d.max_time , s.text
  from mh.plsql_profiler_units u, mh.plsql_profiler_data d, all_source s
 where u.runid = u.runid
   and u.unit_number = d.unit_number
   and s.owner = u.unit_owner
   and s.name = u.unit_name
   and s.TYPE = u.unit_type
   and s.line = d.line#
   and u.runid=3
   and u.runid=d.runid;
LINE# TOTAL_OCCUR   TOTAL_TIME     MIN_TIME     MAX_TIME TEXT
----- ----------- ------------ ------------ ------------ ----------------------------------------
    1           0         5000         5000         5000 procedure prc_del
    4        1001       989000         1000        16000   for x in 1..1000 loop
    5        1000    526245000        58000    283551000     delete from p where id=x;
    7           1       207000       207000       207000   commit;
    8           1         7000         7000         7000 end;

5 rows selected.

==>外來鍵建立了索引，雖然加快了查詢速度，但由於級聯刪除，導致所耗時間增加。

2,分析有呼叫關係的儲存過程
2.1 建立測試用儲存過程
create or replace function prc_a(x int,y int) return int
is
begin
   return x+y;
end;
/

create or replace function prc_b(x int,y int) return int
is
begin
   return prc_a(x,y);
end;
/

2.2 收集資訊
exec dbms_profiler.start_profiler;

set serveroutput on
declare
  s int;
begin
  for x in 1..100 loop
     s := prc_b(x,s);
  end loop;
  dbms_output.put_line('==>'||s);
end;
/

exec dbms_profiler.stop_profiler;

2.3 查詢結果
col unit_name for a15
col line# for 999
col total_occur for 9999999
col total_time for 99999999999
col min_time for 99999999999
col max_time for 99999999999
col text for a40
select u.unit_name,d.line# , d.total_occur , d.total_time ,
       d.min_time, d.max_time , s.text
  from mh.plsql_profiler_units u, mh.plsql_profiler_data d, all_source s
 where u.runid = u.runid
   and u.unit_number = d.unit_number
   and s.owner = u.unit_owner
   and s.name = u.unit_name
   and s.TYPE = u.unit_type
   and s.line = d.line#
   and u.runid=5
   and u.runid=d.runid;
UNIT_NAME       LINE# TOTAL_OCCUR   TOTAL_TIME     MIN_TIME     MAX_TIME TEXT
--------------- ----- ----------- ------------ ------------ ------------ ----------------------------------------
PRC_B               1           0       107000         1000         3000 function prc_b(x int,y int) return int
PRC_B               4         100       196000         1000        17000    return prc_a(x,y);
PRC_B               5         100        85000         1000         1000 end;
PRC_A               1           0       106000         1000         3000 function prc_a(x int,y int) return int
PRC_A               4         100        88000         1000         1000    return x+y;
PRC_A               5         100        92000         1000         2000 end;