??? Statspack的Report文檔其實(shí)是將Oracle系統(tǒng)的各項(xiàng)系統(tǒng)個(gè)指標(biāo)分項(xiàng)統(tǒng)計(jì)成各個(gè)報(bào)表，之后放入到Report文檔中，所以可以根據(jù)自己的需要截取其中的一部分進(jìn)行分析。里面的數(shù)據(jù)非常之多，要一次分析完全是不太現(xiàn)實(shí)的。
?
??? 下面截取一下常見(jiàn)的學(xué)習(xí)一下：
?
?
1、報(bào)表頭信息
?
??? 報(bào)表頭顯示了數(shù)據(jù)庫(kù)實(shí)例的相關(guān)信息，包括數(shù)據(jù)庫(kù)名稱、ID、版本號(hào)、主機(jī)等信息；以及起止snap的信息等：
?
STATSPACK report for
?
Database??? DB Id??? Instance???? Inst Num Startup Time??? Release???? RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
?????????? 486494833 dodo??????????????? 1 21-2月 -09 16:2 10.2.0.1.0? NO
?????????????????????????????????????????? 7
?
Host? Name:?? HHZ-0099???????? Num CPUs:??? 2??????? Phys Memory (MB):??? 2,046
~~~~
?
Snapshot?????? Snap Id???? Snap Time????? Sessions Curs/Sess Comment
~~~~~~~~??? ---------- ------------------ -------- --------- -------------------
Begin Snap:????????? 1 21-2月 -09 20:27:36????? 18?????? 3.4
? End Snap:????????? 4 21-2月 -09 21:05:50????? 19?????? 3.1
?? Elapsed:?????????????? 38.23 (mins)
?
Cache Sizes?????????????????????? Begin??????? End
~~~~~~~~~~~????????????????? ---------- ----------
?????????????? Buffer Cache:?????? 404M???????????? Std Block Size:???????? 8K
?????????? Shared Pool Size:?????? 164M???????????????? Log Buffer:???? 6,860K
?
?
2、負(fù)載間檔
?
??? 該部分提供每秒和每個(gè)事物的統(tǒng)計(jì)信息，是監(jiān)控系統(tǒng)吞吐量和負(fù)載變化的重要部分。
?
Load Profile??????????????????????????? Per Second?????? Per Transaction
~~~~~~~~~~~~?????????????????????? ---------------?????? ---------------
????????????????? Redo size:????????????? 1,888.85???????????? 22,805.41
????????????? Logical reads:???????????????? 51.72??????????????? 624.40
????????????? Block changes:????????????????? 5.49???????????????? 66.27
???????????? Physical reads:????????????????? 0.01????????????????? 0.13
??????????? Physical writes:????????????????? 1.52???????????????? 18.33
???????????????? User calls:????????????????? 0.51????????????????? 6.13
???????????????????? Parses:????????????????? 2.21???????????????? 26.72
??????????????? Hard parses:????????????????? 0.25????????????????? 3.00
????????????????????? Sorts:????????????????? 0.79????????????????? 9.59
???????????????????? Logons:????????????????? 0.03????????????????? 0.39
?????????????????? Executes:????????????????? 4.12???????????????? 49.78
?????????????? Transactions:????????????????? 0.08
?
? % Blocks changed per Read:?? 10.61??? Recursive Call %:??? 97.58
?Rollback per transaction %:??? 0.00?????? Rows per Sort:??? 51.29
?
??? 說(shuō)明：
??? ⑴ Redo size：每秒產(chǎn)生的日志大小(單位字節(jié))，可標(biāo)志數(shù)據(jù)變更頻率, 數(shù)據(jù)庫(kù)任務(wù)的繁重與否
??? ⑵ Logical reads：平?jīng)Q每秒產(chǎn)生的邏輯讀，單位是block
??? ⑶ Block changes：每秒block變化數(shù)量，數(shù)據(jù)庫(kù)事物帶來(lái)改變的塊數(shù)量
??? ⑷ Physical reads：平均每秒數(shù)據(jù)庫(kù)從磁盤讀取的block數(shù)
??? ⑸ Physical writes：平均每秒數(shù)據(jù)庫(kù)寫磁盤的block數(shù)
??? ⑹ User calls：每秒用戶call次數(shù)
??? ⑺ Parses: 每秒解析次數(shù)，近似反應(yīng)每秒語(yǔ)句的執(zhí)行次數(shù), 軟解析每秒超過(guò)300次意味著你的“應(yīng)用程序”效率不高，調(diào)整session_cursor_cache
??? ⑻ Hard parses：每秒產(chǎn)生的硬解析次數(shù), 每秒超過(guò)100次，就可能說(shuō)明你綁定使用的不好
??? ⑼ Sorts：每秒產(chǎn)生的排序次數(shù)
??? ⑽ Logons：每秒的登陸次數(shù)
??? ⑾ Executes：每秒的執(zhí)行次數(shù)
??? ⑿ Transactions：每秒產(chǎn)生的事務(wù)數(shù)，反映數(shù)據(jù)庫(kù)任務(wù)繁重與否
??? ⒀ Recursive Call %: 如果有很多PLSQL，那么他就會(huì)比較高
??? ⒁ Rollback per transaction %：看回滾率是不是很高，因?yàn)榛貪L很耗資源
?
??? 注：回滾率=Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% ，
如果回滾率過(guò)高，可能說(shuō)明你的數(shù)據(jù)庫(kù)經(jīng)歷了太多的無(wú)效操作，
過(guò)多的回滾可能還會(huì)帶來(lái)Undo Block的競(jìng)爭(zhēng)。
?
3、實(shí)例命中率
?
??? 該部分可以提前找出ORACLE潛在將要發(fā)生的性能問(wèn)題，很重要
?
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
??????????? Buffer Nowait %:?? 99.99?????? Redo NoWait %:? 100.00
??????????? Buffer? Hit?? %:?? 99.98??? In-memory Sort %:? 100.00
??????????? Library Hit?? %:?? 94.65??????? Soft Parse %:?? 88.77
???????? Execute to Parse %:?? 46.34???????? Latch Hit %:? 100.00
Parse CPU to Parse Elapsd %:?? 97.83???? % Non-Parse CPU:?? 95.37
?
?Shared Pool Statistics??????? Begin?? End
?????????????????????????????? ------? ------
???????????? Memory Usage %:?? 60.34?? 71.99
??? % SQL with executions>1:?? 63.66?? 66.00
? % Memory for SQL w/exec>1:?? 82.19?? 73.37
?
??? 說(shuō)明:
??? ⑴ Buffer Nowait %：在緩沖區(qū)中獲取Buffer的未等待比率, Buffer Nowait<99%說(shuō)明，有可能是有熱塊(查找x$bh的tch和v$latch_children的cache buffers chains)
??? ⑵ Redo NoWait %：在Redo緩沖區(qū)獲取Buffer的未等待比率
??? ⑶ Buffer Hit %：數(shù)據(jù)塊在數(shù)據(jù)緩沖區(qū)中得命中率，通常應(yīng)在90%以上，否則需要調(diào)整，小于95%，重要的參數(shù),小于90%可能是要加db_cache_size，但是大量的非選擇的索引也會(huì)造成該值很高（大量的db file sequential read）
??? ⑷ In-memory Sort %：在內(nèi)存中的排序率
??? ⑸ Library Hit %：主要代表sql在共享區(qū)的命中率，通常在95%以上，否則需要要考慮加大共享池，綁定變量，修改cursor_sharing等參數(shù)。
??? ⑹ Soft Parse %：近似看作sql在共享區(qū)的命中率，小于95%需要考慮到綁定，如果低于80%，那么就可能sql基本沒(méi)有被重用
??? ⑺ Execute to Parse %：sql語(yǔ)句解析后被重復(fù)執(zhí)行的次數(shù)，如果過(guò)低，可以考慮設(shè)置session_cached_cursors參數(shù), 公式為100 * (1 - Parses/Executions) = Execute to Parse 所以如果系統(tǒng)Parses > Executions,就可能出現(xiàn)該比率小于0的情況, 該值<0通常說(shuō)明shared pool設(shè)置或效率存在問(wèn)題造成反復(fù)解析，reparse可能較嚴(yán)重,或者可是同snapshot有關(guān)如果該值為負(fù)值或者極低,通常說(shuō)明數(shù)據(jù)庫(kù)性能存在問(wèn)題。
??? ⑻ Latch Hit %: 要確保>99%，否則存在嚴(yán)重的性能問(wèn)題，比如綁定等會(huì)影響該參數(shù)
??? ⑼
Parse CPU to Parse Elapsd %：解析實(shí)際運(yùn)行事件/(解析實(shí)際運(yùn)行時(shí)間+解析中等待資源時(shí)間)越高越好
??? ⑽ % Non-Parse CPU：查詢實(shí)際運(yùn)行時(shí)間/(查詢實(shí)際運(yùn)行時(shí)間+sql解析時(shí)間)，太低表示解析消耗時(shí)間過(guò)多。100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd %
?
??? 注：
??? 如果一個(gè)經(jīng)常訪問(wèn)的列上的索引被刪除，可能會(huì)造成Buffer?Hit顯著的下降
??? 如果增加了索引，但是他影響了Oracle正確的選擇表連接時(shí)的驅(qū)動(dòng)順序，那么可能會(huì)導(dǎo)致Buffer?Hit顯著增高
??? 如果你的命中率變化幅度很大，說(shuō)明你要改變SQL模式
??? Shared Pool相關(guān)統(tǒng)計(jì)數(shù)據(jù)
??? ⑴ Memory Usage %：共享池內(nèi)存使用率，應(yīng)該穩(wěn)定在70%-90%間，太小浪費(fèi)內(nèi)存，太大則內(nèi)存不足。
??? ⑵ % SQL with executions>1：執(zhí)行次數(shù)大于1的sql比率，若太小可能是沒(méi)有使用bind variables。
??? ⑶ % Memory for SQL w/exec>1：也即是memory for sql with execution>1:執(zhí)行次數(shù)大于1的sql消耗內(nèi)存/所有sql消耗的內(nèi)存
?
?
4、首要等待事件(Top 5)
?
??? Oracle等待事件是衡量Oracle運(yùn)行狀況的重要依據(jù)及指示，主要有空閑等待事件和非空閑等待事件。
??? TIMED_STATISTICS = TRUE 等待事件按等待的時(shí)間排序
??? TIMED_STATISTICS = FALSE 等待事件按等待的數(shù)量排序
??? Statspack中必須在Session上設(shè)置TIMED_STATISTICS = TRUE
??? 空閑等待事件是Oracle正等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫(kù)時(shí)候，不用過(guò)多注意這部分事件，非空閑等待事件專門針對(duì)Oracle的活動(dòng)，指數(shù)據(jù)庫(kù)任務(wù)或應(yīng)用程序運(yùn)行過(guò)程中發(fā)生的等待，這些等待事件是我們?cè)谡{(diào)整數(shù)據(jù)庫(kù)應(yīng)該關(guān)注的。
Top 5 Timed Events??????????????????????????????????????????????????? Avg %Total
~~~~~~~~~~~~~~~~~~?????????????????????????????????????????????????? wait?? Call
Event??????????????????????????????????????????? Waits??? Time (s)?? (ms)?? Time
----------------------------------------- ------------ ----------- ------ ------
db file parallel write?????????????????????????? 1,575????????? 20???? 13?? 53.8
CPU time??????????????????????????????????????????????????????? 10????????? 26.0
control file sequential read???????????????????? 2,001?????????? 3????? 2??? 8.7
control file parallel write??????????????????????? 805?????????? 3????? 4??? 7.7
log file parallel write??????????????????????????? 676?????????? 1????? 1??? 1.8
????????? -------------------------------------------------------------
??? 比較影響性能常見(jiàn)等待事件有：
??? ① db file scattered read
??? 該事件通常與全表掃描有關(guān)。因?yàn)槿頀呙枋潜环湃雰?nèi)存中進(jìn)行的，通常情況下它不可能被放入連續(xù)的緩沖區(qū)中，所以就散布在緩沖區(qū)的緩存中。該指數(shù)的數(shù)量過(guò)大說(shuō)明缺少索引或者限制了索引的使用(也可以調(diào)整optimizer_index_cost_adj) 。這種情況也可能是正常的，因?yàn)閳?zhí)行全表掃描可能比索引掃描效率更高。當(dāng)系統(tǒng)存在這些等待時(shí)，需要通過(guò)檢查來(lái)確定全表掃描是否必需的來(lái)調(diào)整。如果經(jīng)常必須進(jìn)行全表掃描，而且表比較小, 把該表存人keep池。如果是大表經(jīng)常進(jìn)行全表掃描,那么應(yīng)該是OLAP系統(tǒng)，而不是OLTP的.
??? ② db file sequential read
??? 該事件說(shuō)明在單個(gè)數(shù)據(jù)塊上大量等待，該值過(guò)高通常是由于表間連接順序很糟糕，或者使用了非選擇性索引。通過(guò)將這種等待與statspack報(bào)表中已知其它問(wèn)題聯(lián)系起來(lái)(如效率不高的sql),通過(guò)檢查確保索引掃描是必須的，并確保多表連接的連接順序來(lái)調(diào)整, DB_CACHE_SIZE可以決定該事件出現(xiàn)的頻率
??? ③ buffer busy wait
??? 當(dāng)緩沖區(qū)以一種非共享方式或者如正在被讀入到緩沖時(shí)，就會(huì)出現(xiàn)該等待。該值不應(yīng)該大于1%，確認(rèn)是不是由于熱點(diǎn)塊造成(如果是可以用反轉(zhuǎn)索引,或者用更小塊大小)
??? ④ latch free
??? 常跟應(yīng)用沒(méi)有很好的應(yīng)用綁定有關(guān)。閂鎖是底層的隊(duì)列機(jī)制(更加準(zhǔn)確的名稱應(yīng)該是互斥機(jī)制)，用于保護(hù)系統(tǒng)全局區(qū)(SGA)共享內(nèi)存結(jié)構(gòu)閂鎖用于防止對(duì)內(nèi)存結(jié)構(gòu)的并行訪問(wèn)。如果閂鎖不可用，就會(huì)記錄一次閂鎖丟失。絕大多數(shù)得閂鎖問(wèn)題都與使用綁定變量失敗(庫(kù)緩存閂鎖)、生成重作問(wèn)題(重執(zhí)行分配閂鎖)、緩存的爭(zhēng)用問(wèn)題(緩存LRU鏈) 以及緩存的熱數(shù)據(jù)寬塊(緩存鏈)有關(guān)。當(dāng)閂鎖丟失率高于0.5%時(shí)，需要調(diào)整這個(gè)問(wèn)題。
??? ⑤ log buffer space
??? 日志緩沖區(qū)寫的速度快于LGWR寫REDOFILE的速度,可以增大日志文件大小，增加日志緩沖區(qū)的大小，或者使用更快的磁盤來(lái)寫數(shù)據(jù)。
??? ⑥ logfile switch
??? 通常是因?yàn)闅w檔速度不夠快，需要增大重做日志
??? ⑦ log file sync
??? 當(dāng)一個(gè)用戶提交或回滾數(shù)據(jù)時(shí)，LGWR將會(huì)話得重做操作從日志緩沖區(qū)填充到日志文件中，用戶的進(jìn)程必須等待這個(gè)填充工作完成。在每次提交時(shí)都出現(xiàn)，如果這個(gè)等待事件影響到數(shù)據(jù)庫(kù)性能，那么就需要修改應(yīng)用程序的提交頻率, 為減少這個(gè)等待事件，須一次提交更多記錄，或者將重做日志REDO LOG 文件訪在不同的物理磁盤上。
?
??? 更加具體的說(shuō)明可以參見(jiàn)eygle大師的《Statspack-v3.0》
?
?
5、實(shí)例的CPU及內(nèi)存信息
?
Host CPU? (CPUs: 2)
~~~~~~~~????????????? Load Average
????????????????????? Begin???? End????? User? System??? Idle???? WIO???? WCPU
??????????????????? ------- -------?? ------- ------- ------- ------- --------
???????????????????????????????????????? 1.62??? 1.99?? 96.39
?
Instance CPU
~~~~~~~~~~~~
????????????? % of total CPU for Instance:??? 0.27
????????????? % of busy? CPU for Instance:??? 7.54
? %DB time waiting for CPU - Resource Mgr:
?
Memory Statistics?????????????????????? Begin????????? End
~~~~~~~~~~~~~~~~~??????????????? ------------ ------------
????????????????? Host Mem (MB):????? 2,046.4????? 2,046.4
?????????????????? SGA use (MB):??????? 584.0??????? 584.0
?????????????????? PGA use (MB):???????? 40.3???????? 40.5
??? % Host Mem used for SGA+PGA:???????? 30.5???????? 30.5
????????? -------------------------------------------------------------
?
Time Model System Stats? DB/Inst: DODO/dodo? Snaps: 1-4
-> Ordered by % of DB time desc, Statistic name
?
Statistic?????????????????????????????????????? Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time???????????????????????????? 9.4??????? 100.9
DB CPU?????????????????????????????????????????????? 8.2???????? 87.5
PL/SQL execution elapsed time??????????????????????? 2.3???????? 24.2
parse time elapsed?????????????????????????????????? 1.7???????? 17.8
hard parse elapsed time????????????????????????????? 1.5???????? 15.5
PL/SQL compilation elapsed time????????????????????? 0.1????????? 1.6
failed parse elapsed time??????????????????????????? 0.0?????????? .3
hard parse (sharing criteria) elaps????????????????? 0.0?????????? .2
connection management call elapsed?????????????????? 0.0?????????? .1
repeated bind elapsed time?????????????????????????? 0.0?????????? .1
hard parse (bind mismatch) elapsed?????????????????? 0.0?????????? .0
sequence load elapsed time?????????????????????????? 0.0?????????? .0
DB time????????????????????????????????????????????? 9.4
background elapsed time???????????????????????????? 32.4
background cpu time????????????????????????????????? 4.3
????????? -------------------------------------------------------------
?
?
6、數(shù)據(jù)庫(kù)用戶程序發(fā)生的所有等待事件
?
Wait Events? DB/Inst: DODO/dodo? Snaps: 1-4
-> s - second, cs - centisecond,? ms - millisecond, us - microsecond
-> %Timeouts:? value of 0 indicates value was < .5%.? Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
?
??????????????????????????????????????????????????????????????????? Avg
??????????????????????????????????????????????? %Time Total Wait?? wait??? Waits
Event??????????????????????????????????? Waits? -outs?? Time (s)?? (ms)???? /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write?????????????????? 1,575????? 0???????? 20???? 13????? 8.3
control file sequential read???????????? 2,001????? 0????????? 3????? 2???? 10.5
control file parallel write??????????????? 805????? 0????????? 3????? 4????? 4.2
log file parallel write??????????????????? 676????? 0????????? 1????? 1????? 3.6
os thread startup?????????????????????????? 35????? 0????????? 0???? 10????? 0.2
db file sequential read???????????????????? 21????? 0????????? 0???? 13????? 0.1
log file sync?????????????????????????????? 43????? 0????????? 0????? 2????? 0.2
direct path write?????????????????????????? 35????? 0????????? 0????? 2????? 0.2
rdbms ipc reply???????????????????????????? 28????? 0????????? 0????? 0????? 0.1
SQL*Net more data to client???????????????? 45????? 0????????? 0????? 0????? 0.2
Streams AQ: qmn slave idle wait???????????? 82????? 0????? 2,297? 28012????? 0.4
Streams AQ: qmn coordinator idle?????????? 163???? 52????? 2,297? 14092????? 0.9
virtual circuit status????????????????????? 76??? 100????? 2,274? 29921????? 0.4
jobq slave wait??????????????????????????? 712???? 99????? 2,127?? 2988????? 3.7
Streams AQ: waiting for time mana??????????? 1??? 100????? 1,109 ######????? 0.0
SQL*Net message from client??????????????? 682????? 0??????? 745?? 1092????? 3.6
class slave wait???????????????????????????? 2??? 100???????? 10?? 4999????? 0.0
SQL*Net message to client????????????????? 683????? 0????????? 0????? 0????? 3.6
????????? -------------------------------------------------------------
?
?
7、數(shù)據(jù)庫(kù)后臺(tái)進(jìn)程發(fā)生的等待事件
?
Background Wait Events? DB/Inst: DODO/dodo? Snaps: 1-4
-> %Timeouts:? value of 0 indicates value was < .5%.? Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
?
??????????????????????????????????????????????????????????????????? Avg
??????????????????????????????????????????????? %Time Total Wait?? wait??? Waits
Event??????????????????????????????????? Waits? -outs?? Time (s)?? (ms)???? /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write?????????????????? 1,575????? 0???????? 20???? 13????? 8.3
control file parallel write??????????????? 805????? 0????????? 3????? 4????? 4.2
control file sequential read?????????????? 729????? 0????????? 2????? 3????? 3.8
log file parallel write??????????????????? 677????? 0????????? 1????? 1????? 3.6
os thread startup?????????????????????????? 35????? 0????????? 0???? 10????? 0.2
direct path write??????????????????????????? 5????? 0????????? 0???? 13????? 0.0
db file sequential read????????????????????? 5????? 0????????? 0???? 10????? 0.0
events in waitclass Other?????????????????? 54????? 0????????? 0????? 0????? 0.3
rdbms ipc message??????????????????????? 8,090???? 92???? 23,167?? 2864???? 42.6
Streams AQ: qmn slave idle wait???????????? 82????? 0????? 2,297? 28012????? 0.4
Streams AQ: qmn coordinator idle?????????? 163???? 52????? 2,297? 14092????? 0.9
pmon timer???????????????????????????????? 789??? 100????? 2,292?? 2906????? 4.2
smon timer????????????????????????????????? 14???? 43????? 2,267 ######????? 0.1
Streams AQ: waiting for time mana??????????? 1??? 100????? 1,109 ######????? 0.0
????????? -------------------------------------------------------------
?
?
8、等待事件柱狀圖
?
Wait Event Histogram? DB/Inst: DODO/dodo? Snaps: 1-4
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
?
?????????????????????????? Total ----------------- % of Waits ------------------
Event????????????????????? Waits? <1ms? <2ms? <4ms? <8ms <16ms <32ms? <=1s?? >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy????? 17? 100.0
SQL*Net break/reset to cli??? 2? 100.0
SQL*Net more data to clien?? 45? 100.0
buffer busy waits???????????? 5? 100.0
control file parallel writ? 805?? 22.1? 69.6??? .5?? 1.0?? 1.5?? 2.6?? 2.7
control file sequential re 2001?? 91.9??? .4??? .6?? 3.1?? 1.8??? .8?? 1.3
db file parallel write???? 1575?? 47.3?? 3.5?? 8.1?? 6.4? 12.8?? 7.3? 14.6
db file scattered read??????? 1? 100.0
db file sequential read????? 21?? 23.8????????????? 19.0? 33.3?? 9.5? 14.3
direct path read????????????? 5? 100.0
direct path write??????????? 35?? 97.1???????????????????????????????? 2.9
latch free??????????????????? 5? 100.0
latch: library cache????????? 2? 100.0
latch: shared pool??????????? 1? 100.0
log file parallel write???? 676?? 96.2??? .7??? .6??? .3??? .4?? 1.0??? .7
log file sync??????????????? 43?? 86.0???????? 2.3?? 2.3?? 2.3?? 4.7?? 2.3
os thread startup??????????? 35????????????????????? 5.7? 91.4?? 2.9
rdbms ipc reply????????????? 28? 100.0
undo segment extension??????? 3? 100.0
SQL*Net message from clien? 682?? 67.9?? 7.6? 14.1?? 5.1?? 2.1??? .9?? 1.0?? 1.3
SQL*Net message to client?? 683? 100.0
SQL*Net more data from cli?? 61? 100.0
Streams AQ: qmn coordinato? 163?? 48.5????????????????????????????????????? 51.5
Streams AQ: qmn slave idle?? 82??????????????????????????????????????????? 100.0
Streams AQ: waiting for ti??? 1??????????????????????????????????????????? 100.0
class slave wait????????????? 2??????????????????????????????????????????? 100.0
dispatcher timer???????????? 38??????????????????????????????????????????? 100.0
jobq slave wait???????????? 712???????????????????????????????????????? .3? 99.7
pmon timer????????????????? 789??? 1.1????????? .1????????????????????? .3? 98.5
rdbms ipc message????????? 8083??? 5.2??? .6??? .4??? .6??? .5??? .1? 29.3? 63.2
smon timer?????????????????? 13??? 7.7?????????????? 7.7?? 7.7??????? 15.4? 61.5
virtual circuit status?????? 76??????????????????????????????????????????? 100.0
????????? -------------------------------------------------------------
?
?
9、TOP SQL
?
??? 調(diào)整首要的25個(gè)緩沖區(qū)讀操作和首要的25個(gè)磁盤讀操作做的查詢，將可對(duì)系統(tǒng)性能產(chǎn)生5%到5000%的增益。
?
??? 內(nèi)容太多不再截取...
?
?
10、實(shí)例活動(dòng)
?
Instance Activity Stats? DB/Inst: DODO/dodo? Snaps: 1-4
?
Statistic????????????????????????????????????? Total???? per Second??? per Trans
--------------------------------- ------------------ -------------- ------------
redo synch time?????????????????????????????????? 11??????????? 0.0????????? 0.1
redo synch writes??????????????????????????????? 614??????????? 0.3????????? 3.2
redo wastage???????????????????????????????? 174,084?????????? 75.9??????? 916.2
redo write time?????????????????????????????????? 61??????????? 0.0????????? 0.3
redo writer latching time????????????????????????? 2??????????? 0.0????????? 0.0
redo writes????????????????????????????????????? 677??????????? 0.3????????? 3.6
rollback changes - undo records a???????????????? 10??????????? 0.0????????? 0.1
rollbacks only - consistent read?????????????????? 8??????????? 0.0????????? 0.0
rows fetched via callback???????????????????? 14,999??????????? 6.5???????? 78.9
session connect time?????????????????????????? 2,312??????????? 1.0???????? 12.2
session cursor cache hits????????????????????? 3,352??????????? 1.5???????? 17.6
session logical reads??????????????????????? 118,636?????????? 51.7??????? 624.4
session pga memory??????????????????????? 34,734,200?????? 15,141.3??? 182,811.6
session pga memory max??????????????????? 32,964,728?????? 14,370.0??? 173,498.6
session uga memory?????????????????? 158,915,954,392?? 69,274,609.6 ############
session uga memory max??????????????????? 44,277,484?????? 19,301.4??? 233,039.4
shared hash latch upgrades - no w???????????? 33,011?????????? 14.4??????? 173.7
sorts (memory)???????????????????????????????? 1,823??????????? 0.8????????? 9.6
sorts (rows)????????????????????????????????? 93,505?????????? 40.8??????? 492.1
sql area purged??????????????????????????????????? 2??????????? 0.0????????? 0.0
switch current to new buffer?????????????????????? 2??????????? 0.0????????? 0.0
table fetch by rowid????????????????????????? 23,047?????????? 10.1??????? 121.3
table fetch continued row???????????????????????? 61??????????? 0.0????????? 0.3
table scan blocks gotten?????????????????????? 9,066??????????? 4.0???????? 47.7
table scan rows gotten?????????????????????? 400,817????????? 174.7????? 2,109.6
table scans (short tables)?????????????????????? 760??????????? 0.3????????? 4.0
total number of times SMON posted????????????????? 8??????????? 0.0????????? 0.0
transaction rollbacks????????????????????????????? 5??????????? 0.0????????? 0.0
undo change vector size??????????????????? 1,329,952????????? 579.8????? 6,999.8
user I/O wait time??????????????????????????????? 34??????????? 0.0????????? 0.2
user calls???????????????????????????????????? 1,165??????????? 0.5????????? 6.1
user commits???????????????????????????????????? 190??????????? 0.1????????? 1.0
workarea executions - optimal??????????????????? 884??????????? 0.4????????? 4.7
write clones created in backgroun????????????????? 0??????????? 0.0????????? 0.0
????????? -------------------------------------------------------------
?
?
11、IO情況統(tǒng)計(jì)
?
Tablespace IO Stats? DB/Inst: DODO/dodo? Snaps: 1-4
->ordered by IOs (Reads + Writes) desc
?
File IO Stats? DB/Inst: DODO/dodo? Snaps: 1-4
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
?
File Read Histogram Stats? DB/Inst: DODO/dodo? Snaps: 1-4
->Number of single block reads in each time range
->ordered by Tablespace, File
?
?
12、緩沖區(qū)
?
Buffer Pool Statistics? DB/Inst: DODO/dodo? Snaps: 1-4
-> Standard block size Pools? D: default,? K: keep,? R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers.? Units of K, M, G are divided by 1000
?
Instance Recovery Stats? DB/Inst: DODO/dodo? Snaps: 1-4
-> B: Begin snapshot,? E: End snapshot
?
Buffer Pool Advisory? DB/Inst: DODO/dodo? End Snap: 4
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
?
Buffer wait Statistics? DB/Inst: DODO/dodo? Snaps: 1-4
-> ordered by wait time desc, waits desc
?
?
13、PGA
?
PGA Aggr Target Stats? DB/Inst: DODO/dodo? Snaps: 1-4
-> B: Begin snap?? E: End snap (rows identified with B or E contain data
?? which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used??? - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem??? - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem?? - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem??? - percentage of workarea memory under manual control
?
?
14、進(jìn)程該要
?
Process Memory Summary Stats? DB/Inst: DODO/dodo? Snaps: 1-4
-> B: Begin snap?? E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
?? Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs:? For Begin/End snapshot lines, it is the number of
?? processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
?
?
15、回滾段
?
Undo Segment Summary? DB/Inst: DODO/dodo? Snaps: 1-4
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count,? OOS - Out Of Space count
-> Undo segment block stats:
?? uS - unexpired Stolen,?? uR - unexpired Released,?? uU - unexpired reUsed
?? eS - expired?? Stolen,?? eR - expired?? Released,?? eU - expired?? reUsed
?
?
16、鎖
?
...
?
-The End-