使用dbms_profiler來觀察每一行PLSQL程式的執行時間
使用dbms_profiler來觀察每一行PLSQL程式的執行時間
有時候我們在執行一段比較長的plsql程式的時候，發現執行過程比較慢，但又不知道是哪部分出現了效能問題，這時候應該如何快速找到最耗費時間的部分呢？當然有很多方法，但這裡推薦一種方法，可以使用dbms_profiler包來觀察每一行程式碼的執行次數和時間。
一、操作步驟
1. 登入到SYS使用者，檢查dbms_profiler包有沒有安裝。沒有的話執行$ORACLE_HOME/rdbms/admin/profload.sql來建立該包。
2. 將該包的執行許可權賦予待除錯的使用者。
3. 登入到除錯使用者，執行$ORACLE_HOME/rdbms/admin/proftab.sql建立相關表。
4. 在待優化的PL/SQL程式前後加上執行dbms_profiler包的程式碼，如下：
BEGIN
DBMS_PROFILER.START_PROFILER('any comment');
...'your pl/sql code'
DBMS_PROFILER.STOP_PROFILER;
...
END;
5. 執行該PL/SQL程式，然後檢查PLSQL_PROFILER_DATA表內容，可以從中看到每一行程式碼的執行次數，每行程式碼總共的執行時間。
二、具體的例子：
SQL> conn / as sysdba;
Connected.
1.檢查是否安裝了dbms_profiler包
SQL> desc dbms_profiler;
ERROR:
ORA-04043: object dbms_profiler does not exist
--如果沒有安裝的話，執行下面的語句安裝
SQL> @/disk_dev/oracle/product/9.2.0/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.
2.把dbms_profiler的執行許可權賦於oauser
SQL> grant execute on dbms_profiler to oauser;
Grant succeeded.
3.使用oauser登入，執行proftab.sql，此檔案實際是建立plsql_profiler_data、plsql_profiler_units、plsql_profiler_runs三個表，具體proftab.sql的內容如下：
proftab.sql:
drop table plsql_profiler_data cascade constraints;
drop table plsql_profiler_units cascade constraints;
drop table plsql_profiler_runs cascade constraints;
drop sequence plsql_profiler_runnumber;
create table plsql_profiler_runs
(
runid number primary key, -- unique run identifier,
-- from plsql_profiler_runnumber
related_run number, -- runid of related run (for client/
-- server correlation)
run_owner varchar2(32), -- user who started run
run_date date, -- start time of run
run_comment varchar2(2047), -- user provided comment for this run
run_total_time number, -- elapsed time for this run
run_system_info varchar2(2047), -- currently unused
run_comment1 varchar2(2047), -- additional comment
spare1 varchar2(256) -- unused
);
comment on table plsql_profiler_runs is
'Run-specific information for the PL/SQL profiler';
create table plsql_profiler_units
(
runid number references plsql_profiler_runs,
unit_number number, -- internally generated library unit #
unit_type varchar2(32), -- library unit type
unit_owner varchar2(32), -- library unit owner name
unit_name varchar2(32), -- library unit name
-- timestamp on library unit, can be used to detect changes to
-- unit between runs
unit_timestamp date,
total_time number DEFAULT 0 NOT NULL,
spare1 number, -- unused
spare2 number, -- unused
--
primary key (runid, unit_number)
);
comment on table plsql_profiler_units is
'Information about each library unit in a run';
create table plsql_profiler_data
(
runid number, -- unique (generated) run identifier
unit_number number, -- internally generated library unit #
line# number not null, -- line number in unit
total_occur number, -- number of times line was executed
total_time number, -- total time spent executing line
min_time number, -- minimum execution time for this line
max_time number, -- maximum execution time for this line
spare1 number, -- unused
spare2 number, -- unused
spare3 number, -- unused
spare4 number, -- unused
--
primary key (runid, unit_number, line#),
foreign key (runid, unit_number) references plsql_profiler_units
);
comment on table plsql_profiler_data is
'Accumulated data from all profiler runs';
create sequence plsql_profiler_runnumber start with 1 nocache;
SQL> conn oauser/oauser;
Connected.
SQL> @/disk_dev/oracle/product/9.2.0/rdbms/admin/proftab.sql;
4.建立一個測試的過程，在程式碼中加入dbms_profiler程式碼，如下：
create or replace procedure p_dbms_profiler_test
is
i number :=1 ;
v_str varchar2(4000);
begin
dbms_profiler.start_profiler('測試一下');
dbms_output.put_line(i);
for i in 1..100 loop
exit when length(v_str)>100;
v_str := v_str||i;
end loop;
dbms_output.put_line(v_str);
dbms_profiler.stop_profiler;
end p_dbms_profiler_test;
SQL> exec p_dbms_profiler_test;
PL/SQL procedure successfully completed
SQL> exec p_dbms_profiler_test;
PL/SQL procedure successfully completed
SQL> exec p_dbms_profiler_test;
PL/SQL procedure successfully completed
執行一次p_dbms_profiler_test過程，就會分別在plsql_profiler_units和plsql_profiler_runs插入一條資訊，在plsql_profiler_data插入具體的過程執行資料。
5.檢視結果
通過下面的SQL語句我們可以知道每一行程式碼的執行次數，時間等等。
SQL> select t1.line#,t3.text,t1.total_occur,t1.total_time,min_time, max_time
2 from plsql_profiler_data t1,plsql_profiler_units t2,user_source t3
3 where t1.runid = t2.runid
4 and t2.unit_name='P_DBMS_PROFILER_TEST'
5 and t2.unit_name = t3.name
6 and t1.line#=t3.line
7 and t1.runid=3
8 order by t1.line#
9 /
LINE# TEXT TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
---------- -------------------------------------------------------------------------------- --
3 i number :=1 ; 0 0 0 0
6 dbms_profiler.start_profiler('測試一下'); 0 0 0 0
7 dbms_output.put_line(i); 1 17000 17000 17000
8 for i in 1..100 loop 56 222000 2000 6000
9 exit when length(v_str)>100; 56 232000 3000 7000
10 v_str := v_str||i; 55 254000 3000 16000
12 dbms_output.put_line(v_str); 1 5000 5000 5000
13 dbms_profiler.stop_profiler; 1 5000 5000 5000
14 end p_dbms_profiler_test; 0 0 0 0
9 rows selected
有時候我們在執行一段比較長的plsql程式的時候，發現執行過程比較慢，但又不知道是哪部分出現了效能問題，這時候應該如何快速找到最耗費時間的部分呢？當然有很多方法，但這裡推薦一種方法，可以使用dbms_profiler包來觀察每一行程式碼的執行次數和時間。
一、操作步驟
1. 登入到SYS使用者，檢查dbms_profiler包有沒有安裝。沒有的話執行$ORACLE_HOME/rdbms/admin/profload.sql來建立該包。
2. 將該包的執行許可權賦予待除錯的使用者。
3. 登入到除錯使用者，執行$ORACLE_HOME/rdbms/admin/proftab.sql建立相關表。
4. 在待優化的PL/SQL程式前後加上執行dbms_profiler包的程式碼，如下：
BEGIN
DBMS_PROFILER.START_PROFILER('any comment');
...'your pl/sql code'
DBMS_PROFILER.STOP_PROFILER;
...
END;
5. 執行該PL/SQL程式，然後檢查PLSQL_PROFILER_DATA表內容，可以從中看到每一行程式碼的執行次數，每行程式碼總共的執行時間。
二、具體的例子：
SQL> conn / as sysdba;
Connected.
1.檢查是否安裝了dbms_profiler包
SQL> desc dbms_profiler;
ERROR:
ORA-04043: object dbms_profiler does not exist
--如果沒有安裝的話，執行下面的語句安裝
SQL> @/disk_dev/oracle/product/9.2.0/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.
2.把dbms_profiler的執行許可權賦於oauser
SQL> grant execute on dbms_profiler to oauser;
Grant succeeded.
3.使用oauser登入，執行proftab.sql，此檔案實際是建立plsql_profiler_data、plsql_profiler_units、plsql_profiler_runs三個表，具體proftab.sql的內容如下：
proftab.sql:
drop table plsql_profiler_data cascade constraints;
drop table plsql_profiler_units cascade constraints;
drop table plsql_profiler_runs cascade constraints;
drop sequence plsql_profiler_runnumber;
create table plsql_profiler_runs
(
runid number primary key, -- unique run identifier,
-- from plsql_profiler_runnumber
related_run number, -- runid of related run (for client/
-- server correlation)
run_owner varchar2(32), -- user who started run
run_date date, -- start time of run
run_comment varchar2(2047), -- user provided comment for this run
run_total_time number, -- elapsed time for this run
run_system_info varchar2(2047), -- currently unused
run_comment1 varchar2(2047), -- additional comment
spare1 varchar2(256) -- unused
);
comment on table plsql_profiler_runs is
'Run-specific information for the PL/SQL profiler';
create table plsql_profiler_units
(
runid number references plsql_profiler_runs,
unit_number number, -- internally generated library unit #
unit_type varchar2(32), -- library unit type
unit_owner varchar2(32), -- library unit owner name
unit_name varchar2(32), -- library unit name
-- timestamp on library unit, can be used to detect changes to
-- unit between runs
unit_timestamp date,
total_time number DEFAULT 0 NOT NULL,
spare1 number, -- unused
spare2 number, -- unused
--
primary key (runid, unit_number)
);
comment on table plsql_profiler_units is
'Information about each library unit in a run';
create table plsql_profiler_data
(
runid number, -- unique (generated) run identifier
unit_number number, -- internally generated library unit #
line# number not null, -- line number in unit
total_occur number, -- number of times line was executed
total_time number, -- total time spent executing line
min_time number, -- minimum execution time for this line
max_time number, -- maximum execution time for this line
spare1 number, -- unused
spare2 number, -- unused
spare3 number, -- unused
spare4 number, -- unused
--
primary key (runid, unit_number, line#),
foreign key (runid, unit_number) references plsql_profiler_units
);
comment on table plsql_profiler_data is
'Accumulated data from all profiler runs';
create sequence plsql_profiler_runnumber start with 1 nocache;
SQL> conn oauser/oauser;
Connected.
SQL> @/disk_dev/oracle/product/9.2.0/rdbms/admin/proftab.sql;
4.建立一個測試的過程，在程式碼中加入dbms_profiler程式碼，如下：
create or replace procedure p_dbms_profiler_test
is
i number :=1 ;
v_str varchar2(4000);
begin
dbms_profiler.start_profiler('測試一下');
dbms_output.put_line(i);
for i in 1..100 loop
exit when length(v_str)>100;
v_str := v_str||i;
end loop;
dbms_output.put_line(v_str);
dbms_profiler.stop_profiler;
end p_dbms_profiler_test;
SQL> exec p_dbms_profiler_test;
PL/SQL procedure successfully completed
SQL> exec p_dbms_profiler_test;
PL/SQL procedure successfully completed
SQL> exec p_dbms_profiler_test;
PL/SQL procedure successfully completed
執行一次p_dbms_profiler_test過程，就會分別在plsql_profiler_units和plsql_profiler_runs插入一條資訊，在plsql_profiler_data插入具體的過程執行資料。
5.檢視結果
通過下面的SQL語句我們可以知道每一行程式碼的執行次數，時間等等。
SQL> select t1.line#,t3.text,t1.total_occur,t1.total_time,min_time, max_time
2 from plsql_profiler_data t1,plsql_profiler_units t2,user_source t3
3 where t1.runid = t2.runid
4 and t2.unit_name='P_DBMS_PROFILER_TEST'
5 and t2.unit_name = t3.name
6 and t1.line#=t3.line
7 and t1.runid=3
8 order by t1.line#
9 /
LINE# TEXT TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
---------- -------------------------------------------------------------------------------- --
3 i number :=1 ; 0 0 0 0
6 dbms_profiler.start_profiler('測試一下'); 0 0 0 0
7 dbms_output.put_line(i); 1 17000 17000 17000
8 for i in 1..100 loop 56 222000 2000 6000
9 exit when length(v_str)>100; 56 232000 3000 7000
10 v_str := v_str||i; 55 254000 3000 16000
12 dbms_output.put_line(v_str); 1 5000 5000 5000
13 dbms_profiler.stop_profiler; 1 5000 5000 5000
14 end p_dbms_profiler_test; 0 0 0 0
9 rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1309740/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DBMS_PROFILER 檢視儲存過程執行時間儲存過程
- Linux 中如何設定每個特定的時間執行特定的程式Linux
- 程式執行時間的測量
- 計算php程式執行時間的程式PHP
- 每一行程式碼都有記錄行程
- 測量程式的執行時間（二）
- 測量程式的執行時間（一）
- Linux 檢視程式啟動時間、執行時間Linux
- KETTLE一行一行執行方法
- Stopwatch 計算程式執行時間
- 如何測量程式碼執行時間
- JavaScript 檢測程式碼執行時間JavaScript
- .NET程式碼樹執行時間計時器
- R語言記錄程式執行的時間R語言
- PAT-B 1026 程式執行時間【時間】
- abap執行時間
- 使用 ES6 來為非同步函式記錄執行時間非同步函式
- python程式計算執行時間差Python
- VC程式執行時間測試函式C程式函式
- 程式執行時的記憶體空間分佈記憶體
- 透過pl/sql計算程式的執行時間SQL
- 通過pl/sql計算程式的執行時間SQL
- lr計算程式執行消耗時間的比較：
- javascript測試程式碼的執行時間程式碼例項JavaScript
- 後臺執行以及保持程式在後臺長時間執行
- 把 React 作為 UI 執行時來使用ReactUI
- css在每一行的結尾新增逗號程式碼例項CSS
- 執行crontab最好的時間(轉)
- job 執行時間為每小時3次，分別在10分，30分，50分
- JavaScript 計算程式碼執行花費時間JavaScript
- 1026. 程式執行時間(15)
- Java專案計算程式執行時間方法Java
- PLSQL來Oracle建立表空間和建立使用者SQLOracle
- 使用DBMS_PROFILER進行PL/SQL效能分析SQL
- 程式碼效能分析-Dottrace跟蹤程式碼執行時間
- 在每一行後面新增commit;MIT
- job 執行時間比排程間隔時間長
- 使用V$檢視觀察Oracle執行狀況Oracle