sqlplus之runstats
[導(dǎo)讀]要使用runstats，需要能訪問幾個(gè)V$視圖，并創(chuàng)建一個(gè)表來存儲(chǔ)統(tǒng)計(jì)結(jié)果，還要?jiǎng)?chuàng)建runstats包。為此，需要訪問4個(gè)V$表（就是那些神奇的動(dòng)態(tài)性能表）：V$STATNAME、V$MYSTAT、V
要使用runstats，需要能訪問幾個(gè)V$視圖，并創(chuàng)建一個(gè)表來存儲(chǔ)統(tǒng)計(jì)結(jié)果，還要?jiǎng)?chuàng)建runstats包。
為此，需要訪問4個(gè)V$表（就是那些神奇的動(dòng)態(tài)性能表）：V$STATNAME、V$MYSTAT、V$LATCH、V$TIMER。
這四個(gè)表其實(shí)是別名，真正對(duì)象的名稱應(yīng)為V_$STATNAME、V_$MYSTAT、??V_$LATCH、??V_$TIMER，并且都是在sys賬戶下。
如果scott賬戶要訪問這四張表，?需要將這四張表的select權(quán)限授予給scott賬戶。我們需要再scott下進(jìn)行操作，因此需要將這四張表的select權(quán)限授予給scott賬戶
1.在sys賬戶下授權(quá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賬戶下
2.1查詢V_$表(不能使用別名查詢，只能使用視圖真名)
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賬戶下創(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)告，說明每次測(cè)試(每個(gè)方法)使用了多少資源，以及不同測(cè)試(不同方法)的結(jié)果之差。可以使用p_difference_threshold來控制只查看 差值大于這個(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 #?接下來是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)行的開始時(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??#接下來是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)行的開始時(shí)間。
scott@ORCL>create?table?t(x?int); 表已創(chuàng)建。
3.2創(chuàng)建存儲(chǔ)過程proc1，使用了一條帶綁定變量的SQL語句
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??/ 過程已創(chuàng)建。
3.3創(chuàng)建存儲(chǔ)過程proc2，分別為要插入的每一行構(gòu)造一條獨(dú)立的SQL語句
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??/
過程已創(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?過程已成功完成。3.5執(zhí)行runstats中的方法以及兩個(gè)存儲(chǔ)過程
scott@ORCL>exec?runstats_pkg.rs_start; PL/SQL?過程已成功完成。 scott@ORCL>??exec?proc1; PL/SQL?過程已成功完成。 scott@ORCL>??exec?runstats_pkg.rs_middle; PL/SQL?過程已成功完成。 scott@ORCL>??exec?proc2; PL/SQL?過程已成功完成。 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?過程已成功完成。
下載文檔
