Oracle’s DBMS_Profiler:PL/SQL 性能調整
添加時間:2012-11-8 17:46:50
添加:
思海網(wǎng)絡
DBMS_PROFILER 包舉例
下面是我提供的怎樣使用配置的簡單例子,運行配置文件來測試下面例程的性能. 例程用到的自定義腳本緊隨其后.
1. 創(chuàng)建過程.
create or replace procedure am_perf_chk (pi_seq in number,
pio_status in out nocopy varchar2) is
l_dat date := sysdate;
begin
if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
pio_status := 'OK';
else
pio_status := 'Invalid tape loaded';
end if;
exception
when others then
pio_status := 'Error in am_perf_chek';
end;
2. 用配置文件調用例程
替換上面的例程, 執(zhí)行call_profiler.sql腳本(腳本代碼參見下面),傳入pi_seq=2
SQL> @d:\am\call_profiler.sql
Profiler started
Invalid tape loaded
PL/SQL procedure successfully completed.
Profiler stopped
Profiler flushed
runid:8
3. 評估執(zhí)行時間:
執(zhí)行eavluate_profiler_results.sql腳本,得到時間統(tǒng)計
SQL> @d:\am\evaluate_profiler_results.sql
Enter value for runid: 8
Enter value for name: am_perf_chk
Enter value for owner: scott
Line Occur Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
1 procedure am_perf_chk (pi_seq in number,
2 pio_status in out nocopy varchar2) is
3 2 43.05965 l_dat date := sysdate;
4 begin
5 1 86.35732 if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
6 0 0 pio_status := 'OK';
7 else
8 1 8.416151 pio_status := 'Invalid tape loaded';
9 end if;
10 exception
11 when others then
12 0 0 pio_status := 'Error in am_perf_chek';!
13 1 2.410361 end;
13 rows selected.
Code% coverage
--------------
66.6666667
4. 正如你看到的,第三行執(zhí)行時間提高到86毫秒.但是改變if語句,重新執(zhí)行上面的過程,將會得到新的結果:
Line Occur Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
1 procedure am_perf_chk (pi_seq in number,
2 pio_status in out nocopy varchar2) is
3 2 17.978816 l_dat date := sysdate;
4 begin
5 1 8.419503 if pi_seq = 1 and trunc(l_dat) = '21-sep-02' then
6 0 0 pio_status := 'OK';
7 else
8 1 7.512684 pio_status := 'Invalid tape loaded';
9 end if;
10 exception
11 when others then
12 0 0 pio_status := 'Error in !am_perf_chek';
13 1 .731657 end;
13 rows selected.
Code% coverage
--------------
66.6666667
5. 正如你看到的, 這種情境下第三行執(zhí)行時間從86毫秒減少到8毫秒,多余的時間是由于內置trunc()函數(shù)引起., 這種情境下如果第一個條件為false,則不會執(zhí)行trunc()函數(shù).這僅僅是個簡單的例子,當你測試的例程越大,你面臨的挑戰(zhàn)更大.
這個配置結果也證明了執(zhí)行期間代碼被覆蓋多少行,從而讓我們知道處于性能監(jiān)視中的代碼范圍。如果任何PL/SQL塊性能出現(xiàn)問題,它也能提煉出各種不同情景的正在在執(zhí)行的代碼并檢查配置結果,從而查明問題所在。
6. 對于一個特定的情景,如果執(zhí)行一段特殊的代碼段,可以得到合理的分析,即使代碼根本一點都不能運行。
環(huán)境的創(chuàng)建
默認安裝或數(shù)據(jù)庫的創(chuàng)建狀態(tài)下,DBMS_PROFILER包不會自動安裝,請DBA用profload.sql腳本創(chuàng)建它.用一個權限較大的或一個單獨的用戶,創(chuàng)建存儲統(tǒng)計信息的表。如果
用如SYS用戶創(chuàng)建,則給其它用戶授予DML權限,并且對這些表創(chuàng)建一個共同的簡寫名.
創(chuàng)建表的如下:
PLSQL_PROFILER_RUNS表:PL/SQL配置的運行細節(jié).
PLSQL_PROFILER_UNITS表:運行中每一個庫單元的信息.
PLSQL_PROFILER_DATA表:所有配置文件運行時的數(shù)據(jù)累積.
PLSQL_PROFILER_RUNNUMBER序列提供了RUNID
運行和解釋配置數(shù)據(jù)
ORACLE提供了三個表來統(tǒng)計,填充RUNID。有許多第三方的工具可以提供自定義的基于這些數(shù)據(jù)的報告,ORACLE提供profrep.sql腳本評估數(shù)據(jù)(在<oracle_home>\plsql\demo\目錄下),下面的兩個簡單腳本就是上面用到的,用來檢查程序單元的執(zhí)行時間.執(zhí)行時間以毫秒存儲
-----------------------------------------------------------
: call_profiler.sql
-----------------------------------------------------------
set head off
set pages 0
select decode(dbms_profiler.start_profiler, '0', 'Profiler started', 'Profiler error')
from dual;
--< place your routine in the below block >--
declare
l_status varchar2(200);
begin
am_perf_chk(2, l_status);
dbms_output.put_line(l_status);
end;
/
select decode(dbms_profiler.stop_profiler, '0', 'Profiler stopped', 'Profiler error')
from dual;
select decode(dbms_profiler.flush_data, '0', 'Profiler flushed', 'Profiler error')
from dual;
select 'runid:' || plsql_profiler_runnumber.currval
from dual;
set head on
set pages 200
-----------------------------------------------------------
: evaluate_profiler_results.sql
-----------------------------------------------------------
undef runid
undef owner
undef name
set verify off
select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
from all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
d.total_occur, d.total_time/1000000 total_time
from plsql_profiler_data d, plsql_profiler_units u
where u.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number) p
where s.owner = p.unit_owner (+)
and s.name = p.unit_name (+)
and s.type = p.unit_type (+)
and s.line = p.line# (+)
and s.name = upper('&&name')
and s.owner = upper('&&owner')
order by s.line;
select exec.cnt/total.cnt * 100 "Code% coverage"
from (select count(1) cnt
from plsql_profiler_data d, plsql_profiler_units u
where d.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number
and u.unit_name = upper('&&name')
and u.unit_owner = upper('&&owner')) total,
(select count(1) cnt
from plsql_profiler_data d, plsql_profiler_units u
where d.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number
and u.unit_name = upper('&&name')
and u.unit_owner = upper('&&owner')
and d.total_occur > 0) exec;
undef runid
undef owner
undef name
結論
DBMS_PROFILER是非常強大的工具,其一就是可以識別PL/SQL的性能問題.這個工具最好用在開發(fā)時期,用來調整基于各種應用的情景的代碼,它也能用很好的調整已在使用中的例程并且采取顯而易見的時間去執(zhí)行。總之,這個工具可以給每一行代碼給予性能統(tǒng)計,它可以幫助我們評估和調整到一個出色的水平,當檢查SQL語句的性能問題時,PL/SQL代碼不應該忽略,相反應該調整到最佳的結果.
關鍵字:PL/SQL、Oracle
下面是我提供的怎樣使用配置的簡單例子,運行配置文件來測試下面例程的性能. 例程用到的自定義腳本緊隨其后.
1. 創(chuàng)建過程.
create or replace procedure am_perf_chk (pi_seq in number,
pio_status in out nocopy varchar2) is
l_dat date := sysdate;
begin
if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
pio_status := 'OK';
else
pio_status := 'Invalid tape loaded';
end if;
exception
when others then
pio_status := 'Error in am_perf_chek';
end;
2. 用配置文件調用例程
替換上面的例程, 執(zhí)行call_profiler.sql腳本(腳本代碼參見下面),傳入pi_seq=2
SQL> @d:\am\call_profiler.sql
Profiler started
Invalid tape loaded
PL/SQL procedure successfully completed.
Profiler stopped
Profiler flushed
runid:8
3. 評估執(zhí)行時間:
執(zhí)行eavluate_profiler_results.sql腳本,得到時間統(tǒng)計
SQL> @d:\am\evaluate_profiler_results.sql
Enter value for runid: 8
Enter value for name: am_perf_chk
Enter value for owner: scott
Line Occur Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
1 procedure am_perf_chk (pi_seq in number,
2 pio_status in out nocopy varchar2) is
3 2 43.05965 l_dat date := sysdate;
4 begin
5 1 86.35732 if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
6 0 0 pio_status := 'OK';
7 else
8 1 8.416151 pio_status := 'Invalid tape loaded';
9 end if;
10 exception
11 when others then
12 0 0 pio_status := 'Error in am_perf_chek';!
13 1 2.410361 end;
13 rows selected.
Code% coverage
--------------
66.6666667
4. 正如你看到的,第三行執(zhí)行時間提高到86毫秒.但是改變if語句,重新執(zhí)行上面的過程,將會得到新的結果:
Line Occur Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
1 procedure am_perf_chk (pi_seq in number,
2 pio_status in out nocopy varchar2) is
3 2 17.978816 l_dat date := sysdate;
4 begin
5 1 8.419503 if pi_seq = 1 and trunc(l_dat) = '21-sep-02' then
6 0 0 pio_status := 'OK';
7 else
8 1 7.512684 pio_status := 'Invalid tape loaded';
9 end if;
10 exception
11 when others then
12 0 0 pio_status := 'Error in !am_perf_chek';
13 1 .731657 end;
13 rows selected.
Code% coverage
--------------
66.6666667
5. 正如你看到的, 這種情境下第三行執(zhí)行時間從86毫秒減少到8毫秒,多余的時間是由于內置trunc()函數(shù)引起., 這種情境下如果第一個條件為false,則不會執(zhí)行trunc()函數(shù).這僅僅是個簡單的例子,當你測試的例程越大,你面臨的挑戰(zhàn)更大.
這個配置結果也證明了執(zhí)行期間代碼被覆蓋多少行,從而讓我們知道處于性能監(jiān)視中的代碼范圍。如果任何PL/SQL塊性能出現(xiàn)問題,它也能提煉出各種不同情景的正在在執(zhí)行的代碼并檢查配置結果,從而查明問題所在。
6. 對于一個特定的情景,如果執(zhí)行一段特殊的代碼段,可以得到合理的分析,即使代碼根本一點都不能運行。
環(huán)境的創(chuàng)建
默認安裝或數(shù)據(jù)庫的創(chuàng)建狀態(tài)下,DBMS_PROFILER包不會自動安裝,請DBA用profload.sql腳本創(chuàng)建它.用一個權限較大的或一個單獨的用戶,創(chuàng)建存儲統(tǒng)計信息的表。如果
用如SYS用戶創(chuàng)建,則給其它用戶授予DML權限,并且對這些表創(chuàng)建一個共同的簡寫名.
創(chuàng)建表的如下:
PLSQL_PROFILER_RUNS表:PL/SQL配置的運行細節(jié).
PLSQL_PROFILER_UNITS表:運行中每一個庫單元的信息.
PLSQL_PROFILER_DATA表:所有配置文件運行時的數(shù)據(jù)累積.
PLSQL_PROFILER_RUNNUMBER序列提供了RUNID
運行和解釋配置數(shù)據(jù)
ORACLE提供了三個表來統(tǒng)計,填充RUNID。有許多第三方的工具可以提供自定義的基于這些數(shù)據(jù)的報告,ORACLE提供profrep.sql腳本評估數(shù)據(jù)(在<oracle_home>\plsql\demo\目錄下),下面的兩個簡單腳本就是上面用到的,用來檢查程序單元的執(zhí)行時間.執(zhí)行時間以毫秒存儲
-----------------------------------------------------------
: call_profiler.sql
-----------------------------------------------------------
set head off
set pages 0
select decode(dbms_profiler.start_profiler, '0', 'Profiler started', 'Profiler error')
from dual;
--< place your routine in the below block >--
declare
l_status varchar2(200);
begin
am_perf_chk(2, l_status);
dbms_output.put_line(l_status);
end;
/
select decode(dbms_profiler.stop_profiler, '0', 'Profiler stopped', 'Profiler error')
from dual;
select decode(dbms_profiler.flush_data, '0', 'Profiler flushed', 'Profiler error')
from dual;
select 'runid:' || plsql_profiler_runnumber.currval
from dual;
set head on
set pages 200
-----------------------------------------------------------
: evaluate_profiler_results.sql
-----------------------------------------------------------
undef runid
undef owner
undef name
set verify off
select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
from all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
d.total_occur, d.total_time/1000000 total_time
from plsql_profiler_data d, plsql_profiler_units u
where u.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number) p
where s.owner = p.unit_owner (+)
and s.name = p.unit_name (+)
and s.type = p.unit_type (+)
and s.line = p.line# (+)
and s.name = upper('&&name')
and s.owner = upper('&&owner')
order by s.line;
select exec.cnt/total.cnt * 100 "Code% coverage"
from (select count(1) cnt
from plsql_profiler_data d, plsql_profiler_units u
where d.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number
and u.unit_name = upper('&&name')
and u.unit_owner = upper('&&owner')) total,
(select count(1) cnt
from plsql_profiler_data d, plsql_profiler_units u
where d.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number
and u.unit_name = upper('&&name')
and u.unit_owner = upper('&&owner')
and d.total_occur > 0) exec;
undef runid
undef owner
undef name
結論
DBMS_PROFILER是非常強大的工具,其一就是可以識別PL/SQL的性能問題.這個工具最好用在開發(fā)時期,用來調整基于各種應用的情景的代碼,它也能用很好的調整已在使用中的例程并且采取顯而易見的時間去執(zhí)行。總之,這個工具可以給每一行代碼給予性能統(tǒng)計,它可以幫助我們評估和調整到一個出色的水平,當檢查SQL語句的性能問題時,PL/SQL代碼不應該忽略,相反應該調整到最佳的結果.
關鍵字:PL/SQL、Oracle
新文章:
- CentOS7下圖形配置網(wǎng)絡的方法
- CentOS 7如何添加刪除用戶
- 如何解決centos7雙系統(tǒng)后丟失windows啟動項
- CentOS單網(wǎng)卡如何批量添加不同IP段
- CentOS下iconv命令的介紹
- Centos7 SSH密鑰登陸及密碼密鑰雙重驗證詳解
- CentOS 7.1添加刪除用戶的方法
- CentOS查找/掃描局域網(wǎng)打印機IP講解
- CentOS7使用hostapd實現(xiàn)無AP模式的詳解
- su命令不能切換root的解決方法
- 解決VMware下CentOS7網(wǎng)絡重啟出錯
- 解決Centos7雙系統(tǒng)后丟失windows啟動項
- CentOS下如何避免文件覆蓋
- CentOS7和CentOS6系統(tǒng)有什么不同呢
- Centos 6.6默認iptable規(guī)則詳解