Oracle數(shù)據(jù)庫(kù)性能優(yōu)化精解

Oracle數(shù)據(jù)庫(kù)性能優(yōu)化精解Overview數(shù)據(jù)庫(kù)應(yīng)用調(diào)優(yōu)概述優(yōu)化思路優(yōu)化步驟優(yōu)化目標(biāo)性能診斷的“七”種武器

工欲善其事,必先利其器性能優(yōu)化經(jīng)典案例詳解事實(shí)勝于雄辯不可照葫蘆畫(huà)瓢，過(guò)分循規(guī)蹈矩<InsertPictureHere>數(shù)據(jù)庫(kù)應(yīng)用調(diào)優(yōu)概述優(yōu)化思路優(yōu)化的根本方法？我們到底要調(diào)優(yōu)什么？誰(shuí)參與調(diào)優(yōu)?ApplicationdesignersApplicationdevelopersDatabaseadministratorsSystemadministrators有哪些類(lèi)型的調(diào)優(yōu)?從什么時(shí)候開(kāi)始調(diào)優(yōu)?調(diào)優(yōu)到什么時(shí)候結(jié)束？調(diào)優(yōu)一個(gè)反復(fù)的過(guò)程，需要隨時(shí)監(jiān)控，隨時(shí)調(diào)整調(diào)優(yōu)的階段調(diào)優(yōu)被分成不同的階段:需求分析和架構(gòu)設(shè)計(jì)需求實(shí)現(xiàn)〔業(yè)務(wù)與技術(shù)〕與數(shù)據(jù)存儲(chǔ)應(yīng)用架構(gòu)與數(shù)據(jù)庫(kù)架構(gòu)應(yīng)用設(shè)計(jì)和開(kāi)發(fā)應(yīng)用模塊設(shè)計(jì)與E-R模型設(shè)計(jì)代碼標(biāo)準(zhǔn)與數(shù)據(jù)標(biāo)準(zhǔn)高效SQL的書(shū)寫(xiě)數(shù)據(jù)庫(kù)配置參數(shù)文件表空間/數(shù)據(jù)文件部屬應(yīng)用表、索引、PLSQL程序周期性調(diào)整全面檢查低效時(shí)調(diào)整針對(duì)性調(diào)整Oracle性能因素常見(jiàn)的調(diào)整問(wèn)題架構(gòu)：糟糕的數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)〔單機(jī)還是rac/中間件還是MTS/容災(zāi)方案/備份策略/存儲(chǔ)配置。。。〕糟糕的session管理(通常跟中間件或者業(yè)務(wù)處理方式有關(guān))開(kāi)發(fā)：糟糕的關(guān)系設(shè)計(jì)(通常因?yàn)闆](méi)有很好遵守標(biāo)準(zhǔn)/或者過(guò)度遵循標(biāo)準(zhǔn))糟糕的游標(biāo)管理(通常是開(kāi)發(fā)人員的錯(cuò)誤導(dǎo)致)糟糕的應(yīng)用算法設(shè)計(jì)(通常與功能模塊設(shè)計(jì)有關(guān))糟糕的語(yǔ)句算法設(shè)計(jì)(通常與語(yǔ)句書(shū)寫(xiě)及數(shù)據(jù)庫(kù)對(duì)象有關(guān))調(diào)優(yōu)的階段調(diào)優(yōu)設(shè)計(jì).架構(gòu)設(shè)計(jì)〔Rac/單機(jī)〕、應(yīng)用設(shè)計(jì)〔模塊設(shè)計(jì)、E-R模型設(shè)計(jì)〕調(diào)優(yōu)應(yīng)用.代碼調(diào)優(yōu)、應(yīng)用存儲(chǔ)對(duì)象調(diào)優(yōu)、應(yīng)用算法調(diào)優(yōu)調(diào)優(yōu)內(nèi)存.數(shù)據(jù)高速緩存區(qū)、共享池、重做日志緩存區(qū)、大池調(diào)優(yōu)I/O.表空間數(shù)據(jù)文件劃分、存儲(chǔ)對(duì)象分布、文件的存儲(chǔ)分布等調(diào)優(yōu)競(jìng)爭(zhēng).回滾段、Lock、latch調(diào)優(yōu)操作系統(tǒng).共享內(nèi)存段大小、文件緩存大小、網(wǎng)絡(luò)配置、文件系統(tǒng)與裸設(shè)備、Raid模式、存儲(chǔ)緩存、異步I/O等優(yōu)化的根本原那么從應(yīng)用需求入手，調(diào)需求遠(yuǎn)重于調(diào)供給 ——總原那么需求的正確性和合理性是首先需要確定的如果某個(gè)局部不是瓶頸，就不要輕易嘗試優(yōu)化 ——有目的優(yōu)化可能會(huì)過(guò)頭，注意協(xié)調(diào)整個(gè)系統(tǒng)的性能 ——有尺度優(yōu)化是為系統(tǒng)提供足夠的資源并且充分合理的使用資源，而不是無(wú)節(jié)制的擴(kuò)充資源 ——有依據(jù)優(yōu)化也意味著合理的劃分任務(wù) ——有條理主動(dòng)式性能優(yōu)化的根本步驟詳細(xì)了解業(yè)務(wù)特性和優(yōu)化需求設(shè)立合理的性能優(yōu)化目標(biāo)收集并記錄當(dāng)前性能信息〔DB/OS〕確定當(dāng)前os和db的性能瓶頸〔等待什么〕結(jié)合性能指標(biāo)、用戶(hù)描述和性能信息分析瓶頸的原因確定適宜的優(yōu)化方法優(yōu)化相關(guān)的性能問(wèn)題〔應(yīng)用程序、數(shù)據(jù)庫(kù)、i/o、爭(zhēng)用、os、存儲(chǔ)、網(wǎng)絡(luò)等〕逐步實(shí)施優(yōu)化方法，記錄實(shí)施更改正程及場(chǎng)景在業(yè)務(wù)穩(wěn)定運(yùn)行后重新收集性能信息，確定是否到達(dá)優(yōu)化要求如果沒(méi)有成功，重復(fù)前面的步驟，直到滿(mǎn)足優(yōu)化目標(biāo)OLTP系統(tǒng)高吞吐量,大量的插入和修改大的,連續(xù)增加的數(shù)據(jù)大量用戶(hù)的并發(fā)訪(fǎng)問(wèn)調(diào)優(yōu)目的:高可用性高速并發(fā)性O(shè)LTP系統(tǒng)架構(gòu)和數(shù)據(jù)庫(kù)設(shè)計(jì)Rac的使用？MTS？主機(jī)資源需求：多CPU較高的I/O能力，尤其是I/O寫(xiě)能力〔單次寫(xiě)較多〕內(nèi)存大了好數(shù)據(jù)緩存區(qū)、共享池、日志緩沖區(qū)都要足夠大，PGA可以小考慮控制日志產(chǎn)生量，分散I/O，加快日志的I/O速度OLTP存儲(chǔ)對(duì)象減少動(dòng)態(tài)分配〔文件、區(qū)、塊〕Initrans要設(shè)置的足夠高，尤其是在索引上塊不宜太大，pctfree不能太小索引:不要太多(B-tree比bitmap更好)對(duì)于順序遞增或遞減的列使用反轉(zhuǎn)索引有規(guī)那么的重建外鍵字段上考慮建索引OLTP應(yīng)用的問(wèn)題減少并發(fā)事務(wù)量，控制事務(wù)大小減少鎖級(jí)別、鎖并發(fā)量控制約束的數(shù)量控制觸發(fā)器的個(gè)數(shù)和大小考慮使用約束條件而不使用應(yīng)用代碼來(lái)實(shí)現(xiàn)數(shù)據(jù)校驗(yàn)適當(dāng)減少外鍵的使用SQL解析：確保代碼可共享.為了優(yōu)化共享的SQL,使用綁定變量而不是文本值.使用CURSOR_SHARING參數(shù).批量數(shù)據(jù)裝載海量數(shù)據(jù)的查詢(xún)大量全表掃描的使用調(diào)優(yōu)目的:快速的響應(yīng)時(shí)間準(zhǔn)確性對(duì)數(shù)據(jù)倉(cāng)庫(kù)應(yīng)使用并行查詢(xún)的特性.決策支持系統(tǒng)Data數(shù)據(jù)倉(cāng)庫(kù)的應(yīng)用架構(gòu)和數(shù)據(jù)庫(kù)設(shè)計(jì)Rac的必要性？MTS？主機(jī)資源需求：多CPU，高CPU主頻更高的I/O能力，尤其是I/O讀能力大池、日志緩沖區(qū)較大關(guān)閉歸檔約束的必要性〔尤其是外鍵〕Nologging的選擇考慮使用數(shù)據(jù)預(yù)處理機(jī)制考慮多層數(shù)據(jù)中心架構(gòu)數(shù)據(jù)倉(cāng)庫(kù)存儲(chǔ)存儲(chǔ)對(duì)象的考慮:仔細(xì)的設(shè)置BLOCKSIZE和DB_FILE_MULTIBLOCK_READ_COUNT.確保區(qū)的大小是參數(shù)乘積的整數(shù)倍.Pctfree可以設(shè)為0存儲(chǔ)上I/O分散基于冗余字段的多表聯(lián)接(數(shù)據(jù)穩(wěn)定),使用hash聚簇表大量使用分區(qū)表和分區(qū)索引評(píng)估索引的使用:盡可能使用位圖索引.對(duì)于按照主鍵執(zhí)行的范圍查詢(xún),使用索引組織表.對(duì)非均勻分布的索引列一定要生成柱狀圖.數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用的問(wèn)題分析時(shí)間不是很重要了.綁定變量可能會(huì)出問(wèn)題.執(zhí)行方案必須被優(yōu)化:執(zhí)行ANALYZE,或者DBMS_STATS.謹(jǐn)慎選擇索引使用并行查詢(xún)特性.仔細(xì)的調(diào)優(yōu),如果有可能使用提示.測(cè)試實(shí)際的數(shù)據(jù).有些功能盡量使用pl/sql程序而不使用應(yīng)用程序語(yǔ)言來(lái)實(shí)現(xiàn)應(yīng)用性能的相關(guān)考慮性能與下面的因素有關(guān):效勞器資源的繁忙度語(yǔ)句對(duì)資源的需求量程序訪(fǎng)問(wèn)的并發(fā)度〔OLTP需要關(guān)注〕程序的重用性〔OLTP需要關(guān)注〕網(wǎng)絡(luò)傳輸數(shù)據(jù)的代價(jià)〔OLAP需要關(guān)注〕考慮現(xiàn)在應(yīng)用的資源需求是否合理考慮是否需要擴(kuò)充資源以滿(mǎn)足資源需求考慮是否需要限制擴(kuò)展需求以滿(mǎn)足性能要求調(diào)優(yōu)的目的減少或消除等待事件訪(fǎng)問(wèn)最少的塊數(shù)將數(shù)據(jù)塊保存在內(nèi)存中響應(yīng)時(shí)間吞吐量負(fù)載量平衡性能與數(shù)據(jù)庫(kù)健壯性的需求影響性能的一些因素:Raid方式復(fù)用控制文件復(fù)用重作日志文件頻繁的執(zhí)行檢查點(diǎn)備份數(shù)據(jù)文件執(zhí)行歸檔異地容災(zāi)等調(diào)優(yōu)所需的主要信息操作系統(tǒng)IO內(nèi)存CPU交換區(qū)網(wǎng)絡(luò)傳輸數(shù)據(jù)庫(kù) 統(tǒng)計(jì)信息 等待事件應(yīng)用級(jí)Sql語(yǔ)句統(tǒng)計(jì)和執(zhí)行方案業(yè)務(wù)級(jí)客戶(hù)反響的性能表象和業(yè)務(wù)運(yùn)行特性應(yīng)用系統(tǒng)級(jí)優(yōu)化的相關(guān)工具操作系統(tǒng)top/sar/vmstat/iostat/netstat數(shù)據(jù)庫(kù) 動(dòng)態(tài)性能視圖utlbstat.sql/utlestat.sql statspackAWR/ADDM 等待事件 OEM的tuning包和diagnostics包自動(dòng)性能優(yōu)化指導(dǎo)應(yīng)用級(jí) autotrace SqltraceSQLTunningAdvisorSQLAccessAdvisor輔助性的相關(guān)文件 告警日志文件 后臺(tái)進(jìn)程跟蹤文件 用戶(hù)進(jìn)程跟蹤文件第三方的一些工具TOP#/usr/local/bin/toplastpid:9669;loadaverages:7.98,8.04,8.2621:59:45198processes:193sleeping,1running,4oncpuCPUstates:15.8%idle,69.1%user,13.1%kernel,1.9%iowait,0.0%swapMemory:4096Mreal,735Mfree,2684Mswapinuse,4487MswapfreePIDUSERNAMETHRPRINICESIZERESSTATETIMECPUCOMMAND16820oracle8i1100749M722Mcpu/1289.3H20.79%oracle29230oracle8i11110749M725Msleep1:083.47%oracle5634oracle8i11500749M725Msleep0:362.99%oracle9390oracle8i11590749M723Msleep0:062.67%oracle7338oracle8i11590749M724Msleep0:162.44%oracle6101oracle8i11310749M725Msleep0:121.54%oracle7018oracle8i115901339M1318Msleep0:161.51%oracle5525oracle8i11590749M723Mcpu/90:061.35%oracle9287oracle8i11590749M725Msleep0:041.33%oracle9014oracle8i111501335M1312Mcpu/10:021.14%oracle5728oracle8i15901327M1306Msleep0:101.03%oracle4590oracle8i11400749M725Msleep0:490.96%oracle9376oracle8i11200749M723Msleep0:020.96%oracle8936oracle8i11440749M723Mrun0:030.92%oracle7623oracle8i11590749M725Msleep0:090.90%oracleSarUnix環(huán)境下，執(zhí)行命令查看cpu的消耗：

sar–u采樣頻率執(zhí)行次數(shù)VmstatUnix環(huán)境下，執(zhí)行命令查看隊(duì)列的情況：

vmstat-s55Sar也可以看到i/o的性能Unix環(huán)境下，執(zhí)行命令查看磁盤(pán)的繁忙率：

sar–d如果busy〉60%，那就有I/O問(wèn)題了1、架構(gòu)和業(yè)務(wù)的設(shè)計(jì)與變更2、熟悉各種數(shù)據(jù)庫(kù)參數(shù)、系統(tǒng)參數(shù)3、應(yīng)用邏輯與SQL代碼實(shí)現(xiàn)4、選擇適宜的存儲(chǔ)方式 存儲(chǔ)盤(pán)陣、存儲(chǔ)模式、存儲(chǔ)參數(shù)、存儲(chǔ)表空間、存儲(chǔ)對(duì)象等5、復(fù)雜的網(wǎng)絡(luò)配置還有更多。。。為滿(mǎn)足業(yè)務(wù)的運(yùn)行要求，高性能要求是目前IT系統(tǒng)普遍面臨的最棘手問(wèn)題，尤其是客戶(hù)面對(duì)著目前越來(lái)越龐大系統(tǒng)和數(shù)據(jù)，系統(tǒng)整合、數(shù)據(jù)大集中似乎成了趨勢(shì)，而對(duì)我們DBA來(lái)說(shuō)，那么充滿(mǎn)了壓力和挑戰(zhàn)。性能優(yōu)化面臨的挑戰(zhàn)優(yōu)化怎么做?。啃枰檬值墓ぞ?！Oracle調(diào)優(yōu)工具的變遷朦朧之初(v5)Debugcode初見(jiàn)端倪(v6)Counters/RatiosBSTAT/ESTATSQL*Trace有所開(kāi)展(v7)出現(xiàn)了WaitEventcounters向timers的變遷快速進(jìn)化(8i)寬廣的范圍STATSPACKOracle調(diào)優(yōu)工具的變遷逐漸完善(9i)更精細(xì)的收集-Sessiontuningusing10046SQLtraces更加全面的STATSPACK智能化、自動(dòng)化開(kāi)始初現(xiàn)日趨完美(10g)–基于數(shù)據(jù)庫(kù)自動(dòng)化根底的更完美的優(yōu)化自動(dòng)化收集更加廣泛的收集保存一段時(shí)間的歷史提供了：ASH,AWR,ADDM,EM等功能調(diào)用形成了越來(lái)越完善的性能優(yōu)化診斷工具工欲善其事，必先利其器<InsertPictureHere>診斷工具中的“七”種武器診斷工具中的七種武器碧玉刀—?jiǎng)討B(tài)性能視圖：刀是最群眾化的武器，小到刮刀、折刀、剃刀、西瓜刀、切菜刀、剔骨刀，大到柳葉刀、圓月彎刀、武士刀、青龍偃月刀。。。不管大小長(zhǎng)短、不管古今中外，刀是最常見(jiàn)的武器。不過(guò)再普通的刀，到了高手的手中，也會(huì)成為神兵利器，刀如此，Oracle的動(dòng)態(tài)性能視圖也如此，無(wú)論各種性能問(wèn)題，根源皆可尋究于此。V$SYSSTATV$SESSIONV$SESSTATV$SGASTATV$FILESTATV$UNDOSTATV$ROLLSTATV$WAITSTATV$LOCKV$LATCHV$SQLV$SQLAREAV$SQLTEXTV$PROCESSV$LIBRARYCACHEV$ROWCACHE……所有工具之根本——?jiǎng)討B(tài)性能視圖無(wú)論何種性能問(wèn)題，根源皆可尋究于此大處著眼，小處著手不是每個(gè)問(wèn)題，都那么清楚的擺在我們面前，細(xì)致的觀(guān)察，仔細(xì)的分析，利器才是利器Selectname,valuefromv$sysstatwherenamelike'%Net%';NAMEVALUE

bytessentviaSQL*Nettoclient2.0196E+12bytesreceivedviaSQL*Netfromclient1.3342E+12SQL*Netroundtripsto/fromclient7397997982bytessentviaSQL*Nettodblink1.5108E+12bytesreceivedviaSQL*Netfromdblink1.1800E+11MemoryV$BUFFER_POOL_STATISTICST/PV$DB_OBJECT_CACHETV$LIBRARYCACHEPV$ROWCACHE PV$SYSSTAT

T/PV$SGASTATPDiskV$DATAFILET/PV$FILESTAT

T/PV$LOGTV$LOG_HISTORYTV$DBFILET/PV$TEMPFILEPV$TEMPSTATPInstance/DatabaseV$DATABASETV$INSTANCETV$OPTION TV$PARAMETER

T/PV$BACKUPTV$PX_PROCESS_SYSSTATT/PV$PROCESSTV$WAITSTATT/PV$SYSTEM_EVENT

T/PContentionV$LOCK

T/PV$ROLLNAMET/PV$ROLLSTATT/PV$WAITSTATT/PV$LATCH

T/PUser/SessionV$LOCKPV$OPEN_CURSORTV$PROCESSTV$SORT_USAGET/PV$SESSION

T/PV$SESSTATT/PV$TRANSACTION

TV$SESSION_EVENT

T/PV$SESSION_WAIT

T/PV$PX_SESSTATPV$PX_SESSIONPV$SQL

p故障診斷和調(diào)優(yōu)的相關(guān)視圖系統(tǒng)級(jí)的統(tǒng)計(jì)會(huì)話(huà)相關(guān)的統(tǒng)計(jì)T:TroubleshootingT/P:Troubleshooting/Performance系統(tǒng)級(jí)統(tǒng)計(jì)信息v$sysstat中統(tǒng)計(jì)信息根據(jù)以下類(lèi)別劃分:Class1–userClass2-redologbuffer信息Class4–鎖Class8–數(shù)據(jù)庫(kù)buffercache信息Class16-OSClass32–并行Class64–表訪(fǎng)問(wèn)Class128–調(diào)試目的統(tǒng)計(jì)信息類(lèi)別SQL>select*fromv$sysstatwhereclass=&classid;&classid:2STATISTIC#NAMECLASSVALUE

114redoentries2215115redosize261060116redobufferallocationretries20117redowastage227212118redowriterlatchingtime20119redowrites278120redoblockswritten2178121redowritetime221122redologspacerequests20123redologspacewaittime20124redologswitchinterrupts20125redoorderingmarks2012rowsselectedSortSQL>select*fromv$sysstatwherenamelike'sort%';STATISTIC#NAMECLASSVALUE

242sorts(memory)64351243sorts(disk)640244sorts(rows)648935Session級(jí)視圖SQL>selectsid,serial#,machine,program,server2fromv$session;SIDSERIAL#MACHINEPROGRAMSERVER

11TESTORACLE.EXEDEDICATED21TESTORACLE.EXEDEDICATED31TESTORACLE.EXEDEDICATED41TESTORACLE.EXEDEDICATED51TESTORACLE.EXEDEDICATED61TESTORACLE.EXEDEDICATED73WORKGROUP\TESTsqlplusw.exeDEDICATED917WORKGROUP\TESTPLSQLDev.exeDEDICATED115WORKGROUP\TESTPLSQLDev.exeDEDICATED9rowsselectedUNIX系統(tǒng)捕獲SQLUNIX多進(jìn)程Server，通過(guò)ps命令可以看到pidselectsql_fulltextfromv$sqlawhere(a.hash_value,a.address)=(selectsql_hash_value,sql_addressfromv$sessionbwhereb.paddr=(selectaddrfromv$processcwherec.spid='&spid'))/診斷工具中的七種武器長(zhǎng)生劍—等待事件：劍，輕靈、快速、靈敏，甚至于詭異。談笑間，輕松快意時(shí)尋出敵人弱點(diǎn)，以閃電般的速度刺入敵人最虛弱的部位，一擊破敵。 性能優(yōu)化的核心是什么，快速準(zhǔn)確的定位，不需要華美的顯示，不需要冗長(zhǎng)的信息，需要的僅僅是一個(gè)準(zhǔn)確的定位，等待事件就是此中利器。競(jìng)爭(zhēng)即等待Oracle的等待事件等待事件的集合提供了會(huì)話(huà)中不得不等待和必須等待的原因.這些等待事件被列在V$EVENT_NAME視圖中,具有下面這些列:EVENT#NAMEPARAMETER1PARAMETER2PARAMETER3V$EVENT_NAME視圖NAMEPARAMETER1PARAMETER2PARAMETER3

PL/SQLlocktimerdurationaltersystemsetmts_dispatcherwaitedbufferbusywaitsfile#block#idlibrarycachepinhandleaddrpinaddress0*mode+namelogbufferspacelogfileswitch(checkpointincomplete)transactionundoseg#wrap#count...286rowsselected.SQL>SELECTname,parameter1,parameter2,parameter32FROMv$event_name;等待事件兩類(lèi)等待事件：空閑等待：oracle正在等待某種動(dòng)作的發(fā)生clientmessage(客戶(hù)機(jī)消息)、NULLevent〔NULL事件〕、pipeget〔管道取操作〕、SQL*NETmessagefromclient〔來(lái)自客戶(hù)端的消息〕、rdbmsipcmessage(數(shù)據(jù)庫(kù)icp消息)等非空閑等待事件：數(shù)據(jù)庫(kù)發(fā)生了競(jìng)爭(zhēng)bufferbusywaits(數(shù)據(jù)高速緩存忙等待)、dbfilescatteredread(數(shù)據(jù)文件離散讀取)、dbfilesequentialread(數(shù)據(jù)文件順序讀)、enqueue(隊(duì)列)、freebufferwaits(空閑緩沖區(qū)等待)、latchfree(拴空閑)、logfileparallelwrite(日志文件并行寫(xiě)入)、logfilesync(日志文件同步)等等待事件的相關(guān)視圖V$SYSTEM_EVENT:一個(gè)事件在所有會(huì)話(huà)的匯總統(tǒng)計(jì)V$SESSION_EVENT:每個(gè)會(huì)話(huà)等待一個(gè)事件的總的統(tǒng)計(jì)V$SESSION_WAIT:當(dāng)前激活會(huì)話(huà)正在等待的事件尋找第一眼的感覺(jué)從v$system_event中發(fā)現(xiàn)系統(tǒng)問(wèn)題從v$session_event中發(fā)現(xiàn)會(huì)話(huà)問(wèn)題從v$session_wait的參數(shù)中找到競(jìng)爭(zhēng)對(duì)象等待事件熟悉各種主要的等待事件,快速定位問(wèn)題所在Top5TimedEvents

Avg%Total

~~~~~~~~~~~~~~~~~~

wait

Call

Event

Waits

Time(s)

(ms)

TimeWaitClass

waitforaundorecord

35,928

3,451

65

50.1

Other

CPUtime

1,687

24.4

dbfilescatteredread392,50482111.7 UserI/O

waitforstoppereventtobei

4,027

278

69

3.4

Other

logfilesync 28,880171 2.1Commitwaitforaundorecord等待與回滾段的大量回滾有關(guān)，一般是出現(xiàn)了大事務(wù)回退造成selectsid,event,wait_classfromv$session_waits

wheres.eventnotlike‘%message%’;

SID

EVENT

WAIT_CLASS

507

PXDeq:TxnRecoveryStart

Idle

511

PXDeq:TxnRecoveryStart

Idle

268

PXDeq:TxnRecoveryStart

Idle

……selectpid,state,undoblocksdonefromv$fast_start_servers;

PIDSTATE

UNDOBLOCKSDONE

133RECOVERING

7124

……FAST_START_PARALLEL_ROLLBACKV$SYSTEM_EVENT視圖EVENT TOTAL_ TOTAL_ TIME_ AVERAGE_ WAITS TIMEOUTS WAITED WAIT

latchfree 5 5 5 1pmontimer 932 535 254430 272.993562processstartup 3 8 2.66666667bufferbusywaits 12 0 5 5...34rowsselected.

SQL>SELECTevent,total_waits,total_timeouts,2time_waited,average_wait3FROMv$system_event; V$SESSION_EVENT視圖SIDEVENTTOTAL_WAITSAVERAGE_WAIT

10bufferbusywaits12510dbfilesequentialread129010fileopen1010SQL*Netmessagetoclient77010SQL*Netmoredatatoclient2010SQL*Netmessagefromclient760SQL>selectsid,event,total_waits,average_wait2>fromv$session_eventwheresid=10;V$SESSION_WAIT視圖SIDSEQ# EVENT WAIT STATE TIME --- 1 1284 pmontimer 0 WAITING 2 1697 rdbmsipcmessage 0 WAITING 3 183 rdbmsipcmessage 0 WAITING 4 4688 rdbmsipcmessage 0 WAITING 5 114 smontimer 0 WAITING 6 14 SQL*Netmessagefromclient -1 WAITED SHORT TIMESQL>SELECTsid,seq#,event,wait_time,state2 FROMv$session_wait;通過(guò)等待事件判斷數(shù)據(jù)庫(kù)的問(wèn)題Select*fromV$SYSTEM_EVENTWhereEventin('bufferbusywaits','dbfilesequentialread', 'dbfilescatteredread','enqueue','freebufferwaits', 'latchfree','logfileparallelwrite','logfilesync');EVENTTOTAL_WAITSTOTAL_TIMEOUTSTIME_WAITEDAVERAGE_WAITLatchfree23656323049441893.177090247Enqueue42440343.808962264Freebufferwaits42323561282016.66375236Bufferbusywaits8943772502181907.203389622Logfileparallelwrite39385480804870.204357037Logfilesync1890409890544425.287993233Dbfilesequentialread6276963503118192464.96767658Dbfilescatteredread1792863403843986.214404845通過(guò)等待事件判斷數(shù)據(jù)庫(kù)的問(wèn)題通過(guò)等待事件判斷數(shù)據(jù)庫(kù)的問(wèn)題通過(guò)等待事件判斷數(shù)據(jù)庫(kù)的問(wèn)題通過(guò)等待事件判斷數(shù)據(jù)庫(kù)的問(wèn)題bufferbusywaits(數(shù)據(jù)高速緩存忙等待)dbfileparallelwrite(數(shù)據(jù)文件并行寫(xiě))dbfilescatteredread(數(shù)據(jù)文件離散讀取)dbfilesequentialread(數(shù)據(jù)文件順序讀)dbfilesinglewrite(數(shù)據(jù)文件單次寫(xiě))directpathread(直接路徑讀取)directpathwrite(直接路徑寫(xiě)出)enqueue(隊(duì)列)freebufferwaits(空閑緩沖區(qū)等待)latchfree(鎖存器空閑)常見(jiàn)的等待事件logbufferspace(日志緩沖區(qū)空間分配)logfileparallelwrite(日志文件并行寫(xiě)入)logfilesinglewrite(日志文件單次寫(xiě))logfileswitch(archivingneeded)logfileswitch(checkpointincomplete)logfilesync(日志文件同步)timerinsksawat(歸檔過(guò)慢)undosegmentextension(回滾段動(dòng)態(tài)擴(kuò)展)常見(jiàn)的等待事件診斷工具中的七種武器霸王槍—statspack：槍中之霸王，膽氣之結(jié)晶。槍具有劍的輕靈，又有棍的霸道，不管是快速定位，還是全面分析，都是statspack所能勝任的。Statspack使用方法設(shè)置TIMED_STATISTICS為T(mén)RUE執(zhí)行spcreate.sql安裝Statspack，只需要安裝一次運(yùn)行應(yīng)用程序在業(yè)務(wù)繁忙期手動(dòng)收集統(tǒng)計(jì)信息statspack.snap或者自動(dòng)收集統(tǒng)計(jì)信息spauto.sql在收集到一定量的統(tǒng)計(jì)信息后，產(chǎn)生報(bào)告spreport.sql

Statspack的輸出包含的信息：數(shù)據(jù)庫(kù)和實(shí)例名稱(chēng)獲取快照的時(shí)間當(dāng)前高速緩存的大小負(fù)載概覽實(shí)例效率百分比前五個(gè)等待事件等待事件的完整列表共享池中SQL語(yǔ)句的信息實(shí)例活動(dòng)統(tǒng)計(jì)表空間和文件I/O緩沖區(qū)統(tǒng)計(jì)信息回退段或復(fù)原段統(tǒng)計(jì)信息栓鎖活動(dòng)字典高速緩存統(tǒng)計(jì)信息庫(kù)高速緩存統(tǒng)計(jì)SGA統(tǒng)計(jì)Init.ora參數(shù)的啟動(dòng)值Statspack內(nèi)容Statspack內(nèi)容Statspack內(nèi)容Statspack內(nèi)容診斷工具中的七種武器孔雀翎—ash、awr、addm、addr：是一種暗器，但又不是暗器。悄然，靈動(dòng)，不動(dòng)聲色間，一切皆在握。Oracle在10g開(kāi)始，推出了一系列自動(dòng)化、智能化的工具，雖然這些工具在以前或多或少都有相似的影子，但功能的增強(qiáng)、理念的增強(qiáng)，造就了這些以前所不具備的新工具。ActiveSessionHistory-活動(dòng)會(huì)話(huà)歷史查找數(shù)據(jù)庫(kù)的瞬間問(wèn)題每秒鐘自動(dòng)從內(nèi)存中抓取樣例的活動(dòng)會(huì)話(huà)信息可以從v$active_session_history獲得會(huì)話(huà)近期的活動(dòng)信息selecta.sql_textfromv$sqla

wheresql_idin(selectsql_idfromv$active_session_historywheresession_id=157);信息直接從內(nèi)存結(jié)構(gòu)中獲取，并不保存，僅在系統(tǒng)運(yùn)行中有效可以得到SIDSQLIDProgramWaitevent#Object,File,Blockactualwaittime(ifcapturedwhilewaiting)通過(guò)ashrpt.sql可以產(chǎn)生ash分析報(bào)告，發(fā)現(xiàn)某個(gè)時(shí)段的TOP

(TopEvents/TopSQL/TopSessions/TopObjects/Files/Latches)可以通過(guò)活動(dòng)會(huì)話(huà)信息追溯到性能問(wèn)題的根源ASH報(bào)告ASH報(bào)告ASH報(bào)告活動(dòng)會(huì)話(huà)信息什么資源在競(jìng)爭(zhēng)？向下追溯到哪個(gè)程序帶來(lái)了競(jìng)爭(zhēng)？以及哪個(gè)SQL帶來(lái)了競(jìng)爭(zhēng)？活動(dòng)會(huì)話(huà)信息中的TOP通過(guò)TOPSQL進(jìn)一步發(fā)現(xiàn)問(wèn)題自動(dòng)負(fù)載信息庫(kù)

(AutomaticWorkloadRepository)自動(dòng)負(fù)載

信息庫(kù)效勞器

警告自動(dòng)化任務(wù)咨詢(xún)

框架數(shù)據(jù)倉(cāng)庫(kù)訪(fǎng)問(wèn)重要統(tǒng)計(jì)信息的

自動(dòng)收集直接內(nèi)存

訪(fǎng)問(wèn)自動(dòng)主動(dòng)高效10g的數(shù)據(jù)庫(kù)中內(nèi)置了工作負(fù)載信息庫(kù)AWR是Oracle10g數(shù)據(jù)庫(kù)自動(dòng)化管理的根底架構(gòu)由MemoryMonitor(MMON)進(jìn)程自動(dòng)捕獲工作負(fù)載數(shù)據(jù)默認(rèn)情況下，每隔60分鐘保存一次，保存7天的數(shù)據(jù)存儲(chǔ)在新SYSAUX表空間內(nèi)效勞器自動(dòng)管理空間要求自動(dòng)去除舊數(shù)據(jù)存儲(chǔ)不同類(lèi)別的數(shù)據(jù)：根本統(tǒng)計(jì)，例如物理讀取SQL統(tǒng)計(jì)，例如磁盤(pán)讀取〔每個(gè)sql語(yǔ)句〕量度，例如，物理讀取數(shù)量/秒通過(guò)awrrpt.sql可以產(chǎn)生與statspack類(lèi)似的性能差異報(bào)告AutomaticWorkloadRepository-

自動(dòng)負(fù)載信息庫(kù)(AWR)自動(dòng)負(fù)載信息庫(kù)SGA內(nèi)存中的統(tǒng)計(jì)信息AWR

統(tǒng)計(jì)信息ASHSYSAUXMMON6:00a.m.7:00a.m.8:00a.m.WR模式快照1快照2快照3快照49:00a.m.9:30amADDM發(fā)現(xiàn)最重要的問(wèn)題BGBG…FGFG…DBA七天自動(dòng)負(fù)載信息庫(kù)修改采樣設(shè)置手工修改你可以修改采樣頻度和數(shù)據(jù)保存周期，DBMS_WORKLOAD_REPOSITORY可以用來(lái)完成這個(gè)工作:executedbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>43200);注:間隔為60分鐘，保存30天(60*24*30=43200)查看維護(hù)快照信息快照詳細(xì)資料生成報(bào)告報(bào)告內(nèi)容ADDM-AutomaticDatabaseDiagnosticMonitoring自動(dòng)負(fù)載信息庫(kù)(AWR)，它定期從數(shù)據(jù)庫(kù)中收集詳細(xì)的與性能相關(guān)的度量標(biāo)準(zhǔn)。有了AWR以后，Oracle又引入了ADDM輔助數(shù)據(jù)庫(kù)性能診斷ADDM自動(dòng)比較每次AWR采樣信息和先前的兩次采樣信息可以輕易的發(fā)現(xiàn)CPU及I/O瓶頸，以及SQL問(wèn)題或者Latch競(jìng)爭(zhēng)AutomaticDatabaseDiagnosticMonitor-

自動(dòng)數(shù)據(jù)庫(kù)診斷監(jiān)控(ADDM)由Oracle自動(dòng)調(diào)用，也可以手動(dòng)調(diào)用可以分析當(dāng)前的，最近一次收集的，也可以分析之前還存在的AWR快照對(duì)RAC架構(gòu)同樣適用分析結(jié)果在數(shù)據(jù)庫(kù)的相應(yīng)字典表中存儲(chǔ)，可以通過(guò)dbms_advisor