要使用runstats，需要能訪問(wèn)幾個(gè)V$視圖，并創(chuàng)建一個(gè)表來(lái)存儲(chǔ)統(tǒng)計(jì)結(jié)果，還要?jiǎng)?chuàng)建runstats包。

為此，需要訪問(wèn)4個(gè)V$表（就是那些神奇的動(dòng)態(tài)性能表）：V$STATNAME、V$MYSTAT、V$LATCH、V$TIMER。

這四個(gè)表其實(shí)是別名，真正對(duì)象的名稱(chēng)應(yīng)為V_$STATNAME、V_$MYSTAT、??V_$LATCH、??V_$TIMER，并且都是在sys賬戶(hù)下。

如果scott賬戶(hù)要訪問(wèn)這四張表，?需要將這四張表的select權(quán)限授予給scott賬戶(hù)。我們需要再scott下進(jìn)行操作，因此需要將這四張表的select權(quán)限授予給scott賬戶(hù)

1.在sys賬戶(hù)下授權(quán)視圖查詢(xún)權(quán)限給scott?

C:UsersAdministrator>sqlplus?/nolog
SQL*Plus:?Release?11.2.0.1.0?Production?on?星期五?3月?16?11:00:45?2018
Copyright?(c)?1982,?2010,?Oracle.??All?rights?reserved.
idle>conn?/as?sysdba
已連接。
sys@ORCL>grant?select?on?sys.v_$statname?to?"SCOTT";
授權(quán)成功。
sys@ORCL>grant?select?on?sys.v_$mystat?to?"SCOTT";
授權(quán)成功。
sys@ORCL>grant?select?on?sys.v_$latch?to?"SCOTT";
授權(quán)成功。
sys@ORCL>grant?select?on?sys.v_$timer?to?"SCOTT";
授權(quán)成功。
sys@ORCL>

2在scott賬戶(hù)下

2.1查詢(xún)V_$表(不能使用別名查詢(xún)，只能使用視圖真名)

scott@ORCL>select?*?from?sys.v_$statname;?--?OK

scott@ORCL>select?*?from?sys.v$statname;
select?*?from?sys.v$statname
??????????????????*
第?1?行出現(xiàn)錯(cuò)誤:
ORA-00942:?表或視圖不存在

2.2在scott賬戶(hù)下創(chuàng)建視圖

scott@ORCL>create?or?replace?view?stats
??2??as?select?'STAT...'||a.name?name,b.value
??3??from?sys.v_$statname?a,sys.v_$mystat?b
??4??where?a.statistic#?=b.statistic#
??5??union?all
??6??select?'LATCH.'||name,gets
??7??from?sys.v_$latch
??8??union?all
??9??select?'STAT...Elapsed?Time',hsecs?from?sys.v_$timer;
視圖已創(chuàng)建。

2.3創(chuàng)建信息收集表?

scott@ORCL>create?global?temporary?table?run_stats
??2??(runid?varchar2(15),
??3??name?varchar2(80),
??4??value?int)
??5??on?commit?preserve?rows;
表已創(chuàng)建。

2.4創(chuàng)建runstats包

scott@ORCL>create?or?replace?package?runstats_pkg
??2??as
??3??procedure?rs_start;
??4??procedure?rs_middle;
??5??procedure?rs_stop(p_difference_threshold?in?number?default?0);
??6??end;
??7
??8??/
程序包已創(chuàng)建。

p_difference_threshold用于控制最后打印的數(shù)據(jù)量。

runstats會(huì)收集并得到每次運(yùn)行的統(tǒng)計(jì)信息+閂信息，然后打印一個(gè)報(bào)告，說(shuō)明每次測(cè)試(每個(gè)方法)使用了多少資源，以及不同測(cè)試(不同方法)的結(jié)果之差?？梢允褂胮_difference_threshold來(lái)控制只查看 差值大于這個(gè)數(shù) 的統(tǒng)計(jì)結(jié)果和閂信息。由于這個(gè)參數(shù)默認(rèn)為0，所以默認(rèn)情況下可以看到所有輸出。

2.5創(chuàng)建包體

scott@ORCL>create?or?replace?package?body?runstats_pkg
??2??????as
??3??????g_start?number;?#這3個(gè)全局變量?用于記錄每次運(yùn)行的耗用時(shí)間
??4??????g_run1??number;
??5??????g_run2??number;

6?????#下面是rs_start例程，這個(gè)例程只是清空保存統(tǒng)計(jì)結(jié)果的表，并填入"上一次"(before)得到的統(tǒng)計(jì)結(jié)果+閂信息。

?????然后獲得當(dāng)前定時(shí)器值，這是一種時(shí)鐘，可用于計(jì)算耗用時(shí)間(單位百分之一秒)

?7 ? ? ?procedure rs_start ? 8 ? ? ?is ?9 ? ? ?begin 10 ? ? ? ? delete from run_stats; 11 12 ? ? ? ? insert into run_stats 13 ? ? ? ? select 'before', stats.* from stats; 14 ? ? ? ? ? g_start := dbms_utility.get_cpu_time; 15 ? ? end;

16 #?接下來(lái)是rs_middle例程，這個(gè)例程只是把第一次測(cè)試運(yùn)行的耗用時(shí)間記錄在g_run1中。?然后插入當(dāng)前的一組統(tǒng)計(jì)結(jié)果和閂信息。

#如果把這些值與先前在?rs_start中保存的值相減，就會(huì)發(fā)現(xiàn)第一個(gè)方法使用了多少閂，以及使用了多少游標(biāo)(一種統(tǒng)計(jì)結(jié)果)，等等。

#最后，記錄下一次運(yùn)行的開(kāi)始時(shí)間

?17?????procedure?rs_middle
?18?????is
?19?????begin
?20?????????g_run1?:=?(dbms_utility.get_cpu_time-g_start);
?21
?22?????????insert?into?run_stats
?23?????????select?'after?1',?stats.*?from?stats;
?24?????????g_start?:=?dbms_utility.get_cpu_time;
?25
?26?????end;
?27
?28?????procedure?rs_stop(p_difference_threshold?in?number?default?0)
?29?????is
?30?????begin
?31?????????g_run2?:=?(dbms_utility.get_cpu_time-g_start);
?32
?33?????????dbms_output.put_line
?34?????????(?'Run1?ran?in?'?||?g_run1?||?'?cpu?hsecs'?);
?35?????????dbms_output.put_line
?36?????????(?'Run2?ran?in?'?||?g_run2?||?'?cpu?hsecs'?);
?37?????????????if?(?g_run2?<>?0?)
?38?????????????then
?39?????????dbms_output.put_line
?40?????????(?'run?1?ran?in?'?||?round(g_run1/g_run2*100,2)?||
?41???????????'%?of?the?time'?);
?42?????????????end?if;
?43?????????dbms_output.put_line(?chr(9)?);
?44
?45?????????insert?into?run_stats
?46?????????select?'after?2',?stats.*?from?stats;
?47
?48?????????dbms_output.put_line
?49?????????(?rpad(?'Name',?30?)?||?lpad(?'Run1',?12?)?||
?50???????????lpad(?'Run2',?12?)?||?lpad(?'Diff',?12?)?);
?51
?52?????????for?x?in
?53?????????(?select?rpad(?a.name,?30?)?||
?54??????????????????to_char(?b.value-a.value,?'999,999,999'?)?||
?55??????????????????to_char(?c.value-b.value,?'999,999,999'?)?||
?56???????????????????to_char(?(?(c.value-b.value)-(b.value-a.value)),
?57??????????????????????????????????????'999,999,999'?)?data
?58?????????????from?run_stats?a,?run_stats?b,?run_stats?c
?59????????????where?a.name?=?b.name
?60??????????????and?b.name?=?c.name
?61??????????????and?a.runid?=?'before'
?62??????????????and?b.runid?=?'after?1'

?63??????????????and?c.runid?=?'after?2'
?64
?65??????????????and?abs(?(c.value-b.value)?-?(b.value-a.value)?)
?66????????????????????>?p_difference_threshold
?67????????????order?by?abs(?(c.value-b.value)-(b.value-a.value))
?68?????????)?loop
?69?????????????dbms_output.put_line(?x.data?);
?70?????????end?loop;
?71
?72?????????dbms_output.put_line(?chr(9)?);
?73?????????dbms_output.put_line
?74?????????(?'Run1?latches?total?versus?runs?--?difference?and?pct'?);
?75?????????dbms_output.put_line
?76?????????(?lpad(?'Run1',?12?)?||?lpad(?'Run2',?12?)?||
?77???????????lpad(?'Diff',?12?)?||?lpad(?'Pct',?10?)?);
?78
?79?????????for?x?in
?80?????????(?select?to_char(?run1,?'999,999,999'?)?||
?81??????????????????to_char(?run2,?'999,999,999'?)?||
?82??????????????????to_char(?diff,?'999,999,999'?)?||
?83??????????????????to_char(?round(?run1/decode(?run2,?0,
?84???????????????????????????????to_number(0),?run2)?*100,2?),?'99,999.99'?)?||
?'%'?data
?85?????????????from?(?select?sum(b.value-a.value)?run1,?sum(c.value-b.value)?ru
n2,
?86???????????????????????????sum(?(c.value-b.value)-(b.value-a.value))?diff
?87??????????????????????from?run_stats?a,?run_stats?b,?run_stats?c
?88?????????????????????where?a.name?=?b.name
?89??????????????????????and?b.name?=?c.name
?90???????????????????????and?a.runid?=?'before'
?91???????????????????????and?b.runid?=?'after?1'
?92???????????????????????and?c.runid?=?'after?2'
?93???????????????????????and?a.name?like?'LATCH%'
?94?????????????????????)
?95?????????)?loop
?96?????????????dbms_output.put_line(?x.data?);
?97?????????end?loop;
?98?????end;
?99
100????end;
101????/

程序包體已創(chuàng)建。

3.使用runstats

3.1創(chuàng)建表T

16??#接下來(lái)是rs_middle例程，這個(gè)例程只是把第一次測(cè)試運(yùn)行的耗用時(shí)間記錄在g_run1中。?然后插入當(dāng)前的一組統(tǒng)計(jì)結(jié)果和閂信息。
#如果把這些值與先前在?rs_start中保存的值相減，就會(huì)發(fā)現(xiàn)第一個(gè)方法使用了多少閂，以及使用了多少游標(biāo)(一種統(tǒng)計(jì)結(jié)果)，等等。
#最后，記錄下一次運(yùn)行的開(kāi)始時(shí)間。

scott@ORCL>create?table?t(x?int);
表已創(chuàng)建。

3.2創(chuàng)建存儲(chǔ)過(guò)程proc1，使用了一條帶綁定變量的SQL語(yǔ)句

scott@ORCL>create?or?replace?procedure?proc1
??2??as
??3??begin
??4??????for?i?in?1?..?10000
??5??????loop
??6??????????execute?immediate
??7??????????'insert?into?t?values(:x)'using?i;
??8??????end?loop;
??9??end;
?10??/

過(guò)程已創(chuàng)建。

3.3創(chuàng)建存儲(chǔ)過(guò)程proc2，分別為要插入的每一行構(gòu)造一條獨(dú)立的SQL語(yǔ)句

scott@ORCL>create?or?replace?procedure?proc2
??2??as
??3??begin
??4??????for?i?in?1?..?10000
??5??????loop
??6??????????execute?immediate
??7??????????'insert?into?t?values('||?i?||')';
??8??????????commit;
??9??????end?loop;
?10??????end?proc2;
?11??/

過(guò)程已創(chuàng)建。

3.4使dbms_output.put_line 生效

要使用dbms_output.put_line ，則必須在sqlplus中顯式聲明：

scott@ORCL>set?serverout?on
scott@ORCL>exec?dbms_output.put_line('yinn');
yinn

PL/SQL?過(guò)程已成功完成。

3.5執(zhí)行runstats中的方法以及兩個(gè)存儲(chǔ)過(guò)程

scott@ORCL>exec?runstats_pkg.rs_start;
PL/SQL?過(guò)程已成功完成。

scott@ORCL>??exec?proc1;
PL/SQL?過(guò)程已成功完成。

scott@ORCL>??exec?runstats_pkg.rs_middle;
PL/SQL?過(guò)程已成功完成。

scott@ORCL>??exec?proc2;
PL/SQL?過(guò)程已成功完成。

scott@ORCL>??exec?runstats_pkg.rs_stop(10000);

Run1?ran?in?29?cpu?hsecs
Run2?ran?in?546?cpu?hsecs
run?1?ran?in?5.31%?of?the?time

Name??????????????????????????????????Run1????????Run2????????Diff
STAT...calls?to?get?snapshot?s??????????85??????10,087??????10,002
STAT...commit?cleanouts?succes???????????9??????10,013??????10,004
STAT...opened?cursors?cumulati??????10,081??????20,091??????10,010
STAT...consistent?gets?from?ca?????????273??????10,284??????10,011
STAT...consistent?gets?????????????????273??????10,284??????10,011
STAT...parse?count?(total)??????????????43??????10,055??????10,012
STAT...commit?cleanouts??????????????????9??????10,021??????10,012
STAT...IMU?Redo?allocation?siz???????????0??????17,760??????17,760
STAT...db?block?changes?????????????20,323??????40,182??????19,859
STAT...db?block?gets?from?cach??????????81??????20,041??????19,960
LATCH.DML?lock?allocation???????????????22??????20,006??????19,984
LATCH.enqueues??????????????????????????93??????20,281??????20,188
LATCH.redo?writing???????????????????????6??????28,119??????28,113
LATCH.messages??????????????????????????22??????28,488??????28,466
STAT...enqueue?requests?????????????????58??????30,026??????29,968
STAT...enqueue?releases?????????????????56??????30,028??????29,972
LATCH.session?allocation????????????????18??????30,016??????29,998
LATCH.In?memory?undo?latch???????????????3??????40,020??????40,017
LATCH.kks?stats?????????????????????????25??????47,406??????47,381
LATCH.redo?allocation????????????????????7??????48,116??????48,109
STAT...db?block?gets?from?cach??????10,468??????60,187??????49,719
STAT...db?block?gets????????????????10,468??????60,187??????49,719
STAT...recursive?calls??????????????11,218??????60,937??????49,719
LATCH.undo?global?data?????????????????157??????50,201??????50,044
STAT...session?logical?reads????????10,741??????70,471??????59,730
LATCH.enqueue?hash?chains??????????????141??????60,348??????60,207
LATCH.shared?pool?simulator?????????????33??????66,792??????66,759
STAT...session?uga?memory?max??????168,592??????93,360?????-75,232
STAT...session?uga?memory???????????65,488?????196,464?????130,976
LATCH.row?cache?objects????????????????694?????180,385?????179,691
LATCH.cache?buffers?chains??????????52,432?????282,416?????229,984
LATCH.shared?pool???????????????????20,733?????432,092?????411,359
STAT...undo?change?vector?size?????645,592???1,323,420?????677,828
STAT...redo?size?????????????????2,385,696???5,111,572???2,725,876
STAT...IMU?undo?allocation?siz???????????0???5,512,320???5,512,320

Run1?latches?total?versus?runs?--?difference?and?pct
Run1????????Run2????????Diff???????Pct
75,892???1,376,015???1,300,123??????5.52%

PL/SQL?過(guò)程已成功完成。