使用profiler測試Oracle PL/SQL效能

Oracle儲存過程和PL/SQL程式碼常常包含了多個子過程和SQL，對於單個的SQL，我們很容易分析他的執行效率，但是對於PL/SQL儲存過程，我們如何分析它的執行效率、具體每一步的耗時情況呢?Oracle提供了profiler工具可以比較好的解決這個問題。

一、使用前的準備工作

1、檢查DBMS_PROFILER包是否安裝

desc dbms_profiler;

如果沒有安裝，在SYS使用者下執行Oracle主目錄裡的指令碼進行安裝

@?/rdbms/admin/profload.sql

2、第一次使用需要建立用於存放統計資料的表

以哪個使用者執行PL/SQL，就以哪個使用者登陸來建立表。執行以下指令碼，會建立三個表plsql_profiler_data、plsql_profiler_runs、plsql_profiler_units

@?/rdbms/admin/proftab.sql

二、執行測試程式碼

對需要分析的儲存過程和PL/SQL程式碼執行以下包裹測試，填入執行儲存過程需要的引數

declare

err number;

emp_no varchar2(20) := '91001';

line_id number := 100;

station_id number := 1040;

res varchar2(100);

begin

err := dbms_profiler.start_profiler(to_char(sysdate,

'yyyy-mm-dd hh24:mi:ss')); -- 任意的註釋引數

-- 需要執行的儲存過程和PL/SQL程式碼

M090_NS_CHK_EMP(emp_no, line_id, station_id, res);

err := dbms_profiler.stop_profiler;

end;

/

三、查詢結果

找到最近一次執行的run_id

col run_comment for a50

select * from (select runid, to_char(run_date, 'yyyy-mm-dd hh24:mi:ss') run_date, run_comment from plsql_profiler_runs order by runid desc) where rownum = 1;

RUNID RUN_DATE RUN_COMMENT

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

3 2019-09-02 12:54:08 2019-09-02 12:54:08

根據run_id查詢分析結果

select p.unit_name,

p.occured,

p.total_time,

p.line# line,

substr(s.text, 1, 100) text

from (select u.unit_name,

d.total_occur occured,

d.total_time,

d.line#

from plsql_profiler_units u, plsql_profiler_data d

where d.runid = u.runid

and d.unit_number = u.unit_number

and d.total_occur > 0

and u.runid = &run_id) p,

user_source s

where p.unit_name = s.name(+)

and p.line# = s.line(+)

order by p.unit_name, p.line#;

由此可以看到儲存過程及其各個子過程中每一步的消耗時間以及執行次數。

四、生成報告

為了以更好的形式展示分析結果，可以執行以下指令碼生成查詢分析報告

@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\profiler.sql

RUNID RUN_DATE RUN_COMMENT

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

1 02-9月 -19 10:01:31 Test Script - 2019/9/2 10:01:31

2 02-9月 -19 10:15:45 Test Script - 2019/9/2 10:15:44

3 02-9月 -19 12:54:08 2019-09-02 12:54:08

已用時間: 00: 00: 00.03

Usage:

sqlplus apps/<pwd>

SQL> START profiler.sql <runid>

輸入 1 的值: 3

生成的報告類似如下形式

附：相關指令碼

profiler.zip