oracle使用profiler分析語句執行效率

1、前言
     Profiler是ORACLE PL/SQL 的一個除錯最佳化跟蹤方案，使用profiler可以跟語句執行過程中消耗的時間等。在日常開發過程中，我們可能會常常遇到一些sql語句、儲存過程在使用過程中，消耗過多的資源，為了細化了某條具體的語句，我們可以使用profiler。這樣大大的減少了我們尋找問題語句的時間，提高效率，在最佳化的過程中，profiler起到一個很好的效果

2、profiler的一些包的介紹

--proftab.sql介紹
在當前使用者建表及序列等，具體如下；
plsql_profiler_runs  - profiler執行資訊                   
plsql_profiler_units - profiler每個單元資訊               
plsql_profiler_data  - profiler每個單元的詳細資料         
plsql_profiler_runnumber 用來生成profiler唯一執行編號的序列

--profload.sql介紹
建立資料庫服務包，包括啟動、停止等，具體如下：
sys.dbms_profiler包
此包包括以下主要的函式過程   
start_profiler           --啟動函式
stop_profiler            --停止函式
pause_profiler
resume_profiler
flush_data
internal_version_check
get_version
rollup_unit
rollup_run

--具體建立過程
SQL> @$ORACLE_HOME/rdbms/admin/proftab.sql;
drop table plsql_profiler_data cascade constraints
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist

drop sequence plsql_profiler_runnumber
              *
ERROR at line 1:
ORA-02289: sequence does not exist

 

Table created.

Comment created.

Table created.

Comment created.

Table created.

Comment created.

Sequence created.

在建立的過程中，會檢測這些物件是否存在，先進行刪除，後面補建成功

SQL> @$ORACLE_HOME/rdbms/admin/profload.sql;

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

3、建立我們需要的測試物件

在本儲存過程中，我們只進行兩條語句的分析處理，具體如下：

SQL> CREATE OR REPLACE PROCEDURE mytest_proc
  2  is
  3    v_counts1 number;
  4    v_counts2 number;
  5    v_counts3 number;
  6 
  7  begin
  8    select count(*) into v_counts1 from dba_tables;
  9    select count(*) into v_counts2 from dba_objects;
 10    select count(*) into v_counts3 from dba_segments;
 11  end;
 12  /

4、開始測試profiler的使用
--針對上面建立的儲存過程，對裡面的語句進行分析
SQL> begin
  2  DBMS_PROFILER.START_PROFILER('my profiler test');
  3  mytest_proc ;
  4  DBMS_PROFILER.STOP_PROFILER;
  5  end;
  6  /

PL/SQL procedure successfully completed.

--查詢剛才分析出來的結果：
SQL> select d.line#,
  2         u.unit_name,
  3         d.total_time,
  4         d.total_occur,
  5         d.max_time,
  6         s.text
  7    from sys.plsql_profiler_data d, sys.all_source s, sys.plsql_profiler_units u
  8   where u.runid = d.runid
  9     and d.unit_number = u.unit_number
 10     and d.total_occur <> 0
 11     and s.type(+) = u.unit_type
 12     and s.owner(+) = u.unit_owner
 13     and s.name(+) = u.unit_name
 14     and d.line# = nvl(s.line, d.line#) and u.unit_name = 'MYTEST_PROC'
 15   order by u.unit_number, d.line#
 16  ;
 
LINE# UNIT_NAME       TOTAL_TIME TOTAL_OCCUR   MAX_TIME TEXT
----- --------------- ---------- ----------- ---------- ----------------------------------------------------
    1 MYTEST_PROC           2006           1       2006 PROCEDURE mytest_proc
    8 MYTEST_PROC      412905914           1  412905914   select count(*) into v_counts1 from dba_tables;
    9 MYTEST_PROC      102775576           1  102775576   select count(*) into v_counts2 from dba_objects;
   10 MYTEST_PROC      222756652           1  222756652   select count(*) into v_counts3 from dba_segments;
   12 MYTEST_PROC           5015           1       5015 end;
  
欄位說明：
unit_time     --表示執行的過程、語句、函式等
total_time    --總共執行的時間，單位非常小，萬億分之一秒
total_occur   --執行的次數，用總時間除次數，可得到每次執行的時間
max_time      --程式碼執行的最最長時間，如有遊標迴圈等
runid         --執行號，這個是自動產生的
Avg_time      --平均執行時間
min_time      --最小執行時間