1、WORKLLOAD REPOOSITOORY rreporrt foor DB NaameDB IddInstaanceInst numReleaaseRACHostICCI131400983996ICCI11110.2.0.3.0YESHPGICCCI1Snap IdSnap TimeeSessiionsCursoors/SSessiionBeginn Snaap:267825-Deec-088 14:04:550241.5End SSnap:268025-Deec-088 15:23:337261.5Elapssed:78.799 (miins)DB Tiime:11.055 (miins

2、)DB Tiime不包包括Oraacle后后臺進(jìn)程消消耗的時間間。如果DB Timee遠(yuǎn)遠(yuǎn)小于于Elappsed時時間，說明明數(shù)據(jù)庫比比較空閑。在79分鐘鐘里（其間間收集了33次快照數(shù)數(shù)據(jù)），數(shù)數(shù)據(jù)庫耗時時11分鐘，RDDA數(shù)據(jù)中中顯示系統(tǒng)統(tǒng)有8個邏輯CPPU（4個物理CPPU），平平均每個CCPU耗時時1.4分鐘鐘，CPUU利用率只只有大約22%（1.4/79）。說明系統(tǒng)統(tǒng)壓力非常常小。可是對于批批量系統(tǒng)，數(shù)數(shù)據(jù)庫的工工作負(fù)載總總是集中在在一段時間間內(nèi)。如果果快照周期期不在這一一段時間內(nèi)內(nèi)，或者快快照周期跨跨度太長而而包含了大大量的數(shù)據(jù)據(jù)庫空閑時時間，所得得出的分析析結(jié)果是沒沒有意義的的。這

3、也說說明選擇分分析時間段段很關(guān)鍵，要要選擇能夠夠代表性能能問題的時時間段。Reporrt SuummarryCachee Sizzes BeginnEndBuffeer Caache:3,3444M3,3444MStd BBlockk Sizze:8KShareed Poool SSize:704M704MLog BBuffeer:14,3552K顯示SGAA中每個區(qū)區(qū)域的大小?。ㄔ贏MMM改變它它們之后），可可用來與初初始參數(shù)值值比較。shareed poool主要要包括liibrarry caache和和dicttionaary ccachee。librrary cachhe用來存存儲最近

4、解解析（或編編譯）后SSQL、PL/SSQL和Javaa claassess等。libbraryy cacche用來來存儲最近近引用的數(shù)數(shù)據(jù)字典。發(fā)生在llibraary ccachee或dicttionaary ccachee的cachhe miiss代價價要比發(fā)生生在bufffer cachhe的代價價高得多。因此shharedd poool的設(shè)置置要確保最最近使用的的數(shù)據(jù)都能能被cacche。Load ProffilePer SSeconndPer TTranssactiionRedo sizee:918,8805.772775,9912.772Logiccal rreadss:3,52

5、11.772,9744.06Blockk chaangess:1,8177.951,5355.22Physiical readds:68.26657.644Physiical writtes:362.559306.220User callls:326.669275.888Parsees:38.66632.655Hard parsses:0.030.03Sortss:0.610.51Logonns:0.010.01Execuutes:354.334299.223Transsactiions:1.18% Bloocks channged per Readd:51.622Recurrsivee Ca

6、lll %:51.722Rollbback per trannsacttion %:85.499Rows per Sortt:#顯示數(shù)據(jù)庫庫負(fù)載概況況，將之與與基線數(shù)據(jù)據(jù)比較才具具有更多的的意義，如如果每秒或或每事務(wù)的的負(fù)載變化化不大，說說明應(yīng)用運運行比較穩(wěn)穩(wěn)定。單個個的報告數(shù)數(shù)據(jù)只說明明應(yīng)用的負(fù)負(fù)載情況，絕絕大多數(shù)據(jù)據(jù)并沒有一一個所謂“正確”的值，然然而Loggons大大于每秒112個、Hardd parrses大大于每秒1100、全全部parrses超超過每秒3300表明明可能有爭爭用問題。Redo sizee：每秒/每事務(wù)產(chǎn)產(chǎn)生的reedo大小?。▎挝蛔肿止?jié)），可可標(biāo)志數(shù)據(jù)據(jù)庫任務(wù)的的繁

7、重程序序。Logiccal rreadss：每秒/每事務(wù)邏邏輯讀的塊塊數(shù)Blockk chaangess：每秒/每事務(wù)修修改的塊數(shù)數(shù)Physiical readds：每秒秒/每事務(wù)物物理讀的塊塊數(shù)Physiical writtes：每每秒/每事務(wù)物物理寫的塊塊數(shù)User callls：每秒秒/每事務(wù)用用戶calll次數(shù)Parsees：SQL解析析的次數(shù)Hard parsses：其其中硬解析析的次數(shù)，硬硬解析太多多，說明SSQL重用用率不高。Sortss：每秒/每事務(wù)的的排序次數(shù)數(shù)Logonns：每秒秒/每事務(wù)登登錄的次數(shù)數(shù)Execuutes：每秒/每事務(wù)SQQL執(zhí)行次次數(shù)Transsactii

8、ons：每秒事務(wù)務(wù)數(shù)Blockks chhangeed peer Reead：表表示邏輯讀讀用于修改改數(shù)據(jù)塊的的比例Recurrsivee Calll：遞歸歸調(diào)用占所所有操作的的比率Rollbback per trannsacttion：每事務(wù)的的回滾率Rows per Sortt：每次排排序的行數(shù)數(shù)注:Oraclle的硬解解析和軟解解析 提到軟軟解析(ssoft parsse)和硬解析析(harrd paarse)，就不能能不說一下下Oraccle對sql的處處理過程。當(dāng)你發(fā)出出一條sqql語句交交付Oraacle，在在執(zhí)行和獲獲取結(jié)果前前，Oraacle對對此sqll將進(jìn)行幾幾個步驟的的處

9、理過程程：1、語語法檢查(synttax ccheckk)檢查此此sql的拼拼寫是否語語法。2、語語義檢查(semaanticc cheeck)諸如檢檢查sqll語句中的的訪問對象象是否存在在及該用戶戶是否具備備相應(yīng)的權(quán)權(quán)限。3、對對sql語句句進(jìn)行解析析(parsse)利用內(nèi)內(nèi)部算法對對sql進(jìn)行行解析，生生成解析樹樹(parrse ttree)及執(zhí)行計計劃(exxecuttion plann)。4、執(zhí)執(zhí)行sqll，返回結(jié)結(jié)果(exxecutte annd reeturnn)其中，軟軟、硬解析析就發(fā)生在在第三個過過程里。Oraacle利利用內(nèi)部的的hashh算法來取取得該sqql的hashh

10、值，然后后在libbraryy cacche里查查找是否存存在該haash值；假設(shè)存存在，則將將此sqll與cachhe中的進(jìn)進(jìn)行比較；假設(shè)“相同”，就將利利用已有的的解析樹與與執(zhí)行計劃劃，而省略略了優(yōu)化器器的相關(guān)工工作。這也也就是軟解解析的過程程。誠然，如如果上面的的2個假設(shè)中中任有一個個不成立，那那么優(yōu)化器器都將進(jìn)行行創(chuàng)建解析析樹、生成成執(zhí)行計劃劃的動作。這個過程程就叫硬解解析。創(chuàng)建解解析樹、生生成執(zhí)行計計劃對于ssql的執(zhí)執(zhí)行來說是是開銷昂貴貴的動作，所所以，應(yīng)當(dāng)當(dāng)極力避免免硬解析，盡盡量使用軟軟解析。Instaance Effiicienncy PPerceentagges (Targ

11、get 1100%) Buffeer Noowaitt %:100.000Redo NoWaait %:100.000Buffeer Hiit %:98.722In-meemoryy Sorrt %:99.866Libraary HHit %:99.977Soft Parsse %:99.922Execuute tto Paarse %:89.099Latchh Hitt %:99.999Parsee CPUU to Parsse Ellapsdd %:7.99% Nonn-Parrse CCPU:99.955本節(jié)包含了了Oraccle關(guān)鍵鍵指標(biāo)的內(nèi)內(nèi)存命中率率及其它數(shù)數(shù)據(jù)庫實例例操作的效效率

12、。其中中Bufffer HHit RRatioo 也稱Cacche HHit RRatioo，Librrary Hit ratiio也稱Libbraryy Cacche HHit rratioo。同Loaad Prrofille一節(jié)相相同，這一一節(jié)也沒有有所謂“正確”的值，而而只能根據(jù)據(jù)應(yīng)用的特特點判斷是是否合適。在一個使使用直接讀讀執(zhí)行大型型并行查詢詢的DSSS環(huán)境，200%的Bufffer HHit RRatioo是可以接接受的，而而這個值對對于一個OOLTP系系統(tǒng)是完全全不能接受受的。根據(jù)據(jù)Oraccle的經(jīng)經(jīng)驗，對于于OLTPPT系統(tǒng)，Buufferr Hitt Rattio理想想應(yīng)該

13、在990%以上上。Buffeer Noowaitt表示在內(nèi)內(nèi)存獲得數(shù)數(shù)據(jù)的未等等待比例。buffeer hiit表示進(jìn)進(jìn)程從內(nèi)存存中找到數(shù)數(shù)據(jù)塊的比比率，監(jiān)視視這個值是是否發(fā)生重重大變化比比這個值本本身更重要要。對于一一般的OLLTP系統(tǒng)統(tǒng)，如果此此值低于880%，應(yīng)應(yīng)該給數(shù)據(jù)據(jù)庫分配更更多的內(nèi)存存。Redo NoWaait表示示在LOGG緩沖區(qū)獲獲得BUFFFER的的未等待比比例。如果果太低（可可參考900%閥值），考考慮增加LLOG BBUFFEER。libraary hhit表示示Oraccle從Librrary Cachhe中檢索索到一個解解析過的SSQL或PL/SSQL語句句的比率，

14、當(dāng)當(dāng)應(yīng)用程序序調(diào)用SQQL或存儲儲過程時，Oracle檢查Library Cache確定是否存在解析過的版本，如果存在，Oracle立即執(zhí)行語句；如果不存在，Oracle解析此語句，并在Library Cache中為它分配共享SQL區(qū)。低的library hit ratio會導(dǎo)致過多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要調(diào)大shared pool區(qū)。Latchh Hitt：Latcch是一種種保護(hù)內(nèi)存存結(jié)構(gòu)的鎖鎖，可以認(rèn)認(rèn)為是SEERVERR進(jìn)程獲取取訪問內(nèi)存存數(shù)據(jù)結(jié)構(gòu)構(gòu)的許可。要確保Laatch Hit99%，否否則意味著著Sharred

15、 PPool latcch爭用，可可能由于未未共享的SSQL，或或者Libbraryy Cacche太小小，可使用用綁定變更更或調(diào)大SShareed Poool解決決。Parsee CPUU to Parsse Ellapsdd：解析實實際運行時時間/(解析實實際運行時時間+解析中等等待資源時時間)，越高越好好。Non-PParsee CPUU ：SQL實際際運行時間間/(SQQL實際運運行時間+SQL解解析時間)，太低表示示解析消耗耗時間過多多。Execuute tto Paarse：是語句執(zhí)執(zhí)行與分析析的比例，如果要SQL重用率高，則這個比例會很高。該值越高表示一次解析后被重復(fù)執(zhí)行的次數(shù)越

16、多。In-meemoryy Sorrt：在內(nèi)內(nèi)存中排序序的比率，如果過低說明有大量的排序在臨時表空間中進(jìn)行?？紤]調(diào)大PGA。Soft Parsse：軟解解析的百分分比（soofts/softts+haards），近似當(dāng)當(dāng)作sqll在共享區(qū)區(qū)的命中率率，太低則則需要調(diào)整整應(yīng)用使用用綁定變量量。Shareed Poool SStatiisticcs BeginnEndMemorry Ussage %:47.19947.500% SQLL witth exxecuttionss1:88.48879.811% Memmory for SQL w/exxec11:79.99973.522Memorry

17、Ussage %：對于于一個已經(jīng)經(jīng)運行一段段時間的數(shù)數(shù)據(jù)庫來說說，共享池池內(nèi)存使用用率，應(yīng)該該穩(wěn)定在775%-990%間，如如果太小，說說明Shaared Pooll有浪費，而而如果高于于90，說明明共享池中中有爭用，內(nèi)內(nèi)存不足。SQL wwith execcutioons11：執(zhí)行次次數(shù)大于11的sql比率率，如果此此值太小，說說明需要在在應(yīng)用中更更多使用綁綁定變量，避避免過多SSQL解析析。Memorry foor SQQL w/execc1：執(zhí)執(zhí)行次數(shù)大大于1的SQL消耗耗內(nèi)存的占占比。Top 55 Timmed EEventts EventtWaitssTime(s)Avg WWait

18、(ms)% Tottal CCall TimeeWait ClasssCPU ttime51577.6SQL*NNet mmore dataa froom cllientt27,31196429.7Netwoorklog ffile paraallell wriite5,49774797.1Systeem I/Odb fiile ssequeentiaal reead7,90003545.3User I/Odb fiile pparalllel writte4,80663475.1Systeem I/O這是報告概概要的最后后一節(jié)，顯顯示了系統(tǒng)統(tǒng)中最嚴(yán)重重的5個等待，按按所占等待待時間的比比例倒

19、序列列示。當(dāng)我我們調(diào)優(yōu)時時，總希望望觀察到最最顯著的效效果，因此此應(yīng)當(dāng)從這這里入手確確定我們下下一步做什什么。例如如如果bufffer bbusy waitt是較嚴(yán)嚴(yán)重的等待待事件，我我們應(yīng)當(dāng)繼繼續(xù)研究報報告中Buufferr Waiit和Filee/Tabblesppace IO區(qū)的的內(nèi)容，識識別哪些文文件導(dǎo)致了了問題。如如果最嚴(yán)重重的等待事事件是I/O事件，我我們應(yīng)當(dāng)研研究按物理理讀排序的的SQL語句句區(qū)以識別別哪些語句句在執(zhí)行大大量I/OO，并研究究Tabllespaace和I/O區(qū)觀觀察較慢響響應(yīng)時間的的文件。如如果有較高高的LATTCH等待待，就需要要察看詳細(xì)細(xì)的LATTCH統(tǒng)計計識

20、別哪些些LATCCH產(chǎn)生的的問題。在這里，llog ffile paraallell wriite是相相對比較多多的等待，占占用了7%的CPU時間間。通常，在沒沒有問題的的數(shù)據(jù)庫中中，CPUU timme總是列列在第一個個。更多的等待待事件，參參見本報告告 的Waitt Eveents一一節(jié)。RAC SStatiisticcsBeginnEndNumbeer off Insstancces:22Globaal Caache Loadd Proofilee Per SSeconndPer TTranssactiionGlobaal Caache bloccks rreceiived:4.163.

21、51Globaal Caache bloccks sserveed:5.975.04GCS/GGES mmessaages receeivedd:408.447344.995GCS/GGES mmessaages sentt:258.003217.990DBWR Fusiion wwritees:0.050.05Estd Inteerconnnectt traafficc (KBB)211.116Globaal Caache Effiicienncy PPerceentagges (Targget llocall+remmote 100%) Buffeer acccesss - llocall

22、 cacche %:98.600Buffeer acccesss - rremotte caache %:0.12Buffeer acccesss - ddisk %:1.28Globaal Caache and Enquueue Servvicess - WWorklload Charracteeristtics Avg gglobaal ennqueuue geet tiime (ms):0.1Avg gglobaal caache cr bblockk recceivee timme (mms):1.1Avg gglobaal caache currrent blocck reeceiv

23、ve tiime (ms):0.8Avg gglobaal caache cr bblockk buiild ttime (ms):0.0Avg gglobaal caache cr bblockk sennd tiime (ms):0.0Globaal caache log flusshes for cr bblockks seervedd %:3.5Avg gglobaal caache cr bblockk fluush ttime (ms):3.9Avg gglobaal caache currrent blocck piin tiime (ms):0.0Avg gglobaal ca

24、ache currrent blocck seend ttime (ms):0.0Globaal caache log flusshes for currrent bloccks sserveed %:0.4Avg gglobaal caache currrent blocck fllush timee (mss):3.0Globaal Caache and Enquueue Servvicess - MMessaagingg Staatisttics Avg mmessaage ssent queuue tiime (ms):0.0Avg mmessaage ssent queuue tii

25、me oon kssxp (ms):0.3Avg mmessaage rreceiived queuue tiime (ms):0.5Avg GGCS mmessaage pproceess ttime (ms):0.0Avg GGES mmessaage pproceess ttime (ms):0.0% of direect ssent messsagess:14.400% of indiirectt sennt meessagges:77.044% of floww conntrollled messsagess:8.56Main Repoort HYPERLINK l 21 Wait

26、Evennts SStatiisticcs HYPERLINK l 22 SQL SStatiisticcs HYPERLINK l 23 Instaance Actiivityy Staatisttics HYPERLINK l 24 IO Sttats HYPERLINK l 1900 Buffeer Poool SStatiisticcs HYPERLINK l 26 Advissory Stattistiics HYPERLINK l 27 Wait Stattistiics HYPERLINK l 28 Undo Stattistiics HYPERLINK l 29 Latchh

27、Staatisttics HYPERLINK l 30 Segmeent SStatiisticcs HYPERLINK l 31 Dictiionarry Caache Stattistiics HYPERLINK l 32 Libraary CCachee Staatisttics HYPERLINK l 33 Memorry Sttatissticss HYPERLINK l 34 Streaams SStatiisticcs HYPERLINK l 6700 Resouurce Limiit Sttatissticss HYPERLINK l 6800 init.ora Paraame

28、teers Wait Evennts SStatiisticcs HYPERLINK l 1500 Time Modeel Sttatissticss HYPERLINK l 200 Wait Classs HYPERLINK l 100 Wait Evennts HYPERLINK l 300 Backggrounnd Waait EEventts HYPERLINK l 1600 Operaatingg Sysstem Stattistiics HYPERLINK l 5800 Serviice SStatiisticcs HYPERLINK l 5820 Serviice WWait C

29、lasss Sttats HYPERLINK l top Back to TTopTime Modeel SttatissticssTotall timme inn dattabasse usser-ccallss (DBB Timme): 663ss Statiisticcs inncludding the wordd baackgrroundd meeasurre baackgrroundd proocesss timme, aand sso doo nott conntribbute to tthe DDB tiime sstatiisticc Orderred bby % or DDB

30、 tiime ddesc, Staatisttic nname Statiisticc NammeTime (s)% of DB TTimeDB CPPU514.55077.611sql eexecuute eelapssed ttime482.22772.744parsee timme ellapseed3.760.57PL/SQQL exxecuttion elappsed timee0.500.08hard parsse ellapseed tiime0.340.05conneectioon maanageementt calll ellapseed tiime0.080.01hard

31、parsse (sshariing ccriteeria) elaapsedd timme0.000.00repeaated bindd elaapsedd timme0.000.00PL/SQQL coompillatioon ellapseed tiime0.000.00faileed paarse elappsed timee0.000.00DB tiime662.997backggrounnd ellapseed tiime185.119backggrounnd cppu tiime67.488此節(jié)顯示了了各種類型型的數(shù)據(jù)庫庫處理任務(wù)務(wù)所占用的的CPU時間間。 HYPERLINK l

32、21 Back to WWait Evennts SStatiisticcs HYPERLINK l top Backk to TopWait Classss - sseconnd cs - centtiseccond - 1000th of aa seccond ms - millliseccond - 10000thh of a seecondd us - micrroseccond - 100000000th of aa seccond orderred bby waait ttime descc, waaits descc Wait ClasssWaitss%Timee -ouutsT

33、otall Waiit Tiime (s)Avg wwait (ms)Waitss /txxnUser I/O66,83370.00120211.944Systeem I/O28,29950.009335.05Netwoork1,5711,45000.00660280.772Clustter210,55480.0029037.611Otherr81,788371.82228014.611Appliicatiion333,11550.0016059.511Concuurrenncy5,18220.04510.93Commiit9190.00440.16Confiiguraationn25,422

34、799.466104.54 HYPERLINK l 21 Back to WWait Evennts SStatiisticcs HYPERLINK l top Backk to TopWait Evenntss - sseconnd cs - centtiseccond - 1000th of aa seccond ms - millliseccond - 10000thh of a seecondd us - micrroseccond - 100000000th of aa seccond orderred bby waait ttime descc, waaits descc (idd

35、le eeventts laast) EventtWaitss%Timee -ouutsTotall Waiit Tiime (s)Avg wwait (ms)Waitss /txxnSQL*NNet mmore dataa froom cllientt27,31190.006424.88log ffile paraallell wriite5,49770.004790.98db fiile ssequeentiaal reead7,90000.003541.41db fiile pparalllel writte4,80660.003470.86db fiile sscattteredd r

36、eaad10,31100.003131.84direcct paath wwritee42,72240.003017.63reliaable messsage3552.8218490.06SQL*NNet bbreakk/resset tto cllientt333,00840.0016059.500db fiile pparalllel readd3,73220.001340.67gc cuurrennt muulti blocck reequesst175,77100.0010031.399contrrol ffile sequuentiial rread15,97740.001012.8

37、5direcct paath rread tempp1,87330.00950.33gc crr mullti bblockk reqquestt20,87770.00803.73log ffile syncc9190.00440.16gc crr bloock bbusy5260.00360.09enq: FB - conntenttion10,38840.00301.85DFS llock handdle3,51770.00310.63contrrol ffile paraallell wriite1,94660.00310.35gc cuurrennt bllock 2-waay4,16

38、550.00200.74libraary ccachee locck4320.00240.08name-servvice calll waiit220.002760.00row ccachee locck3,89440.00200.70gcs llog fflushh synnc1,259942.022210.22os thhreadd staartupp185.562890.00gc crr bloock 22-wayy3,67110.00200.66gc cuurrennt bllock busyy1130.001120.02SQL*NNet mmessaage tto cllientt1

39、,5444,11550.0010275.883gc buufferr bussy156.671700.00gc crr dissk reead3,27220.00100.58direcct paath wwritee temmp1590.00150.03gc cuurrennt grrant busyy8980.00110.16log ffile swittch ccomplletioon290.001170.01CGS wwait for IPC msg48,733999.877008.71gc cuurrennt grrant 2-waay1,14220.00000.20kjbdrrmcv

40、ttq lmmon ddrm qquiessce: pingg commplettion90.000190.00enq: US - conntenttion5670.00000.10direcct paath rread1380.00010.02enq: WF - conntenttion140.00090.00ksxr polll remmote insttancees13,299158.455002.37libraary ccachee pinn2110.00010.04ges gglobaal reesourrce ddirecctoryy to be ffrozeen9100.0000

41、100.00wait for scn ack5830.00000.10log ffile sequuentiial rread360.00020.01undo segmment exteensioon25,344299.799004.53rdbmss ipcc repply2790.00000.05ktfbttgex6100.0000100.00enq: HW - conntenttion440.00010.01gc crr graant 22-wayy1580.00000.03enq: TX - inddex cconteentioon10.000340.00enq: CF - connte

42、nttion640.00010.01PX Deeq: SSignaal ACCK3721.622010.01latchh freee30.000100.00buffeer buusy wwaitss6250.16000.11KJC: Waitt forr msgg sennds tto coompleete1540.00000.03log bbuffeer sppace110.00020.00enq: PS - conntenttion460.00010.01enq: TM - conntenttion700.00000.01IPC ssend comppletiion ssync40100.

43、000000.01PX Deeq: rreap creddit1,544499.811000.28log ffile singgle wwritee360.00000.01enq: TT - conntenttion460.00000.01enq: TD - KTFF dummp enntriees120.00010.00read by ootherr sesssionn10.000120.00LGWR waitt forr reddo coopy5400.00000.10PX Deeq Crreditt: seend bblkd175.88000.00enq: TA - conntentti

44、on140.00000.00latchh: gees reesourrce hhash listt440.00000.01enq: PI - conntenttion80.00000.00writee commplette waaits10.00020.00enq: DR - conntenttion30.00000.00enq: MW - conntenttion30.00000.00enq: TS - conntenttion30.00000.00PX qrref llatchh150100.000000.03enq: MD - conntenttion20.00000.00latchh:

45、 KCCL gcc eleementt parrent latcch110.00000.00enq: JS - jobb runn locck - syncchronnize10.00010.00SQL*NNet mmore dataa to clieent160.00000.00latchh: caache bufffers lru chaiin10.00000.00enq: UL - conntenttion10.00000.00gc cuurrennt spplit10.00000.00enq: AF - tassk seeriallizattion10.00000.00latchh:

46、obbjectt queeue hheadeer opperattion30.00000.00latchh: caache bufffers chaiins10.00000.00latchh: ennqueuue haash cchainns20.00000.00SQL*NNet mmessaage ffrom clieent1,5444,11330.0012,62268275.883gcs rremotte meessagge634,888498.6449,203314113.441DIAG idlee waiit23,62280.004,61661954.22ges rremotte me

47、essagge149,559193.4554,61223126.722Streaams AAQ: qqmn sslavee idlle waait1670.004,61112761110.03Streaams AAQ: qqmn ccoorddinattor iidle waitt35147.8664,61111313770.06Streaams AAQ: wwaitiing ffor mmessaages in tthe qqueuee488100.0004,605594360.09virtuual ccircuuit sstatuus157100.0004,59662927220.03PX

48、 Iddle WWait1,072297.1112,581124070.19jobq slavve waait14597.93342028960.03Streaams AAQ: wwaitiing ffor ttime manaagemeent oor clleanuup taasks1100.00027026974470.00PX Deeq: PParsee Repply4040.000030.01PX Deeq: EExecuutionn Msgg12126.455000.02PX Deeq: JJoin ACK3842.111010.01PX Deeq: EExecuute RReply

49、y3432.355000.01PX Deeq: MMsg FFragmment160.00000.00Streaams AAQ: RRAC qqmn ccoorddinattor iidle waitt351100.000000.06classs slaave wwait20.00000.00db fiile sscattteredd reaad等待事事件是當(dāng)SSESSIION等待待multti-bllock I/O時時發(fā)生的，通通過是由于于fulll tabble sscanss或 inddex ffast fulll scaans。發(fā)發(fā)生過多讀讀操作的SSegmeents可可以在“Segm

50、mentss by Physsicall Reaads”和和 “SQQL orrdereed byy Reaads”節(jié)節(jié)中識別（在在其它版本本的報告中中，可能是是別的名稱稱）。如果果在OLTTP應(yīng)用中中，不應(yīng)該該有過多的的全掃描操操作，而應(yīng)應(yīng)使用選擇擇性好的索索引操作。DB fiile ssequeentiaal reead等待待意味著發(fā)發(fā)生順序II/O讀等等待（通常常是單塊讀讀取到連續(xù)續(xù)的內(nèi)存區(qū)區(qū)域中），如如果這個等等待非常嚴(yán)嚴(yán)重，應(yīng)該該使用上一一段的方法法確定執(zhí)行行讀操作的的熱點SEEGMENNT，然后后通過對大大表進(jìn)行分分區(qū)以減少少I/O量，或或者優(yōu)化執(zhí)執(zhí)行計劃（通通過使用存存儲大綱或或

51、執(zhí)行數(shù)據(jù)據(jù)分析）以以避免單塊塊讀操作引引起的seequenntiall reaad等待。通過在批批量應(yīng)用中中，DB filee seqquenttial readd是很影響響性能的事事件，總是是應(yīng)當(dāng)設(shè)法法避免。Log FFile Paraallell Wriite事件件是在等待待LGWRR進(jìn)程將REEDO記錄錄從LOGG 緩沖區(qū)區(qū)寫到聯(lián)機(jī)機(jī)日志文件件時發(fā)生的的。雖然寫寫操作可能能是并發(fā)的的，但LGGWR需要要等待最后后的I/OO寫到磁盤盤上才能認(rèn)認(rèn)為并行寫寫的完成，因因此等待時時間依賴于于OS完成所所有請求的的時間。如如果這個等等待比較嚴(yán)嚴(yán)重，可以以通過將LLOG文件件移到更快快的磁盤上上或者

52、條帶帶化磁盤（減減少爭用）而而降低這個個等待。Buffeer Buusy WWaitss事件是在在一個SEESSIOON需要訪訪問BUFFFER CACHHE中的一一個數(shù)據(jù)庫庫塊而又不不能訪問時時發(fā)生的。緩沖區(qū)“busyy”的兩個原原因是：11）另一個個SESSSION正正在將數(shù)據(jù)據(jù)塊讀進(jìn)BBUFFEER。2）另一個個SESSSION正正在以排它它模式占用用著這塊被被請求的BBUFFEER。可以以在“Segmmentss by Bufffer BBusy Waitts”一節(jié)中找找出發(fā)生這這種等待的的SEGMMENT，然然后通過使使用revversee-keyy inddexess并對熱表表進(jìn)行

53、分區(qū)區(qū)而減少這這種等待事事件。Log FFile Syncc事件，當(dāng)當(dāng)用戶SEESSIOON執(zhí)行事事務(wù)操作（COMMIT或ROLLBACK等）后，會通知 LGWR進(jìn)程將所需要的所有REDO信息從LOG BUFFER寫到LOG文件，在用戶SESSION等待LGWR返回安全寫入磁盤的通知時發(fā)生此等待。減少此等待的方法寫Log File Parallel Write事件的處理。Enqueeue WWaitss是串行訪訪問本地資資源的本鎖鎖，表明正正在等待一一個被其它它SESSSION（一一個或多個個）以排它它模式鎖住住的資源。減少這種種等待的方方法依賴于于生產(chǎn)等待待的鎖類型型。導(dǎo)致EEnqueeue

54、等待待的主要鎖鎖類型有三三種：TXX（事務(wù)鎖鎖）, TTM D（ML鎖）和ST（空間管理理鎖）。 HYPERLINK l 21 Back to WWait Evennts SStatiisticcs HYPERLINK l top Backk to TopBackggrounnd Waait EEventtsorderred bby waait ttime descc, waaits descc (iddle eeventts laast) EventtWaitss%Timee -ouutsTotall Waiit Tiime (s)Avg wwait (ms)Waitss /txxnlog

55、ffile paraallell wriite5,49770.004790.98db fiile pparalllel writte4,80660.003470.86eventts inn waiitclaass OOtherr69,000283.25522012.333contrrol ffile sequuentiial rread9,32330.00711.67contrrol ffile paraallell wriite1,94660.00310.35os thhreadd staartupp185.562890.00direcct paath rread1380.00010.02d

56、b fiile ssequeentiaal reead210.00050.00direcct paath wwritee1380.00000.02log ffile sequuentiial rread360.00020.01gc crr bloock 22-wayy960.00000.02gc cuurrennt bllock 2-waay780.00000.01log bbuffeer sppace110.00020.00row ccachee locck590.00000.01log ffile singgle wwritee360.00000.01buffeer buusy wwait

57、ss1510.66000.03gc cuurrennt grrant busyy290.00000.01libraary ccachee locck40.00010.00enq: TM - conntenttion100.00000.00gc cuurrennt grrant 2-waay80.00000.00gc crr mullti bblockk reqquestt70.00000.00gc crr graant 22-wayy50.00000.00rdbmss ipcc messsagee97,288873.77750,199451617.388gcs rremotte meessag

58、ge634,888698.6449,203314113.441DIAG idlee waiit23,62280.004,61661954.22pmon timeer1,6211100.0004,615528470.29ges rremotte meessagge149,559193.4554,61223126.722Streaams AAQ: qqmn sslavee idlle waait1670.004,61112761110.03Streaams AAQ: qqmn ccoorddinattor iidle waitt35147.8664,61111313770.06smon timee

59、r2776.504,53111635660.05Streaams AAQ: wwaitiing ffor ttime manaagemeent oor clleanuup taasks1100.00027026974470.00PX Deeq: PParsee Repply4040.000030.01PX Deeq: JJoin ACK3842.111010.01PX Deeq: EExecuute RReplyy3432.355000.01Streaams AAQ: RRAC qqmn ccoorddinattor iidle waitt351100.000000.06 HYPERLINK

60、l 21 Back to WWait Evennts SStatiisticcs HYPERLINK l top Backk to TopOperaatingg Sysstem StattistiicsStatiisticcTotallNUM_LLCPUSS0NUM_VVCPUSS0AVG_BBUSY_TIMEE101,4442AVG_IIDLE_TIMEE371,2241AVG_IIOWAIIT_TIIME5,4600AVG_SSYS_TTIME25,7995AVG_UUSER_TIMEE75,5110BUSY_TIMEE812,6644IDLE_TIMEE2,9711,0777IOWAII