1、AWR報告詳細(xì)分析 AWR 是 Oracle 10g 版本 推出的新特性， 全稱叫AutomaticWorkloadRepository-自動負(fù)載信息庫, AWR 是通過對比兩次快，照(snapshot)收集到的統(tǒng)計信息，來生成報表數(shù)據(jù)，生成的報表包括多個部分。 WORKLOAD REPOSITORY report for DB NameDB IdInstanceInst numReleaseRACHostICCIICCI1110.2.0.3.0YESHPGICCI1Snap IdSnap TimeSessionsCursors/SessionBegin Snap:267825-Dec-08

2、14:04:50241.5End Snap:268025-Dec-08 15:23:37261.5Elapsed:78.79 (mins)DB Time:11.05 (mins)DB Time不包括Oracle后臺進(jìn)程消耗的時間。如果DB Time遠(yuǎn)遠(yuǎn)小于Elapsed時間，說明數(shù)據(jù)庫比較空閑。db time= cpu time + wait time（不包含空閑等待） （非后臺進(jìn)程）說白了就是db time就是記錄的服務(wù)器花在數(shù)據(jù)庫運(yùn)算(非后臺進(jìn)程)和等待(非空閑等待)上的時間DB time = cpu time + all of nonidle wait event time在79分鐘里（

3、其間收集了3次快照數(shù)據(jù)），數(shù)據(jù)庫耗時11分鐘，RDA數(shù)據(jù)中顯示系統(tǒng)有8個邏輯CPU（4個物理CPU），平均每個CPU耗時1.4分鐘，CPU利用率只有大約2%（1.4/79）。說明系統(tǒng)壓力非常小。列出下面這兩個來做解釋：Report A:Snap Id Snap Time Sessions Curs/Sess- - - -Begin Snap: 4610 24-Jul-08 22:00:54 68 19.1End Snap: 4612 24-Jul-08 23:00:25 17 1.7Elapsed: 59.51 (mins)DB Time: 466.37 (mins)Report B:Snap

4、 Id Snap Time Sessions Curs/Sess- - - -Begin Snap: 3098 13-Nov-07 21:00:37 39 13.6End Snap: 3102 13-Nov-07 22:00:15 40 16.4Elapsed: 59.63 (mins)DB Time: 19.49 (mins)服務(wù)器是AIX的系統(tǒng)，4個雙核cpu,共8個核:/sbin bindprocessor -qThe available processors are: 0 1 2 3 4 5 6 7先說Report A,在snapshot間隔中，總共約60分鐘，cpu就共有60*8=4

5、80分鐘，DB time為466.37分鐘，則：cpu花費(fèi)了466.37分鐘在處理Oralce非空閑等待和運(yùn)算上(比方邏輯讀)也就是說cpu有 466.37/480*100% 花費(fèi)在處理Oracle的操作上，這還不包括后臺進(jìn)程看Report B，總共約60分鐘，cpu有 19.49/480*100% 花費(fèi)在處理Oracle的操作上很顯然，2中服務(wù)器的平均負(fù)載很低。從awr report的Elapsed time和DB Time就能大概了解db的負(fù)載?？墒菍τ谂肯到y(tǒng)，數(shù)據(jù)庫的工作負(fù)載總是集中在一段時間內(nèi)。如果快照周期不在這一段時間內(nèi)，或者快照周期跨度太長而包含了大量的數(shù)據(jù)庫空閑時間，所得出的分

6、析結(jié)果是沒有意義的。這也說明選擇分析時間段很關(guān)鍵，要選擇能夠代表性能問題的時間段。Report SummaryCache Sizes BeginEndBuffer Cache:3,344M3,344MStd Block Size:8KShared Pool Size:704M704MLog Buffer:14,352K顯示SGA中每個區(qū)域的大小（在AMM改變它們之后），可用來與初始參數(shù)值比較。shared pool主要包括library cache和dictionary cache。library cache用來存儲最近解析（或編譯）后SQL、PL/SQL和Java classes等。libr

7、ary cache用來存儲最近引用的數(shù)據(jù)字典。發(fā)生在library cache或dictionary cache的cache miss代價要比發(fā)生在buffer cache的代價高得多。因此shared pool的設(shè)置要確保最近使用的數(shù)據(jù)都能被cache。Load ProfilePer SecondPer TransactionRedo size:918,805.72775,912.72Logical reads:3,521.772,974.06Block changes:1,817.951,535.22Physical reads:68.2657.64Physical writes:362.

8、59306.20User calls:326.69275.88Parses:38.6632.65Hard parses:0.030.03Sorts:0.610.51Logons:0.010.01Executes:354.34299.23Transactions:1.18% Blocks changed per Read:51.62Recursive Call %:51.72Rollback per transaction %:85.49Rows per Sort:#顯示數(shù)據(jù)庫負(fù)載概況，將之與基線數(shù)據(jù)比較才具有更多的意義，如果每秒或每事務(wù)的負(fù)載變化不大，說明應(yīng)用運(yùn)行比較穩(wěn)定。單個的報告數(shù)據(jù)只說明

9、應(yīng)用的負(fù)載情況，絕大多數(shù)據(jù)并沒有一個所謂“正確”的值，然而Logons大于每秒12個、Hard parses大于每秒100、全部parses超過每秒300表明可能有爭用問題。Redo size：每秒產(chǎn)生的日志大小(單位字節(jié))，可標(biāo)志數(shù)據(jù)變更頻率, 數(shù)據(jù)庫任務(wù)的繁重與否。Logical reads：每秒/每事務(wù)邏輯讀的塊數(shù).平?jīng)Q每秒產(chǎn)生的邏輯讀的block數(shù)。Logical Reads= Consistent Gets + DB Block Gets Block changes：每秒/每事務(wù)修改的塊數(shù)Physical reads：每秒/每事務(wù)物理讀的塊數(shù)Physical writes：每秒/每事

10、務(wù)物理寫的塊數(shù)User calls：每秒/每事務(wù)用戶call次數(shù)Parses：SQL解析的次數(shù).每秒解析次數(shù)，包括fast parse，soft parse和hard parse三種數(shù)量的綜合。 軟解析每秒超過300次意味著你的應(yīng)用程序效率不高，調(diào)整session_cursor_cache。在這里，fast parse指的是直接在PGA中命中的情況（設(shè)置了session_cached_cursors=n）；soft parse是指在shared pool中命中的情形；hard parse則是指都不命中的情況。Hard parses：其中硬解析的次數(shù)，硬解析太多，說明SQL重用率不高。每秒產(chǎn)生的

11、硬解析次數(shù), 每秒超過100次，就可能說明你綁定使用的不好，也可能是共享池設(shè)置不合理。這時候可以啟用參數(shù)cursor_sharing=similar|force，該參數(shù)默認(rèn)值為exact。但該參數(shù)設(shè)置為similar時，存在bug，可能導(dǎo)致執(zhí)行計劃的不優(yōu)。Sorts：每秒/每事務(wù)的排序次數(shù)Logons：每秒/每事務(wù)登錄的次數(shù)Executes：每秒/每事務(wù)SQL執(zhí)行次數(shù)Transactions：每秒事務(wù)數(shù).每秒產(chǎn)生的事務(wù)數(shù)，反映數(shù)據(jù)庫任務(wù)繁重與否。Blocks changed per Read：表示邏輯讀用于修改數(shù)據(jù)塊的比例.在每一次邏輯讀中更改的塊的百分比。Recursive Call：遞歸調(diào)

12、用占所有操作的比率.遞歸調(diào)用的百分比，如果有很多PL/SQL，那么這個值就會比較高。Rollback per transaction：每事務(wù)的回滾率.看回滾率是不是很高，因?yàn)榛貪L很耗資源 ,如果回滾率過高,可能說明你的數(shù)據(jù)庫經(jīng)歷了太多的無效操作 ,過多的回滾可能還會帶來Undo Block的競爭 該參數(shù)計算公式如下: Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% 。Rows per Sort：每次排序的行數(shù)注:Oracle的硬解析和軟解析 提到軟解析(soft prase)和硬解析(hard prase)，就不

13、能不說一下Oracle對sql的處理過程。當(dāng)你發(fā)出一條sql語句交付Oracle，在執(zhí)行和獲取結(jié)果前，Oracle對此sql將進(jìn)行幾個步驟的處理過程：1、語法檢查(syntax check)檢查此sql的拼寫是否語法。2、語義檢查(semantic check)諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應(yīng)的權(quán)限。3、對sql語句進(jìn)行解析(prase)利用內(nèi)部算法對sql進(jìn)行解析，生成解析樹(parse tree)及執(zhí)行計劃(execution plan)。4、執(zhí)行sql，返回結(jié)果(execute and return)其中，軟、硬解析就發(fā)生在第三個過程里。Oracle利用內(nèi)部的h

14、ash算法來取得該sql的hash值，然后在library cache里查找是否存在該hash值；假設(shè)存在，則將此sql與cache中的進(jìn)行比較；假設(shè)“相同”，就將利用已有的解析樹與執(zhí)行計劃，而省略了優(yōu)化器的相關(guān)工作。這也就是軟解析的過程。誠然，如果上面的2個假設(shè)中任有一個不成立，那么優(yōu)化器都將進(jìn)行創(chuàng)建解析樹、生成執(zhí)行計劃的動作。這個過程就叫硬解析。創(chuàng)建解析樹、生成執(zhí)行計劃對于sql的執(zhí)行來說是開銷昂貴的動作，所以，應(yīng)當(dāng)極力避免硬解析，盡量使用軟解析。Instance Efficiency Percentages (Target 100%) Buffer Nowait %:100.00Redo

15、 NoWait %:100.00Buffer Hit %:98.72In-memory Sort %:99.86Library Hit %:99.97Soft Parse %:99.92Execute to Parse %:89.09Latch Hit %:99.99Parse CPU to Parse Elapsd %:7.99% Non-Parse CPU:99.95本節(jié)包含了Oracle關(guān)鍵指標(biāo)的內(nèi)存命中率及其它數(shù)據(jù)庫實(shí)例操作的效率。其中Buffer Hit Ratio 也稱Cache Hit Ratio，Library Hit ratio也稱Library Cache Hit rati

16、o。同Load Profile一節(jié)相同，這一節(jié)也沒有所謂“正確”的值，而只能根據(jù)應(yīng)用的特點(diǎn)判斷是否合適。在一個使用直接讀執(zhí)行大型并行查詢的DSS環(huán)境，20%的Buffer Hit Ratio是可以接受的，而這個值對于一個OLTP系統(tǒng)是完全不能接受的。根據(jù)Oracle的經(jīng)驗(yàn)，對于OLTPOLAP:聯(lián)機(jī)分析處理OLTP:聯(lián)機(jī)事務(wù)處理OLAP是主要應(yīng)用數(shù)據(jù)倉庫系統(tǒng)OLTP是一般的項(xiàng)目開發(fā)用到的基本的、日常的事務(wù)處理；比如數(shù)據(jù)庫記錄的增、刪、改、查。系統(tǒng)，Buffer Hit Ratio理想應(yīng)該在90%以上。Buffer Nowait表示在內(nèi)存獲得數(shù)據(jù)的未等待比例。在緩沖區(qū)中獲取Buffer的未等待比

17、率。Buffer Nowait的這個值一般需要大于99%。否則可能存在爭用，可以在后面的等待事件中進(jìn)一步確認(rèn)。buffer hit表示進(jìn)程從內(nèi)存中找到數(shù)據(jù)塊的比率，監(jiān)視這個值是否發(fā)生重大變化比這個值本身更重要。對于一般的OLTP系統(tǒng)，如果此值低于80%，應(yīng)該給數(shù)據(jù)庫分配更多的內(nèi)存。數(shù)據(jù)塊在數(shù)據(jù)緩沖區(qū)中的命中率，通常應(yīng)在95%以上。否則，小于95%，需要調(diào)整重要的參數(shù)，小于90%可能是要加db_cache_size。一個高的命中率，不一定代表這個系統(tǒng)的性能是最優(yōu)的，比如大量的非選擇性的索引被頻繁訪問，就會造成命中率很高的假相（大量的db file sequential read），但是一個比較低

18、的命中率，一般就會對這個系統(tǒng)的性能產(chǎn)生影響，需要調(diào)整。命中率的突變，往往是一個不好的信息。如果命中率突然增大，可以檢查top buffer get SQL，查看導(dǎo)致大量邏輯讀的語句和索引，如果命中率突然減小，可以檢查top physical reads SQL，檢查產(chǎn)生大量物理讀的語句，主要是那些沒有使用索引或者索引被刪除的。Redo NoWait表示在LOG緩沖區(qū)獲得BUFFER的未等待比例。如果太低（可參考90%閥值），考慮增加LOG BUFFER。當(dāng)redo buffer達(dá)到1M時，就需要寫到redo log文件，所以一般當(dāng)redo buffer設(shè)置超過1M，不太可能存在等待buffer

19、空間分配的情況。當(dāng)前，一般設(shè)置為2M的redo buffer，對于內(nèi)存總量來說，應(yīng)該不是一個太大的值。library hit表示Oracle從Library Cache中檢索到一個解析過的SQL或PL/SQL語句的比率，當(dāng)應(yīng)用程序調(diào)用SQL或存儲過程時，Oracle檢查Library Cache確定是否存在解析過的版本，如果存在，Oracle立即執(zhí)行語句；如果不存在，Oracle解析此語句，并在Library Cache中為它分配共享SQL區(qū)。低的library hit ratio會導(dǎo)致過多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要調(diào)大shar

20、ed pool區(qū)。STATEMENT在共享區(qū)的命中率，通常應(yīng)該保持在95%以上，否則需要要考慮：加大共享池；使用綁定變量；修改cursor_sharing等參數(shù)。Latch Hit：Latch是一種保護(hù)內(nèi)存結(jié)構(gòu)的鎖，可以認(rèn)為是SERVER進(jìn)程獲取訪問內(nèi)存數(shù)據(jù)結(jié)構(gòu)的許可。要確保Latch Hit99%，否則意味著Shared Pool latch爭用，可能由于未共享的SQL，或者Library Cache太小，可使用綁定變更或調(diào)大Shared Pool解決。要確保99%，否則存在嚴(yán)重的性能問題。當(dāng)該值出現(xiàn)問題的時候，我們可以借助后面的等待時間和latch分析來查找解決問題。Parse CPU t

21、o Parse Elapsd：解析實(shí)際運(yùn)行時間/(解析實(shí)際運(yùn)行時間+解析中等待資源時間)，越高越好。計算公式為：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析實(shí)際運(yùn)行時間/(解析實(shí)際運(yùn)行時間+解析中等待資源時間)。如果該比率為100%，意味著CPU等待時間為0，沒有任何等待。Non-Parse CPU ：SQL實(shí)際運(yùn)行時間/(SQL實(shí)際運(yùn)行時間+SQL解析時間)，太低表示解析消耗時間過多。計算公式為：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU

22、),2)。如果這個值比較小，表示解析消耗的CPU時間過多。與PARSE_CPU相比，如果TOT_CPU很高，這個比值將接近100%，這是很好的，說明計算機(jī)執(zhí)行的大部分工作是執(zhí)行查詢的工作，而不是分析查詢的工作。Execute to Parse：是語句執(zhí)行與分析的比例，如果要SQL重用率高，則這個比例會很高。該值越高表示一次解析后被重復(fù)執(zhí)行的次數(shù)越多。計算公式為：Execute to Parse =100 * (1 - Parses/Executions)。本例中，差不多每execution 5次需要一次parse。所以如果系統(tǒng)Parses Executions，就可能出現(xiàn)該比率小于0的情況。該

23、值0通常說明shared pool設(shè)置或者語句效率存在問題，造成反復(fù)解析，reparse可能較嚴(yán)重,或者是可能同snapshot有關(guān)，通常說明數(shù)據(jù)庫性能存在問題。In-memory Sort：在內(nèi)存中排序的比率，如果過低說明有大量的排序在臨時表空間中進(jìn)行。考慮調(diào)大PGA(10g)。如果低于95%，可以通過適當(dāng)調(diào)大初始化參數(shù)PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE來解決，注意這兩個參數(shù)設(shè)置作用的范圍時不同的，SORT_AREA_SIZE是針對每個session設(shè)置的，PGA_AGGREGATE_TARGET則時針對所有的sesion的。Soft Parse：軟解析

24、的百分比（softs/softs+hards），近似當(dāng)作sql在共享區(qū)的命中率，太低則需要調(diào)整應(yīng)用使用綁定變量。sql在共享區(qū)的命中率，小于1:88.4879.81% Memory for SQL w/exec1:79.9973.52Memory Usage %：對于一個已經(jīng)運(yùn)行一段時間的數(shù)據(jù)庫來說，共享池內(nèi)存使用率，應(yīng)該穩(wěn)定在75%-90%間，如果太小，說明Shared Pool有浪費(fèi)，而如果高于90，說明共享池中有爭用，內(nèi)存不足。這個數(shù)字應(yīng)該長時間穩(wěn)定在75%90%。如果這個百分比太低，表明共享池設(shè)置過大，帶來額外的管理上的負(fù)擔(dān)，從而在某些條件下會導(dǎo)致性能的下降。如果這個百分率太高，會使共

25、享池外部的組件老化，如果SQL語句被再次執(zhí)行，這將使得SQL語句被硬解析。在一個大小合適的系統(tǒng)中，共享池的使用率將處于75%到略低于90%的范圍內(nèi).SQL with executions1：執(zhí)行次數(shù)大于1的sql比率，如果此值太小，說明需要在應(yīng)用中更多使用綁定變量，避免過多SQL解析。在一個趨向于循環(huán)運(yùn)行的系統(tǒng)中，必須認(rèn)真考慮這個數(shù)字。在這個循環(huán)系統(tǒng)中，在一天中相對于另一部分時間的部分時間里執(zhí)行了一組不同的SQL語句。在共享池中，在觀察期間將有一組未被執(zhí)行過的SQL語句，這僅僅是因?yàn)橐獔?zhí)行它們的語句在觀察期間沒有運(yùn)行。只有系統(tǒng)連續(xù)運(yùn)行相同的SQL語句組，這個數(shù)字才會接近100%。Memory

26、for SQL w/exec1：執(zhí)行次數(shù)大于1的SQL消耗內(nèi)存的占比。這是與不頻繁使用的SQL語句相比，頻繁使用的SQL語句消耗內(nèi)存多少的一個度量。這個數(shù)字將在總體上與% SQL with executions1非常接近，除非有某些查詢?nèi)蝿?wù)消耗的內(nèi)存沒有規(guī)律。在穩(wěn)定狀態(tài)下，總體上會看見隨著時間的推移大約有75%85%的共享池被使用。如果Statspack報表的時間窗口足夠大到覆蓋所有的周期，執(zhí)行次數(shù)大于一次的SQL語句的百分率應(yīng)該接近于100%。這是一個受觀察之間持續(xù)時間影響的統(tǒng)計數(shù)字?？梢云谕S觀察之間的時間長度增大而增大。小結(jié)：通過ORACLE的實(shí)例有效性統(tǒng)計數(shù)據(jù)，我們可以獲得大概的一個

27、整體印象，然而我們并不能由此來確定數(shù)據(jù)運(yùn)行的性能。當(dāng)前性能問題的確定，我們主要還是依靠下面的等待事件來確認(rèn)。我們可以這樣理解兩部分的內(nèi)容，hit統(tǒng)計幫助我們發(fā)現(xiàn)和預(yù)測一些系統(tǒng)將要產(chǎn)生的性能問題，由此我們可以做到未雨綢繆。而wait事件，就是表明當(dāng)前數(shù)據(jù)庫已經(jīng)出現(xiàn)了性能問題需要解決，所以是亡羊補(bǔ)牢的性質(zhì)。Top 5 Timed Events EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait ClassCPU time51577.6SQL*Net more data from client27,3196429.7Networklog file p

28、arallel write5,4974797.1System I/Odb file sequential read7,9003545.3User I/Odb file parallel write4,8063475.1System I/O這是報告概要的最后一節(jié)，顯示了系統(tǒng)中最嚴(yán)重的5個等待，按所占等待時間的比例倒序列示。當(dāng)我們調(diào)優(yōu)時，總希望觀察到最顯著的效果，因此應(yīng)當(dāng)從這里入手確定我們下一步做什么。例如如果buffer busy wait是較嚴(yán)重的等待事件，我們應(yīng)當(dāng)繼續(xù)研究報告中Buffer Wait和File/Tablespace IO區(qū)的內(nèi)容，識別哪些文件導(dǎo)致了問題。如果最嚴(yán)重的等待事件是

29、I/O事件，我們應(yīng)當(dāng)研究按物理讀排序的SQL語句區(qū)以識別哪些語句在執(zhí)行大量I/O，并研究Tablespace和I/O區(qū)觀察較慢響應(yīng)時間的文件。如果有較高的LATCH等待，就需要察看詳細(xì)的LATCH統(tǒng)計識別哪些LATCH產(chǎn)生的問題。一個性能良好的系統(tǒng)，cpu time應(yīng)該在top 5的前面，否則說明你的系統(tǒng)大部分時間都用在等待上。在這里，log file parallel write是相對比較多的等待，占用了7%的CPU時間。通常，在沒有問題的數(shù)據(jù)庫中，CPU time總是列在第一個。更多的等待事件，參見本報告 的Wait Events一節(jié)。RAC StatisticsBeginEndNumbe

30、r of Instances:22Global Cache Load Profile Per SecondPer TransactionGlobal Cache blocks received:4.163.51Global Cache blocks served:5.975.04GCS/GES messages received:408.47344.95GCS/GES messages sent:258.03217.90DBWR Fusion writes:0.050.05Estd Interconnect traffic (KB)211.16Global Cache Efficiency P

31、ercentages (Target local+remote 100%) Buffer access - local cache %:98.60Buffer access - remote cache %:0.12Buffer access - disk %:1.28Global Cache and Enqueue Services - Workload Characteristics Avg global enqueue get time (ms):0.1Avg global cache cr block receive time (ms):1.1Avg global cache curr

32、ent block receive time (ms):0.8Avg global cache cr block build time (ms):0.0Avg global cache cr block send time (ms):0.0Global cache log flushes for cr blocks served %:3.5Avg global cache cr block flush time (ms):3.9Avg global cache current block pin time (ms):0.0Avg global cache current block send

33、time (ms):0.0Global cache log flushes for current blocks served %:0.4Avg global cache current block flush time (ms):3.0Global Cache and Enqueue Services - Messaging Statistics Avg message sent queue time (ms):0.0Avg message sent queue time on ksxp (ms):0.3Avg message received queue time (ms):0.5Avg

34、GCS message process time (ms):0.0Avg GES message process time (ms):0.0% of direct sent messages:14.40% of indirect sent messages:77.04% of flow controlled messages:8.56Main Report Wait Events Statistics SQL Statistics Instance Activity Statistics IO Stats Buffer Pool Statistics Advisory Statistics W

35、ait Statistics Undo Statistics Latch Statistics Segment Statistics Dictionary Cache Statistics Library Cache Statistics Memory Statistics Streams Statistics Resource Limit Statistics init.ora Parameters Wait Events Statistics Time Model Statistics Wait Class Wait Events Background Wait Events Operat

36、ing System Statistics Service Statistics Service Wait Class Stats Back to Top/* oracle等待事件是衡量oracle運(yùn)行狀況的重要依據(jù)及指示，等待事件分為兩類：空閑等待事件和非空閑等待事件, TIMED_STATISTICS = TRUE 那么等待事件按等待的時間排序，= FALSE那么事件按等待的數(shù)量排序。運(yùn)行statspack期間必須session上設(shè)置TIMED_STATISTICS = TRUE，否則統(tǒng)計的數(shù)據(jù)將失真?？臻e等待事件是oracle正等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫時候，不用過多注意這部分事件

37、，非空閑等待事件專門針對oracle的活動，指數(shù)據(jù)庫任務(wù)或應(yīng)用程序運(yùn)行過程中發(fā)生的等待，這些等待事件是我們在調(diào)整數(shù)據(jù)庫應(yīng)該關(guān)注的。 對于常見的等待事件，說明如下：1) db file scattered read 文件分散讀取該事件通常與全表掃描或者fast full index scan有關(guān)。因?yàn)槿頀呙枋潜环湃雰?nèi)存中進(jìn)行的進(jìn)行的，通常情況下基于性能的考慮，有時候也可能是分配不到足夠長的連續(xù)內(nèi)存空間，所以會將數(shù)據(jù)塊分散(scattered)讀入Buffer Cache中。該等待過大可能是缺少索引或者沒有合適的索引(可以調(diào)整optimizer_index_cost_adj) 。這種情況也可能是

38、正常的，因?yàn)閳?zhí)行全表掃描可能比索引掃描效率更高。當(dāng)系統(tǒng)存在這些等待時，需要通過檢查來確定全表掃描是否必需的來調(diào)整。因?yàn)槿頀呙璞恢糜贚RU(Least Recently Used，最近最少適用)列表的冷端(cold end)，對于頻繁訪問的較小的數(shù)據(jù)表，可以選擇把他們Cache 到內(nèi)存中，以避免反復(fù)讀取。當(dāng)這個等待事件比較顯著時，可以結(jié)合v$session_longops 動態(tài)性能視圖來進(jìn)行診斷，該視圖中記錄了長時間(運(yùn)行時間超過6 秒的)運(yùn)行的事物，可能很多是全表掃描操作(不管怎樣，這部分信息都是值得我們注意的)。關(guān)于參數(shù)OPTIMIZER_INDEX_COST_ADJn：該參數(shù)是一個百分比

39、值，缺省值為100，可以理解為FULL SCAN COST/INDEX SCAN COST。當(dāng)n%* INDEX SCAN COST select parameter1,parameter2,parameter3 from v$event_name where name=buffer busy waitsPARAMETER1 PARAMETER2 PARAMETER3- - -file# block# idOracle 10gPARAMETER1 PARAMETER2 PARAMETER3- - -file# block# class#在診斷buffer busy waits事件的過程中，獲取

40、如下信息會很有用：1.獲取產(chǎn)生buffer busy waits事件的等待原因編號，這可以通過查詢該事件的p3參數(shù)值獲得2.獲取產(chǎn)生此事件的SQL語句，可以通過如下的查詢獲得：select sql_text from v$sql t1,v$session t2,v$session_wait t3where t1.address=t2.sql_address and t1.hash_value=t2.sql_hash_valueand t2.sid=t3.sid and t3.event=buffer busy waits;3.獲取等待的塊的類型以及所在的segment，可以通過如下查詢獲得：P

41、HP code:select Segment Header class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait bwhere a.header_file=b.p1 and a.header_block=b.p2 and b.event=buffer busy waitsunionselect Freelist Groups class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$

42、session_wait bwhere a.header_file=b.p1 and b.p2 between a.header_block+1 and (a.header_block+a.freelist_groups) and a.freelist_groups1 and b.event=buffer busy waitsunionselect a.segment_type| block class,a.segment_type,a.segment_name,a.partition_name from dba_extents a,v$session_wait bwhere a.file_i

43、d=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1 and b.event=buffer busy waits and not exists(select 1 from dba_segments where header_file=b.p1 and header_block= b.p2);查詢的第一部分：如果等待的塊類型是segment header，那么可以直接拿buffer busy waits事件的p1和p2參數(shù)去dba_segments視圖中匹配header_file和header_block字段即可找到等待的seg

44、ment名稱和segment類型，進(jìn)行相應(yīng)調(diào)整查詢的第二部分：如果等待的塊類型是freelist groups，也可以在dba_segments視圖中找出對應(yīng)的segment名稱和segment類型，注意這里的參數(shù)p2表示的freelist groups的位置是在segment的header_block+1到header_block+freelist groups組數(shù)之間，并且freelist groups組數(shù)大于1查詢的第三部分：如果等待的塊類型是普通的數(shù)據(jù)塊，那么可以用p1、p2參數(shù)和dba_extents進(jìn)行聯(lián)合查詢得到block所在的segment名稱和segment類型對于不同的等待

45、塊類型，我們采取不同的處理辦法：1.data segment header：進(jìn)程經(jīng)常性的訪問data segment header通常有兩個原因：獲取或修改process freelists信息、擴(kuò)展高水位標(biāo)記，針對第一種情況，進(jìn)程頻繁訪問process freelists信息導(dǎo)致freelist爭用，我們可以增大相應(yīng)的segment對象的存儲參數(shù)freelist或者freelist groups；若由于數(shù)據(jù)塊頻繁進(jìn)出freelist而導(dǎo)致進(jìn)程經(jīng)常要修改freelist，則可以將pctfree值和pctused值設(shè)置較大的差距，從而避免數(shù)據(jù)塊頻繁進(jìn)出freelist；對于第二種情況，由于該se

46、gment空間消耗很快，而設(shè)置的next extent過小，導(dǎo)致頻繁擴(kuò)展高水位標(biāo)記，解決的辦法是增大segment對象的存儲參數(shù)next extent或者直接在創(chuàng)建表空間的時候設(shè)置extent size uniform2.data block：某一或某些數(shù)據(jù)塊被多個進(jìn)程同時讀寫，成為熱點(diǎn)塊，可以通過如下這些辦法來解決這個問題：(1)降低程序的并發(fā)度，如果程序中使用了parallel查詢，降低parallel degree，以免多個parallel slave同時訪問同樣的數(shù)據(jù)對象而形成等待降低性能(2)調(diào)整應(yīng)用程序使之能讀取較少的數(shù)據(jù)塊就能獲取所需的數(shù)據(jù)，減少buffer gets和physi

47、cal reads(3)減少同一個block中的記錄數(shù)，使記錄分布于更多的數(shù)據(jù)塊中，這可以通過若干途徑實(shí)現(xiàn)：可以調(diào)整segment對象的pctfree值，可以將segment重建到block size較小的表空間中，還可以用alter table minimize records_per_block語句減少每塊中的記錄數(shù)(4)若熱點(diǎn)塊對象是類似自增id字段的索引，則可以將索引轉(zhuǎn)換為反轉(zhuǎn)索引，打散數(shù)據(jù)分布，分散熱點(diǎn)塊3.undo segment header：undo segment header爭用是因?yàn)橄到y(tǒng)中undo segment不夠，需要增加足夠的undo segment，根據(jù)undo segment的管理方法，若是手工管理模式，需要修改rollback_segments初始化參數(shù)來增加rollback segment，若是自動管理模式，可以減小transactions_per_rollback_segment初始化參數(shù)的值來使oracle自動增多rollback segment的數(shù)量4.undo block：undo block爭用是由于應(yīng)用程序中存在對數(shù)據(jù)的讀和寫同時進(jìn)行，讀進(jìn)程需要到undo segment中去獲得一致性數(shù)據(jù)，解決辦法是錯開應(yīng)用程序修改數(shù)據(jù)和大量查詢數(shù)據(jù)的時間小結(jié)：buffer busy waits