Oracle數(shù)據(jù)庫(kù)性能優(yōu)化方法論

1、Oracle數(shù)據(jù)庫(kù)性能優(yōu)化方法論技術(shù)創(chuàng)新，變革未來智慧IT目錄性能優(yōu)化方法論1-1性能優(yōu)化和資源性能優(yōu)化工具講解1-21-3什么是OWIOWI ORACLE WAIT INTERFACE:等待事件 ORACLE 7 :104個(gè)ORACLE 8:140個(gè)，ORACLE 8I:220 ORACLE9I:400ORACLE10G:800 ORACLE11G:1367 ORACLE12c:OWI性能指標(biāo)性能指標(biāo)：響應(yīng)時(shí)間：從發(fā)出指令之后到收到結(jié)果之間消逝的時(shí)間響應(yīng)時(shí)間（Response Time）=工作時(shí)間（service Time）+等待時(shí)間（wait time） 只要降低工作時(shí)間和等待時(shí)間，響應(yīng)時(shí)

2、間自然隨之降低，并增加用戶滿意度吞吐量：固定時(shí)間之內(nèi)可以完成的工作量OWI特點(diǎn)OWI ORACLE WAIT INTERFACEOWI是面向問題的OWI是定量的OWI是征兆學(xué)的OWI是不斷進(jìn)步完善的OWI 最強(qiáng)有力的表現(xiàn)形式-AWR報(bào)告OWI重要體現(xiàn)視圖V$SESSIONV$ACTIVE_SESSION_HISTORYV$PROCESSV$TRANSACTIONV$LOCK,V$LOCK_OBJECTV$SQL,V$SQLTEXTV$DATABASEV$DATAFILESelect table_name from dict where table_name like%SESSION%;OWI事

3、件之高速緩沖區(qū)什么是高速緩沖區(qū)相關(guān)等待事件：Latch cache buffer chains 2.Latch cache buffer lru chains3.Buffer busy wait/read by other session 4.Write complete waits5.Free buffer waitsOWI事件之庫(kù)高速緩沖區(qū)1.什么是庫(kù)高速緩沖區(qū)相關(guān)等待事件： 1.Latch:shared_pool 2.Latch:library cache3.Library cache lock/library cache pinOWI事件之行高速緩沖區(qū)1.什么是行高速緩沖區(qū)相關(guān)等待事件

4、： 1.Row cache lock 2.enq:SQ-contention 3.DFS lock handleOWI事件事務(wù)上的等待事務(wù)運(yùn)行過程中可能的等待：1.enq:TM-contention 2.enq:TX-row lock contention 3.enq:TX-allocate ITL Entry4.enq:TX-index contentionOWI事件段上的等待段在數(shù)據(jù)庫(kù)運(yùn)行過程中可能的等待：1.enq:HW-contention 2.enq:ST-contention 3.enq:TT-contention4.enq:US-contentionOWI事件IO上的等待體現(xiàn)在I

5、O上的等待： 1.db file scattered read 2.db file sequential read 3.Direct path read 4.Direct path write5.db file parallel write 6.Control file parallel writeOWI事件在重做緩沖區(qū)上的等待體現(xiàn)在REDO上的等待1.latch:redo writing,latch:redo allocation,latch:redo copy 2.Log file sync3.Log file parallel write 4.Log buffer space5.Log

6、file switch completion,log file switch,checkpoint incompleteOWI事件在網(wǎng)絡(luò)上的等待體現(xiàn)在REDO上的等待1.SQL*Net message from/to client 1.SQL*Net more data from/to client 1.SQL*Net message from/to dblink1.SQL*Net more data from/to dblink性能優(yōu)化和資源目錄1-2性能優(yōu)化工具講解1-1性能優(yōu)化方法論1-3性能優(yōu)化方法論資源ORACLE數(shù)據(jù)庫(kù)的性能依賴于數(shù) 據(jù)庫(kù)所在的硬件及操作系統(tǒng)能 上，因此，我們?cè)谠\斷

7、性能問 題的時(shí)候，應(yīng)將操作系統(tǒng)資源 指標(biāo)作為整體性能指標(biāo)的一部 分。定位影響性能的主要資源：查看CPU資源查看內(nèi)存資源查看IO資源CPU資源內(nèi)存資源I/O資源性能優(yōu)化方法論-CPUCPU資源:CPU資源是否緊張我們可以通過檢查CPU的利用率及等待運(yùn)行的進(jìn)程數(shù)來了解，一般來 說CPU 的運(yùn)算速度主要受主頻和高低緩存大小的影響。OLAP系統(tǒng)由于進(jìn)程數(shù)量少，所以其性能和CPU的頻率關(guān)系較大。OLTP系統(tǒng)由于進(jìn)程數(shù)量多，所以其性能和CPU的數(shù)量關(guān)系較大。Oracle會(huì)根據(jù)系統(tǒng)的CPU數(shù)量自動(dòng)調(diào)節(jié)參數(shù)（CPU_COUNT）和進(jìn)程數(shù)量（LMS進(jìn)程等）。提示：CPU使用率不是越低越好，我們需要做到的是，在不

8、出現(xiàn)資源阻塞的情況下，充分發(fā)揮CPU資源的能力性能優(yōu)化方法論-CPUCPU資源緊張的征兆：操作系統(tǒng)IDLE很低，等待隊(duì)列很高可以通過vmstat,top/topas等工具觀察性能優(yōu)化方法論-CPU資源緊張?jiān)騍QL執(zhí)行計(jì)劃異常,大量全表掃描latch（內(nèi)存鎖）或者mutex爭(zhēng)用高并發(fā)的SQL解析CPU硬件資源不足bug性能優(yōu)化方法論內(nèi)存內(nèi)存資源操作系統(tǒng)內(nèi)存消耗主要用 于數(shù)據(jù)庫(kù)（SGA）和進(jìn)程 消耗（PGA）,為計(jì)算型內(nèi) 存資源。性能優(yōu)化方法論內(nèi)存內(nèi)存不足的癥狀系統(tǒng)產(chǎn)生大量交換，進(jìn)一步可能導(dǎo)致本地硬盤100% 繁忙嚴(yán)重情況下，會(huì)導(dǎo)致系統(tǒng)無法響應(yīng)（登錄）。在RAC中，由于進(jìn)程長(zhǎng)時(shí)間得不到內(nèi)存派生，

9、則容易引起腦裂（Brain split）性能優(yōu)化方法論內(nèi)存內(nèi)存不足常見的原因SGA設(shè)置過大操作系統(tǒng)參數(shù)設(shè)置不合理，如AIX 5L的maxperm%大量的連接進(jìn)程PGA異常增長(zhǎng)RMAN備份內(nèi)存硬件資源不足bugAIX 5L的maxperm參數(shù)設(shè)置性能優(yōu)化方法論-IO存儲(chǔ)優(yōu)化吞吐量和IOPS是考察存儲(chǔ)性能的兩個(gè)主要指標(biāo)。傳統(tǒng)存儲(chǔ)的最終瓶頸在于磁盤尋道時(shí)間，轉(zhuǎn)速越快，磁盤數(shù)量越多，存儲(chǔ)性能越高。固態(tài)硬盤是很重要的里程碑，是解決磁盤瓶頸的重要技術(shù)。性能優(yōu)化方法論-IO存儲(chǔ)異常癥狀硬件錯(cuò)誤，如存儲(chǔ)不可寫。這類錯(cuò)誤往往會(huì)在警告日志錯(cuò)報(bào)錯(cuò)。存儲(chǔ)性能緩慢，該類錯(cuò)誤需要DBA預(yù)估值，相對(duì)比較難發(fā)現(xiàn)。性能優(yōu)化方法

10、論-IO導(dǎo)致性能的原因分析SQL執(zhí)行計(jì)劃異常，如大量的全表掃描。DB CACHE配置不合理（過?。?。異步I/O參數(shù)配置不足。存儲(chǔ)硬件配置不足。條帶設(shè)計(jì)不合理。訪問熱點(diǎn)。BUG。硬件錯(cuò)誤磁盤隊(duì)列深度不足硬件本身如轉(zhuǎn)速較低性能優(yōu)化方法論-IO木桶原理：“木桶效應(yīng)”指的是一個(gè)由長(zhǎng)短 不同的木板組成的木桶，決定其 水容量的大小的并非木桶中最長(zhǎng) 的一塊木板或者所有木板的平均 值，而是取決于最短的那塊木板性能優(yōu)化工具講解性能優(yōu)化和資源目錄1-31-1性能優(yōu)化方法論1-2性能優(yōu)化方法論工具自動(dòng)化性能優(yōu)化工具自動(dòng)化性能優(yōu)化是一個(gè)趨勢(shì)。但是Oracle的建議只能當(dāng)做一個(gè)工具。在越來越自動(dòng)化的今天，對(duì)DBA要求其

11、實(shí)更高了。性能優(yōu)化方法論工具性能優(yōu)化的三大利器AWR(Automatic Workload Repository)AWR是Oracle 10g中的一個(gè)新特性，類似于10g以前的statspack。不過在使用上 要比statspack簡(jiǎn)單，提供的性能指標(biāo)要比statspack多很多，能更好的幫助DBA來發(fā)現(xiàn)數(shù)據(jù)庫(kù)的性能瓶頸ASH (Active Session History)ASH以V$SESSION為基礎(chǔ)，每秒采樣一次，記錄活動(dòng)會(huì)話等待的事件。不活動(dòng)的會(huì)話 不會(huì)采樣，采樣工作由新引入的后臺(tái)進(jìn)程MMNL來完成$ORACLE_HOME/rdbms/admin/rdbms/ashrpt.sqlAD

12、DM (Automatic Database Diagnostic Monitor AWR)是Oracle內(nèi)部的一個(gè)顧問系統(tǒng)，能夠自動(dòng)的完成最數(shù)據(jù)庫(kù)的一些優(yōu)化的建議，給出SQL的優(yōu)化，索引的創(chuàng)建，統(tǒng)計(jì)量的收集等建議什么是AWRAWR (Automatic Workload Repository)一堆歷史性能數(shù)據(jù)，放在 SYSAUX 表空間上， AWR 和 SYSAUX 都是 10g 出 現(xiàn)的，是 Oracle 調(diào)優(yōu)的關(guān)鍵特性；大約 1999 年左右開始開發(fā)，已經(jīng)有 15 年歷史默認(rèn)快照間隔 1 小時(shí)， 10g 保存 7 天、 11g 保存 8 天; 可以通過DBMS_WORKLOAD_REPO

13、SITORY.MODIFY_SNAPSHOT_SETTINGS 修改AWR 程序核心是 dbms_workload_repository 包 運(yùn)行腳本：?/rdbms/admin/awrrpt 本實(shí)例?/rdbms/admin/awrrpti RAC 中選擇實(shí)例號(hào)手動(dòng)執(zhí)行一個(gè)快照：exec dbms_workload_repository.create_snapshot;性能優(yōu)化方法論AWR詳解AWR使用方法生成報(bào)告命令：SQL?/rdbms/admin/awrrpt.sql-普通AWR報(bào)告 SQL?/rdbms/admin/awrgrpt.sql-采集RAC集群報(bào)告 SQL?/rdbms/a

14、dmin/awrrpti.sql-采集RAC二節(jié)點(diǎn)報(bào)告報(bào)告匯總信息，涵蓋最基本也是最直觀的AWR新能信息， 包括內(nèi)存配置，參數(shù)指標(biāo)，數(shù)據(jù)庫(kù)命中率，等待事件等高負(fù)載SQL，按CPU 處理時(shí)間，邏輯讀，物理讀，執(zhí)行次 數(shù)，解析次數(shù)，高版本等多個(gè)方面展現(xiàn)數(shù)據(jù)庫(kù)高負(fù)載SQL數(shù)據(jù)庫(kù)指標(biāo)統(tǒng)計(jì)，涵蓋IO，內(nèi)存advisory，等待事件，undo統(tǒng)計(jì)，latch 統(tǒng)計(jì)參數(shù)文件內(nèi)容，包括各種系統(tǒng)參數(shù)，隱含參數(shù)，event等的設(shè)置性能優(yōu)化方法論AWR詳解AWR使用方法性能優(yōu)化方法論AWRLoad Profile部分指標(biāo)定義redo size單位 bytes， redo size 可以用來估量 update/ins

15、ert/delete 的頻率，大的 redo size 往往對(duì) lgwr 寫日志，和 arch 歸檔造成 I/O 壓力， Per Transaction 可以用來分 辨是 大量小事務(wù)， 還是少量大事務(wù)。如上例每秒 redo 約 1MB ，每個(gè)事務(wù) 800 字節(jié)，符合 OLTP 特征LogicalRead單位 次數(shù)*塊數(shù)，邏輯讀耗CPU，主頻和 CPU 核數(shù)都很重要，邏輯讀高則 DB CPU 往往高，也往往可以看到 latch: cache buffer chains 等待。 大量 OLTP 系統(tǒng) (例如 siebel)可以高達(dá)幾十乃至上百 GbytesBlock changes單位 次數(shù)*塊數(shù)

16、，描繪數(shù)據(jù)變化頻率Physical Read單位次數(shù)*塊數(shù)，物理讀消耗 IO 讀，體現(xiàn)在 IOPS 和吞吐量等不同緯度上；但 減少物理讀可能意味著消耗更多CPU。好的存儲(chǔ)每秒物理讀能力達(dá)到幾GB， 例如Exadata。 這個(gè)physical read 包含了 physical reads cache 和 physical reads direct性能優(yōu)化方法論AWRLoad Profile部分指標(biāo)定義Physicalwrites單位 次數(shù)*塊數(shù)，主要是 DBWR 寫 datafile，也有 direct path write。 dbwr長(zhǎng)期寫出慢會(huì)導(dǎo)致定期 log file switch(ch

17、eckpoint no complete) 檢查點(diǎn)無法完成的前臺(tái)等待。 這個(gè) physical write包含了 physical writes direct +physical writes from cacheUser Calls單位次數(shù)，用戶調(diào)用數(shù)， more details from internalParses解析次數(shù)，包括軟解析+硬解析，軟解析優(yōu)化得不好，則夸張地說幾乎等于每秒 SQL 執(zhí)行次數(shù)。 即執(zhí)行解析比 1:1，而我們希望的是 解析一次 到處運(yùn)行哦！Logons登陸次數(shù)， logon storm 登陸風(fēng)暴，結(jié)合 AUDIT 審計(jì)數(shù)據(jù)一起看。短連接 的附帶效應(yīng)是游標(biāo)緩存無用E

18、xecutes執(zhí)行次數(shù)，反應(yīng)執(zhí)行頻率性能優(yōu)化方法論AWRLoad Profile部分指標(biāo)定義Rollback回滾次數(shù)， 反應(yīng)回滾頻率， 但是這個(gè)指標(biāo)不太精確，參考而已，別太當(dāng) 真Transactions每秒事務(wù)數(shù)，是數(shù)據(jù)庫(kù)層的 TPS，可以看做壓力測(cè)試或比對(duì)性能時(shí)的一個(gè)指標(biāo)，孤立看無意義% Blocks changed per Read每次邏輯讀導(dǎo)致數(shù)據(jù)塊變化的比率；如果redo size, =block changes =pct of blocks changed perread三個(gè)指標(biāo)都很高，則說明系統(tǒng)正執(zhí)行大量 insert/update/delete;pct of blocks cha

19、nged per read = (block changes ) /( logical reads)Load Profile,負(fù)載指標(biāo) 在本環(huán)節(jié)提供了2個(gè)維度per second 和per transactionper Second:主要是把快照內(nèi)的delta值除以快照時(shí)間的秒數(shù)，是我們審視數(shù)據(jù)的主 要維度，任何性能數(shù)據(jù)脫離了時(shí)間模型則毫無意義。per transaction : 基于事務(wù)的維度， 與 per second 相比是把除數(shù)從時(shí)間的秒數(shù)改 為了該段時(shí)間內(nèi)的事務(wù)數(shù)性能優(yōu)化方法論AWR命中率部分：上述所有指標(biāo)的目標(biāo)均為 100%，即越大越好，在少數(shù) bug 情況下可能超過 100%或者

20、為負(fù)值Buffer Nowait % 會(huì)話申請(qǐng)一個(gè) buffer(兼容模式)不等待的次數(shù)比例。 需要訪問 buffer 時(shí)立即可以訪問的比率，不兼容的情況在9i 中是buffer busy waits，從 10g 以 后 buffer busy waits 分離為 buffer busy wait 和 readby other session2 個(gè)等待事件buffer HIT%: 經(jīng)典的經(jīng)典，高速緩存命中率，反應(yīng)物理讀和緩存命中間的糾結(jié)， 但這個(gè)指標(biāo)即便 99% 也不能說明物理讀等待少了性能優(yōu)化方法論AWRRedo nowait%: 會(huì)話在生成redo entry時(shí)不用等待的比例，redo 相

21、關(guān)的資源爭(zhēng)用例如redo space request 爭(zhēng)用可能造成生成redo 時(shí)需求等待。此項(xiàng)數(shù)據(jù)來源于v$sysstat 中的(redo log space requests/redo entries)，一般來說10g 以后不太用關(guān)注log_buffer 參數(shù)的大小，需要關(guān) 注是否有十分頻繁的 log switch ，過小的 redo logfilesize 如果配合較大的 SGA 和頻繁的 commit 提交都可能造成該問題。 考慮增到 redo logfile 的尺寸 : 14G 每個(gè)，710 組都 是合適的。同時(shí)考慮優(yōu)化 redo logfile 和 datafile 的 I/OIn

22、-memory Sort%:這個(gè)指標(biāo)因?yàn)樗挥?jì)算 workarea 中所有的操作類型，所以現(xiàn)在越來越雞 肋了，純粹在內(nèi)存中完成的排序比例。Library Hit%: library cache 命中率，申請(qǐng)一個(gè)library cache object 例如一個(gè) SQL cursor 時(shí)，其已經(jīng)在library cache 中的比例Soft Parse: 軟解析比例，無需多說的經(jīng)典指標(biāo)Execute to Parse% 指標(biāo)反映了執(zhí)行解析比 其公式為 1-(parse/execute) , 目標(biāo)為 100% 及接近于只執(zhí)行而不解析性能優(yōu)化方法論AWR命中率部分：Latch Hit%: willi

23、ng-to-wait latch 閂申請(qǐng)不要等待的比例。Parse CPU To Parse Elapsd:該指標(biāo)反映了 快照內(nèi)解析 CPU 時(shí)間和總的解析時(shí)間的比 值(Parse CPU Time/ Parse Elapsed Time)； 若該指標(biāo)水平很低，那么說明在整個(gè)解 析過程中實(shí)際在 CPU 上運(yùn)算的時(shí)間是很短的，而主要的解析時(shí)間都耗費(fèi)在各種其 他非空閑的等待事件上了%Non-Parse CPU 非解析cpu 比例，公式為 (DB CPU Parse CPU)/DB CPU， 若大多 數(shù) CPU 都用在解析上了，則資源存在沒有合理利用現(xiàn)象性能優(yōu)化方法論AWRTOP等待部分：性能優(yōu)化方

24、法論AWR時(shí)間模型部分：parse time elapsed、 hard parse elapsed time 結(jié)合 起來看解析是否是主要矛盾，若是則重點(diǎn)是軟解 析還是硬解析sequence load elapsed time sequence 序列爭(zhēng)用是否是問題焦點(diǎn)PL/SQL compilation elapsed time PL/SQL 對(duì)象編譯 的耗時(shí)注意PL/SQL execution elapsed time 純耗費(fèi)在 PL/SQL 解釋器上的時(shí)間。不包括花在執(zhí)行和解析 其包含SQL 上的時(shí)間connection management call elapsed time 建立數(shù)據(jù) 庫(kù) session 連接和斷開的耗時(shí)failed parse elapsed time 解析失敗，例如由于ORA-4031hard parse (sharing criteria)