PL/SQL是Oracle提供的過程化資料庫語言，因其與Oracle資料庫、SQL語言之間的無縫連線，一直在資料庫開發領域扮演著很重要的地位。

我們使用PL/SQL開發的程式程式碼，無論是封裝過的儲存過程sp，還是專門的匿名PL/SQL塊，很多時候都需要進行效能評測，來發現潛在的錯誤呼叫或者最佳化空間。在Oracle11g裡，我們可以使用dbms_hprof包來實現對pl/sql程式碼的動態分析。

1、 DBMS_HPROF淺析

DBMS_HPROF是Oracle 11g中提供的效能除錯程式包，提供進行PL/SQL程式除錯效能的介面。該包程式對PL/SQL進行層次資料收集，進行底層呼叫分析和效能資訊生成。

PL/SQL程式往往是建立在層次呼叫的基礎之上。程式碼A呼叫B和C，C又呼叫D，D又呼叫E。在各種巢狀的層次呼叫關係中，我們只管的感覺往往是程式碼A執行速度慢，卻並不知道具體是那個部分消耗。使用dbms_hprof程式包，可以幫助我們進行pl/sql層次呼叫分析。

Dbms_hprof包括兩個元件：資料收集元件（Data Collection Component）和分析元件（Analyzer Component）。

下面我們一起進行dbms_hprof程式包的除錯實驗。

2、 相應環境準備

首先我們準備一個實驗儲存過程，作為實驗物件。

SQL> create or replace procedure P_PROF_TEST is

2 i number;

3 procedure p1 is

4 i number;

5 begin

6 select count(*) into i

7 from all_tables;

8 end;

9

10 procedure p2 is

11 i number;

12 begin

13 select count(*) into i

14 from all_tab_columns;

15 end;

16

17 begin

18 for i in 1..10 loop

19 p2;

20 end loop;

21

22 for i in 1..100 loop

23 p1;

24 end loop;

25 end P_PROF_TEST;

26 /

Procedure created

我們構造了一個儲存過程p_prof_test，儲存過程在sys使用者下。此外進行profile分析還需要一個directory目錄物件。

--建立一個test目錄

[oracle@oracle11g test01]$ pwd

/test01

[oracle@oracle11g test01]$ ls -l

total 0

SQL> sho user

User is "SYS"

SQL> create directory prof_out as '/test01';

Directory created

同時，保證使用者具有執行dbms_hprof包的execute許可權。

3、使用dbms_hprof進行資料收集

下面進行除錯實驗。

--開始除錯過程

SQL> exec dbms_hprof.start_profiling(location => 'PROF_OUT',filename => 't.trc');

PL/SQL procedure successfully completed

Executed in 0 seconds

--執行實驗的儲存過程

SQL> exec p_prof_test;

PL/SQL procedure successfully completed

Executed in 11.468 seconds

SQL> exec dbms_hprof.stop_profiling;

PL/SQL procedure successfully completed

Executed in 0 seconds

Dbms_hprof的儲存過程start_profiling是開始進行除錯度量的過程。該方法的呼叫需要指定一個directory目錄物件，還有生成trace檔案的名稱。Dbms_hprof將分析的結果存放在磁碟的跟蹤檔案裡。

[oracle@oracle11g test01]$ ls -l

total 20

-rw-r--r-- 1 oracle oinstall 19626 Jul 18 14:28 t.trc

跟蹤檔案中的內容，都是執行過程中的一些呼叫關係。如下為片段：

P#C PLSQL."SYS"."DBMS_HPROF"::11."STOP_PROFILING"#980980e97e42f8ec #59

P#R

P#! PL/SQL Timer Stopped

一系列的呼叫關係，形成跟蹤檔案t.trc。我們透過直接閱讀的手段是可以讀取到的。不過dbms_hprof提供了一系列的工具，可以幫助我們分析執行結果。

4、分析跟蹤資料結果

Dbms_hprof的跟蹤結果有兩種方式，資料表儲存和html格式報告。我們下面分別進行介紹。

ü 資料表儲存

對分析的結果，我們可以使用資料表來進行儲存。首先需要在分析的資料Schema下構建系列資料表。

SQL> sho user

USER is "SYS"

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

使用指令碼可以幫助建立一系列的分析結果容納資料表。

SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'DBMSHP_%';

TABLE_NAME

------------------------------

DBMSHP_FUNCTION_INFO

DBMSHP_PARENT_CHILD_INFO

DBMSHP_RUNS

Executed in 0.063 seconds

我們需要呼叫dbms_hprof分析元件方法。

SQL> var mid number;

SQL> begin

2 :mid := dbms_hprof.analyze(location => 'PROF_OUT',filename => 't.trc',run_comment=>'Run Dev');

3 end;

4 /

PL/SQL procedure successfully completed

Executed in 0.078 seconds

mid

---------

1

返回的mid表示分析結果的唯一標記。透過這個標記可以從hprof系列結果表中獲取到結果資料。

其中，檢視dbmshp_runs表示進行分析的次數資訊，每次進行分析，都會對應一條記錄。其中最重要的欄位就是runid和total_elapsed_time，分別表示該次分析的序列號和分析執行時間。

SQL> select runid, TOTAL_ELAPSED_TIME, RUN_COMMENT from dbmshp_runs;

RUNID TOTAL_ELAPSED_TIME RUN_COMMENT

---------- ------------------------------ --------------------

1 7568868 Run Dev

Executed in 0.016 seconds

其次第二個檢視就是DBMSHP_FUNCTION_INFO，記錄著pl/sql程式碼相關的函式和呼叫物件資訊。

SQL> select runid, symbolid, module, type, function, FUNCTION_ELAPSED_TIME from dbmshp_function_info;

RUNID SYMBOLID MODULE TYPE FUNCTION FUNCTION_E

----- ---------- -------------------- --------------- ------------------------------ ----------

1 1 __anonymous_block 142

1 2 __plsql_vm 35

1 3 DBMS_HPROF PACKAGE BODY STOP_PROFILING 0

1 4 DBMS_TRANSACTION PACKAGE BODY LOCAL_TRANSACTION_ID 26

1 5 P_PROF_TEST PROCEDURE P_PROF_TEST 453

1 6 P_PROF_TEST PROCEDURE P_PROF_TEST.P1 4400

1 7 P_PROF_TEST PROCEDURE P_PROF_TEST.P2 192

1 8 P_PROF_TEST PROCEDURE __static_sql_exec_line13 1871195

1 9 P_PROF_TEST PROCEDURE __static_sql_exec_line6 5692425

9 rows selected

Executed in 0.032 seconds

其中包括的內容，是該段pl/sql程式碼相關所有相關函式內容。各列中的symbolid表示的就是相關函式在評測結果中的表示資訊。

最後就是dbmshp_parent_child_info，表示前後呼叫函式關係和每個函式的消耗時間。

SQL> select runid, parentsymid, childsymid, SUBTREE_ELAPSED_TIME from dbmshp_parent_child_info;

RUNID PARENTSYMID CHILDSYMID SUBTREE_ELAPSED_TIME

----- ----------- ---------- ---------------------------------------

1 1 5 7568665

1 1 4 26

1 1 3 0

1 2 1 7568833

1 5 6 5696825

1 5 7 1871387

1 6 9 5692425

1 7 8 1871195

8 rows selected

Executed in 0.016 seconds

除了將分析結果儲存在資料表外，還可以選擇使用html報表方式顯示。

ü Html報表格式

分析t.trc檔案，獲取最後的分析報告。我們可以選擇可讀性更好的html格式。

[oracle@oracle11g test01]$ pwd

/test01

[oracle@oracle11g test01]$ ls

t.trc

[oracle@oracle11g test01]$

分析使用的工具軟體是plshprof。

[oracle@oracle11g test01]$ plshprof -output m t.trc

PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

[9 symbols processed]

[Report written to 'm.html']

其中，m表示生成的報告檔案主檔案是m.html。t.trc為分析的資料來源跟蹤檔名稱。

此時觀察生成目錄，可以知道對應的檔案是多個。

[oracle@oracle11g test01]$ ls

m_2c.html m_fn.html m_mf.html m_nsf.html m_tc.html m_ts.html

m_2f.html m.html m_ms.html m_nsp.html m_td.html t.trc

m_2n.html m_md.html m_nsc.html m_pc.html m_tf.html

其中只有m.html才是報告的主檔案。下面考慮將檔案獲取到可以開啟的系統下。

[oracle@oracle11g test01]$ zip -r load.zip *.*

adding: m_2c.html (deflated 69%)

adding: m_2f.html (deflated 71%)

adding: m_2n.html (deflated 68%)

adding: m_fn.html (deflated 80%)

adding: m.html (deflated 77%)

adding: m_md.html (deflated 80%)

adding: m_mf.html (deflated 80%)

adding: m_ms.html (deflated 80%)

adding: m_nsc.html (deflated 63%)

adding: m_nsf.html (deflated 64%)

adding: m_nsp.html (deflated 60%)

adding: m_pc.html (deflated 88%)

adding: m_tc.html (deflated 80%)

adding: m_td.html (deflated 80%)

adding: m_tf.html (deflated 80%)

adding: m_ts.html (deflated 79%)

adding: t.trc (deflated 94%)

生成zip壓縮檔案。

[oracle@oracle11g test01]$ ls

load.zip m_2n.html m_md.html m_nsc.html m_pc.html m_tf.html

m_2c.html m_fn.html m_mf.html m_nsf.html m_tc.html m_ts.html

m_2f.html m.html m_ms.html m_nsp.html m_td.html t.trc

5、結論

本篇中，我們詳細介紹了dbms_hprof程式包的使用和安裝。以及透過一個小例項來驗證我們對pl/sql程式的呼叫。

對pl/sql而言，Oracle11g推出的這個新特性可以幫助我們解決PL/SQL程式碼調優問題，解決一些層次效能評定問題。

在下篇中，我們打算著重介紹一些生成評測結果的觀察方法。

使用DBMS_HPROF評測PL/SQL程式碼效能（上）

相關文章