David's Logger: 使用 DBMS_HPROF 對PLSQL程式進行側寫分析

DBMS_HPROF 是 ORACLE 的側寫分析工具，要順利執行須具備下列條件:

執行 dbmshptab.sql (Console)
以SYS身分登入，建立目錄物件 (TOAD)
以SYS身分執行側寫分析，在目錄物件所指定的目錄產生 TRC 檔案 (TOAD)
使用CONSOLE命令，分析 TRC 檔案，產生 HTML

1. 執行 dbmshptab.sql ，將會產生下列物件

TABLE: dbmshp_runs, dbmshp_function_info, dbmshp_parent_child_info

SEQUENCE: dbmshp_runnumber

上述SQL位置在 /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/

必須在DB主機，使用 SQLPLUS 以 SYSDBA 身分執行

執行完畢後會產生表格如下

2. 以SYS身分登入，建立目錄物件 (TOAD)

   CREATE OR REPLACE DIRECTORY HPROF_DIR AS 
'/home/oracle/logs/';

3. 以SYS身分執行側寫分析，在目錄物件所指定的目錄產生 TRC 檔案 (TOAD)

假設在HR中，欲對某PROCEDURE側寫分析，該PROCEDURE如下(以HR身分)

   
CREATE OR REPLACE PROCEDURE HR.hprof_test 
IS 
BEGIN 
    FOR v_Lp IN 1..4 LOOP 
       simple_procedure;   --呼叫另一PROCEDURE
    END LOOP; 
END hprof_test;

側寫分析的區塊必須以下列指令包圍之

DBMS_HPROF.START_PROFILING('目錄物件', 'TRC檔案'); 及

DBMS_HPROF.STOP_PROFILING;

以下以SYS身分

   
BEGIN 
    DBMS_HPROF.START_PROFILING('HPROF_DIR', 'hprof_test.trc'); 
    hprof_test; 
    DBMS_HPROF.STOP_PROFILING; 
END;

側寫完畢，以SYS身分在TOAD執行DBMS_HPROF.ANALYZE，它會回傳一個NUMBER，可用該回傳值，從dbmshp_runs, dbmshp_function_info, dbmshp_parent_child_info 這三個TABLE中，撈取所要的資料

   
DECLARE 
   v_hprun     NUMBER; 
BEGIN 
   v_hprun := DBMS_HPROF.analyze(
      LOCATION  => 'HPROF_DIR',          -- 目錄物件
      FILENAME  => 'hprof_test.trc');    -- TRC檔案
   DBMS_OUTPUT.PUT_LINE('v_hprun: ' || v_hprun); 
END;

執行完DBMS_HPROF.ANALYZE以後，回傳值及訊息如下:

三個分析表如下

DBMSHP_RUNS

   

CREATE TABLE SYS.DBMSHP_RUNS
(
  RUNID               NUMBER,
  RUN_TIMESTAMP       TIMESTAMP(6),
  TOTAL_ELAPSED_TIME  INTEGER,
  RUN_COMMENT         VARCHAR2(2047 BYTE)
)
TABLESPACE SYSTEM

DBMSHP_FUNCTION_INFO

   
CREATE TABLE SYS.DBMSHP_FUNCTION_INFO
(
  RUNID                  NUMBER,
  SYMBOLID               NUMBER,
  OWNER                  VARCHAR2(32 BYTE),
  MODULE                 VARCHAR2(32 BYTE),
  TYPE                   VARCHAR2(32 BYTE),
  FUNCTION               VARCHAR2(4000 BYTE),
  LINE#                  NUMBER,
  HASH                   RAW(32)                DEFAULT NULL,
  NAMESPACE              VARCHAR2(32 BYTE)      DEFAULT NULL,
  SUBTREE_ELAPSED_TIME   INTEGER                DEFAULT NULL,
  FUNCTION_ELAPSED_TIME  INTEGER                DEFAULT NULL,
  CALLS                  INTEGER                DEFAULT NULL
)
TABLESPACE SYSTEM

DBMSHP_PARENT_CHILD_INFO

   
CREATE TABLE SYS.DBMSHP_PARENT_CHILD_INFO
(
  RUNID                  NUMBER,
  PARENTSYMID            NUMBER,
  CHILDSYMID             NUMBER,
  SUBTREE_ELAPSED_TIME   INTEGER                DEFAULT NULL,
  FUNCTION_ELAPSED_TIME  INTEGER                DEFAULT NULL,
  CALLS                  INTEGER                DEFAULT NULL
)
TABLESPACE SYSTEM

執行完DBMS_HPROF.ANALYZE以後，回傳值的RUNID為1 ，因此透過該 RUNID 來搜尋合乎該 RUNID 的資料

   
SELECT run_timestamp, total_elapsed_time
FROM   dbmshp_runs where runid = 1;

結果如下

   
SELECT owner, type, function, line#,  
   subtree_elapsed_time AS ST_TIME,  
   function_elapsed_time AS FN_TIME, 
   calls FROM   dbmshp_function_info 
   where runid = 1;

結果如下

   
SELECT parentsymid, childsymid,  
   subtree_elapsed_time AS ST_TIME, 
   function_elapsed_time as FN_TIME, 
   calls FROM   dbmshp_parent_child_info 
   where runid = 1;

結果如下