Statspack學(xué)習(xí)(四).Report分析
?
??? Statspack的Report文檔其實是將Oracle系統(tǒng)的各項系統(tǒng)個指標(biāo)分項統(tǒng)計成各個報表，之后放入到Report文檔中，所以可以根據(jù)自己的需要截取其中的一部分進行分析。里面的數(shù)據(jù)非常之多，要一次分析完全是不太現(xiàn)實的。
?
??? 下面截取一下常見的學(xué)習(xí)一下：
?
?
1、報表頭信息
?
??? 報表頭顯示了數(shù)據(jù)庫實例的相關(guān)信息，包括數(shù)據(jù)庫名稱、ID、版本號、主機等信息；以及起止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、負載間檔
?
??? 該部分提供每秒和每個事物的統(tǒng)計信息，是監(jiān)控系統(tǒng)吞吐量和負載變化的重要部分。
?
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
?
??? 說明：
??? ⑴ Redo size：每秒產(chǎn)生的日志大小(單位字節(jié))，可標(biāo)志數(shù)據(jù)變更頻率, 數(shù)據(jù)庫任務(wù)的繁重與否
??? ⑵ Logical reads：平?jīng)Q每秒產(chǎn)生的邏輯讀，單位是block
??? ⑶ Block changes：每秒block變化數(shù)量，數(shù)據(jù)庫事物帶來改變的塊數(shù)量
??? ⑷ Physical reads：平均每秒數(shù)據(jù)庫從磁盤讀取的block數(shù)
??? ⑸ Physical writes：平均每秒數(shù)據(jù)庫寫磁盤的block數(shù)
??? ⑹ User calls：每秒用戶call次數(shù)
??? ⑺ Parses: 每秒解析次數(shù)，近似反應(yīng)每秒語句的執(zhí)行次數(shù), 軟解析每秒超過300次意味著你的“應(yīng)用程序”效率不高，調(diào)整session_cursor_cache
??? ⑻ Hard parses：每秒產(chǎn)生的硬解析次數(shù), 每秒超過100次，就可能說明你綁定使用的不好
??? ⑼ Sorts：每秒產(chǎn)生的排序次數(shù)
??? ⑽ Logons：每秒的登陸次數(shù)
??? ⑾ Executes：每秒的執(zhí)行次數(shù)
??? ⑿ Transactions：每秒產(chǎn)生的事務(wù)數(shù)，反映數(shù)據(jù)庫任務(wù)繁重與否
??? ⒀ Recursive Call %: 如果有很多PLSQL，那么他就會比較高
??? ⒁ Rollback per transaction %：看回滾率是不是很高，因為回滾很耗資源
?
??? 注：回滾率=Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% ，
如果回滾率過高，可能說明你的數(shù)據(jù)庫經(jīng)歷了太多的無效操作，
過多的回滾可能還會帶來Undo Block的競爭。
?
3、實例命中率
?
??? 該部分可以提前找出ORACLE潛在將要發(fā)生的性能問題，很重要
?
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
?
??? 說明:
??? ⑴ Buffer Nowait %：在緩沖區(qū)中獲取Buffer的未等待比率, Buffer Nowait<99%說明，有可能是有熱塊(查找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，但是大量的非選擇的索引也會造成該值很高（大量的db file sequential read）
??? ⑷ In-memory Sort %：在內(nèi)存中的排序率
??? ⑸ Library Hit %：主要代表sql在共享區(qū)的命中率，通常在95%以上，否則需要要考慮加大共享池，綁定變量，修改cursor_sharing等參數(shù)。
??? ⑹ Soft Parse %：近似看作sql在共享區(qū)的命中率，小于95%需要考慮到綁定，如果低于80%，那么就可能sql基本沒有被重用
??? ⑺ Execute to Parse %：sql語句解析后被重復(fù)執(zhí)行的次數(shù)，如果過低，可以考慮設(shè)置session_cached_cursors參數(shù), 公式為100 * (1 - Parses/Executions) = Execute to Parse 所以如果系統(tǒng)Parses > Executions,就可能出現(xiàn)該比率小于0的情況, 該值<0通常說明shared pool設(shè)置或效率存在問題造成反復(fù)解析，reparse可能較嚴重,或者可是同snapshot有關(guān)如果該值為負值或者極低,通常說明數(shù)據(jù)庫性能存在問題。
??? ⑻ Latch Hit %: 要確保>99%，否則存在嚴重的性能問題，比如綁定等會影響該參數(shù)
??? ⑼
Parse CPU to Parse Elapsd %：解析實際運行事件/(解析實際運行時間+解析中等待資源時間)越高越好
??? ⑽ % Non-Parse CPU：查詢實際運行時間/(查詢實際運行時間+sql解析時間)，太低表示解析消耗時間過多。100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd %
?
??? 注：
??? 如果一個經(jīng)常訪問的列上的索引被刪除，可能會造成Buffer Hit顯著的下降
??? 如果增加了索引，但是他影響了Oracle正確的選擇表連接時的驅(qū)動順序，那么可能會導(dǎo)致Buffer Hit顯著增高
??? 如果你的命中率變化幅度很大，說明你要改變SQL模式
??? Shared Pool相關(guān)統(tǒng)計數(shù)據(jù)
??? ⑴ Memory Usage %：共享池內(nèi)存使用率，應(yīng)該穩(wěn)定在70%-90%間，太小浪費內(nèi)存，太大則內(nèi)存不足。
??? ⑵ % SQL with executions>1：執(zhí)行次數(shù)大于1的sql比率，若太小可能是沒有使用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運行狀況的重要依據(jù)及指示，主要有空閑等待事件和非空閑等待事件。
??? TIMED_STATISTICS = TRUE 等待事件按等待的時間排序
??? TIMED_STATISTICS = FALSE 等待事件按等待的數(shù)量排序
??? Statspack中必須在Session上設(shè)置TIMED_STATISTICS = TRUE
??? 空閑等待事件是Oracle正等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫時候，不用過多注意這部分事件，非空閑等待事件專門針對Oracle的活動，指數(shù)據(jù)庫任務(wù)或應(yīng)用程序運行過程中發(fā)生的等待，這些等待事件是我們在調(diào)整數(shù)據(jù)庫應(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
????????? -------------------------------------------------------------
??? 比較影響性能常見等待事件有：
??? ① db file scattered read
??? 該事件通常與全表掃描有關(guān)。因為全表掃描是被放入內(nèi)存中進行的，通常情況下它不可能被放入連續(xù)的緩沖區(qū)中，所以就散布在緩沖區(qū)的緩存中。該指數(shù)的數(shù)量過大說明缺少索引或者限制了索引的使用(也可以調(diào)整optimizer_index_cost_adj) 。這種情況也可能是正常的，因為執(zhí)行全表掃描可能比索引掃描效率更高。當(dāng)系統(tǒng)存在這些等待時，需要通過檢查來確定全表掃描是否必需的來調(diào)整。如果經(jīng)常必須進行全表掃描，而且表比較小, 把該表存人keep池。如果是大表經(jīng)常進行全表掃描,那么應(yīng)該是OLAP系統(tǒng)，而不是OLTP的.
??? ② db file sequential read
??? 該事件說明在單個數(shù)據(jù)塊上大量等待，該值過高通常是由于表間連接順序很糟糕，或者使用了非選擇性索引。通過將這種等待與statspack報表中已知其它問題聯(lián)系起來(如效率不高的sql),通過檢查確保索引掃描是必須的，并確保多表連接的連接順序來調(diào)整, DB_CACHE_SIZE可以決定該事件出現(xiàn)的頻率
??? ③ buffer busy wait
??? 當(dāng)緩沖區(qū)以一種非共享方式或者如正在被讀入到緩沖時，就會出現(xiàn)該等待。該值不應(yīng)該大于1%，確認是不是由于熱點塊造成(如果是可以用反轉(zhuǎn)索引,或者用更小塊大小)
??? ④ latch free
??? 常跟應(yīng)用沒有很好的應(yīng)用綁定有關(guān)。閂鎖是底層的隊列機制(更加準(zhǔn)確的名稱應(yīng)該是互斥機制)，用于保護系統(tǒng)全局區(qū)(SGA)共享內(nèi)存結(jié)構(gòu)閂鎖用于防止對內(nèi)存結(jié)構(gòu)的并行訪問。如果閂鎖不可用，就會記錄一次閂鎖丟失。絕大多數(shù)得閂鎖問題都與使用綁定變量失敗(庫緩存閂鎖)、生成重作問題(重執(zhí)行分配閂鎖)、緩存的爭用問題(緩存LRU鏈) 以及緩存的熱數(shù)據(jù)寬塊(緩存鏈)有關(guān)。當(dāng)閂鎖丟失率高于0.5%時，需要調(diào)整這個問題。
??? ⑤ log buffer space
??? 日志緩沖區(qū)寫的速度快于LGWR寫REDOFILE的速度,可以增大日志文件大小，增加日志緩沖區(qū)的大小，或者使用更快的磁盤來寫數(shù)據(jù)。
??? ⑥ logfile switch
??? 通常是因為歸檔速度不夠快，需要增大重做日志
??? ⑦ log file sync
??? 當(dāng)一個用戶提交或回滾數(shù)據(jù)時，LGWR將會話得重做操作從日志緩沖區(qū)填充到日志文件中，用戶的進程必須等待這個填充工作完成。在每次提交時都出現(xiàn)，如果這個等待事件影響到數(shù)據(jù)庫性能，那么就需要修改應(yīng)用程序的提交頻率, 為減少這個等待事件，須一次提交更多記錄，或者將重做日志REDO LOG 文件訪在不同的物理磁盤上。
?
??? 更加具體的說明可以參見eygle大師的《Statspack-v3.0》
?
?
5、實例的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ù)庫用戶程序發(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ù)庫后臺進程發(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個緩沖區(qū)讀操作和首要的25個磁盤讀操作做的查詢，將可對系統(tǒng)性能產(chǎn)生5%到5000%的增益。
?
??? 內(nèi)容太多不再截取...
?
?
10、實例活動
?
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)計
?
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、進程該要
?
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、鎖
?
...
?