Oracle數(shù)據(jù)庫(kù)培訓(xùn)(高級(jí))課件

培訓(xùn)-Oracle性能分析及一般故障排查培訓(xùn)-Oracle性能分析及一般故障排查培訓(xùn)目標(biāo)Oracle性能基本分析手段OracleSQL優(yōu)化基本知識(shí)Oracle具體案例處理培訓(xùn)目標(biāo)Oracle性能基本分析手段切入點(diǎn)Linux系統(tǒng)可以通過top命令查看系統(tǒng)的CPU、內(nèi)存、運(yùn)行時(shí)間、交換分區(qū)、執(zhí)行的線程等信息。通過top命令可以有效的發(fā)現(xiàn)系統(tǒng)的缺陷出在哪里。是內(nèi)存不夠、CPU處理能力不夠、IO讀寫過高。切入點(diǎn)Linux系統(tǒng)可以通過top命令查看系統(tǒng)的CPU、內(nèi)存切入點(diǎn)切入點(diǎn)切入點(diǎn)切入點(diǎn)

OracleSQL性能優(yōu)化知識(shí)

非SQL調(diào)整

(*2)

單個(gè)SQL的性能調(diào)優(yōu)

(*1)多個(gè)SQL的性能調(diào)優(yōu)如何快速定位及認(rèn)知性能問題點(diǎn)

應(yīng)用調(diào)整

應(yīng)用變慢

DB調(diào)整(*1)

(*1)Oracle

Database調(diào)優(yōu)或功能調(diào)整(*2)應(yīng)用開發(fā)員的程序邏輯調(diào)整，性能調(diào)優(yōu)應(yīng)用調(diào)整

OracleSQL性能優(yōu)化知識(shí)

非SQL調(diào)整 單個(gè)SQL

OracleSQL性能優(yōu)化知識(shí)

應(yīng)用變得反應(yīng)很慢！是因?yàn)?？非SQL原因需對(duì)應(yīng)用邏輯進(jìn)行審查SQL原因僅一兩個(gè)SQL需要優(yōu)化(索引，增加查詢條件限制)非常多的SQL需要優(yōu)化，如多個(gè)SQL的單一化SQL修改等(則需要審查整個(gè)應(yīng)用程序邏輯)前端應(yīng)用服務(wù)器/網(wǎng)絡(luò)CPU資源等待DISKI/ODBServerOracleElaps響應(yīng)STARTEND前端CPU資源等待DISKI/ODBServerOracleElaps響應(yīng)STARTEND應(yīng)用服務(wù)器/網(wǎng)路

OracleSQL性能優(yōu)化知識(shí)

應(yīng)用變得反應(yīng)很慢！是

應(yīng)用變得反應(yīng)很慢！數(shù)據(jù)庫(kù)整體變慢？由于應(yīng)用程序影響導(dǎo)致數(shù)據(jù)庫(kù)整體變慢對(duì)應(yīng)用程序進(jìn)行調(diào)整Oracle數(shù)據(jù)庫(kù)在變慢數(shù)據(jù)庫(kù)調(diào)整（參數(shù)調(diào)整，擴(kuò)大硬件內(nèi)存等）修改應(yīng)用程序(綁定變量，查詢調(diào)優(yōu)等)

OracleSQL性能優(yōu)化知識(shí)

應(yīng)用變得反應(yīng)很慢！數(shù)據(jù)庫(kù)整體變慢？

OracleSQL

OracleSQL性能優(yōu)化知識(shí)

開發(fā)員的SQL問題定位思路DBA指出的問題SQL及當(dāng)前正開發(fā)測(cè)試或正運(yùn)行的SQL考慮本質(zhì)影響因素

?

是否有效使用CPU

?

磁盤IO的使用情況

?

同步處理情況

查看外在表現(xiàn)形式?

鎖等待情況?

表連接和索引使用情況?

軟硬解析情況?

掃描讀取情況

分析SQL使用工具命令

set

autotrace

execute

plan

sql_trace

OracleSQL性能優(yōu)化知識(shí)

開發(fā)員的SQL問

OracleSQL性能優(yōu)化知識(shí)

STARTENDDB處理DB連接SQL執(zhí)行FetchCOMMITROLLBACKDB斷連應(yīng)用邏輯數(shù)據(jù)庫(kù)服務(wù)器服務(wù)器進(jìn)程接收請(qǐng)求（連接，SQL）SQL語(yǔ)法、語(yǔ)義分析生成SQL執(zhí)行計(jì)劃SQL執(zhí)行取得結(jié)果

OracleSQL性能優(yōu)化知識(shí)

STARTENDDB處理

OracleSQL性能優(yōu)化知識(shí)

數(shù)據(jù)庫(kù)處理時(shí)間通過下圖，了解哪些因素可能會(huì)成為瓶頸?？梢钥吹綌?shù)據(jù)庫(kù)處理時(shí)間被細(xì)分成三部分：1）CPU處理時(shí)間

2）等待時(shí)間(資源等待，同步處理)3）等待時(shí)間(磁盤I/O處理)前端應(yīng)用服務(wù)器/網(wǎng)絡(luò)CPU資源等待DISKI/O數(shù)據(jù)庫(kù)服務(wù)器?非規(guī)范化表設(shè)計(jì)?數(shù)據(jù)放置不合理?執(zhí)行計(jì)劃不正確?數(shù)據(jù)碎片化?不必要的SQL解析?并發(fā)(資源競(jìng)爭(zhēng))?資源枯竭?不必要的SQL解析?結(jié)合負(fù)載排序OracleElaps響應(yīng)時(shí)間可成為瓶頸的因素STARTEND

OracleSQL性能優(yōu)化知識(shí)

數(shù)據(jù)庫(kù)處理時(shí)間前端應(yīng)用服

OracleSQL性能優(yōu)化知識(shí)

CPU處理時(shí)間（使用率20％）同步處理等待時(shí)間DiskI/O(10000block)CPU處理時(shí)間（使用率80％）同步?等待DiskI/O(6000block)高速化Oracle處理時(shí)間如何使數(shù)據(jù)庫(kù)處理得更快有效利用CPU資源減少等待時(shí)間降低磁盤I/O降低同步處理所需時(shí)間Oracle處理時(shí)間CPU處理時(shí)間DiskI/O等待時(shí)間＝同步處理等待時(shí)間＋＋等待(Wait)時(shí)間

OracleSQL性能優(yōu)化知識(shí)

CPU處理時(shí)間同步處理D

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用–共享池解析SQL語(yǔ)句的執(zhí)行計(jì)劃被存儲(chǔ)在共享SQL區(qū)(SharedSQLArea)如果每個(gè)用戶已經(jīng)運(yùn)行相同的SQL，并使用相同的共享SQL區(qū)共享池庫(kù)緩存SELECTnameFROMempSELECTnameFROM

deptSELECTnameFROMempREDO日志緩沖區(qū)數(shù)據(jù)字典緩存數(shù)據(jù)庫(kù)高速緩沖區(qū)系統(tǒng)全局區(qū)SGA…

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用–共享

OracleSQL性能優(yōu)化知識(shí)

共享SQL語(yǔ)句Sql語(yǔ)句在第一次解析之后,ORACLE將SQL語(yǔ)句存放在SGA的sharedpool中,被所有用戶共享。再次執(zhí)行的時(shí)候，ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執(zhí)行路徑。提升了執(zhí)行性能并節(jié)省了內(nèi)存的使用注：ORACLE只對(duì)簡(jiǎn)單的表提供高速緩沖(cachebuffering),這個(gè)功能并不適用于多表連接查詢.ORACLE對(duì)兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,SQL語(yǔ)句必須完全相同(包括空格,換行等).

OracleSQL性能優(yōu)化知識(shí)

共享SQL語(yǔ)句

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（1）使用綁定變量不使用綁定變量SELECT*FROMEMPWHEREEMPNO=:v_empno共享池庫(kù)緩存REDO日志緩沖區(qū)數(shù)據(jù)字典緩存數(shù)據(jù)庫(kù)高速緩沖區(qū)系統(tǒng)全局區(qū)SGA…SELECT*FROMempWHEREempno=5223SELECT*FROMempWHEREempno=8826SELECT*FROMempWHEREempno=4328SELECT*FROMempWHEREempno=5211SELECT*FROMempWHEREempno=2671共享池浪費(fèi)SQL硬解析（HARDPARSE）過多的了數(shù)據(jù)庫(kù)負(fù)荷數(shù)據(jù)字典引用處理加大

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（1）SE

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（2）創(chuàng)建一個(gè)SQL編碼規(guī)范，并按規(guī)范進(jìn)行編碼。（請(qǐng)注意:下列看似相同的語(yǔ)句，Oracle并不認(rèn)為其相同?。∫虼瞬⒉粫?huì)得到重用）SELECT*FROMEMPWHEREEMPNO=:v_empnoSELECT*FROMEMP

□WHEREEMPNO=:v_empnoSELECT*FROMEMPWHEREEMPNO=:v_empno

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（2）SE

OracleSQL性能優(yōu)化知識(shí)

表連接（NestedLoop）查詢流程：通過優(yōu)化器確定驅(qū)動(dòng)表（外部表）進(jìn)行以下的循環(huán)處理：提取驅(qū)動(dòng)表中有效數(shù)據(jù)的一行（可以訪問索引，也可以無索引）在其他表（內(nèi)部表）查找匹配的有效數(shù)據(jù)并提?。ㄔL問索引）將數(shù)據(jù)返回到Oracle客戶端注意事項(xiàng)：被驅(qū)動(dòng)表(內(nèi)部表)如果沒有索引的話，查詢性能將很差

OracleSQL性能優(yōu)化知識(shí)

表連接（NestedL

OracleSQL性能優(yōu)化知識(shí)

舉例:NestedLoopSELECTd.dname,e.empno,e.ename

FROMempe,deptdWHEREe.deptno=d.deptno

ANDe.empnobetween7000and7500;

IdOperationName0SELECTSTATEMENT1NESTEDLOOPS*2TABLEACCESSBYINDEXROWIDEMP*3INDEXRANGESCANPK_EMP4TABLEACCESSBYINDEXROWIDDEPT*5INDEXUNIQUESCANPK_DEPTPredicateInformation(identifiedbyoperationid):2-filter("E"."DEPTNO"ISNOTNULL)3-access("E"."EMPNO">=7000AND"E"."EMPNO"<=7500)5-access("E"."DEPTNO"="D"."DEPTNO")①③②④

在①~④反復(fù)循環(huán)執(zhí)行

OracleSQL性能優(yōu)化知識(shí)

舉例:NestedL

OracleSQL性能優(yōu)化知識(shí)

舉例:MergeJoinSELECTd.dname,e.empno,e.ename

FROMempe,deptdWHEREe.deptno=d.deptno;

IdOperationName0SELECTSTATEMENT1MERGEJOIN2TABLEACCESSBYINDEXROWIDDEPT3INDEXFULLSCANPK_DEPT*4SORTJOIN*5TABLEACCESSFULLEMPPredicateInformation(identifiedbyoperationid):4-access("E"."DEPTNO"="D"."DEPTNO")filter("E"."DEPTNO"="D"."DEPTNO")5-filter("E"."DEPTNO"ISNOTNULL)①③②④

①、②在執(zhí)行后、③、④再執(zhí)行最后⑤進(jìn)行合并處理⑤

OracleSQL性能優(yōu)化知識(shí)

舉例:MergeJo

OracleSQL性能優(yōu)化知識(shí)

表連接（HashJoin）查詢流程：對(duì)數(shù)據(jù)量小的表進(jìn)行全表讀取在內(nèi)存中創(chuàng)建一個(gè)對(duì)應(yīng)的哈希表對(duì)大表進(jìn)行讀取并Hash（檢查哈希表，找到匹配行哈希值后返回大表的對(duì)應(yīng)行）注意事項(xiàng)：當(dāng)連接條件是非等價(jià)的鍵（范圍指定）連接，則不推薦使用哈希聯(lián)接。OLTP場(chǎng)景下哈希連接需要注意。表A表B內(nèi)存區(qū)域哈希表（表A內(nèi)容）Hash函數(shù)Hash函數(shù)

OracleSQL性能優(yōu)化知識(shí)

表連接（HashJoiOracleSQL性能優(yōu)化知識(shí)OracleSQL性能優(yōu)化知識(shí)21OracleSQL性能優(yōu)化知識(shí)OracleSQL性能優(yōu)化知識(shí)22

不僅僅將SQL作為一種語(yǔ)言，更要了解SQL在數(shù)據(jù)庫(kù)的運(yùn)作，從而實(shí)現(xiàn)有效的編碼更多得去了解表中的數(shù)據(jù)，清楚表的行數(shù)大小和查詢條件。Sql優(yōu)化是一個(gè)長(zhǎng)期的積累與不斷學(xué)習(xí)的過程希望大家在理解oraclesql解析的過程以及優(yōu)化器，執(zhí)行計(jì)劃等基礎(chǔ)上，在sql語(yǔ)句書寫與開發(fā)上面能保持一個(gè)良好的規(guī)范使代碼可讀性更好，代碼健壯性更佳OracleSQL性能優(yōu)化知識(shí)

OracleSQL性能優(yōu)化知識(shí)一般故障處理故障樹分析法一般故障處理故障樹分析法分析故障樹分析法分析故障樹分析法案例業(yè)務(wù)過程中發(fā)生等待現(xiàn)象Racvip切換漂移根據(jù)ash查找as積壓原因案例業(yè)務(wù)過程中發(fā)生等待現(xiàn)象26業(yè)務(wù)過程中發(fā)生等待現(xiàn)象業(yè)務(wù)過程中發(fā)生等待現(xiàn)象通過owi（oraclewaitinterface）可以找到當(dāng)前oracle到底在等待什么，從而找到解決方法業(yè)務(wù)過程中發(fā)生等待現(xiàn)象業(yè)務(wù)過程中發(fā)生等待現(xiàn)象27Owi相關(guān)視圖v$system_event從實(shí)例啟動(dòng)至今，所有會(huì)話上發(fā)生的等待事件的積累信息?？梢耘袛鄬?shí)例整體的穩(wěn)定性，比較特定時(shí)段的差值，還可以進(jìn)一步區(qū)間分析。v$session_event擁有當(dāng)前已連接的各會(huì)話的等待事件累積信息。（total_waits,total_timeouts,time_waited）v$session_wait提供當(dāng)前正等待事件的詳細(xì)信息。不是累積信息而是實(shí)時(shí)信息。state為waiting,wait_time顯示實(shí)際等待時(shí)間，即只需分析WAITING狀態(tài)+相應(yīng)時(shí)間不是Idle狀態(tài)v$system_wait_class整體分類Owi相關(guān)視圖v$system_event28新時(shí)代清算等待案例現(xiàn)象描述晚上10點(diǎn)左右業(yè)務(wù)部門做清算，發(fā)現(xiàn)幾個(gè)清算無法繼續(xù)雖不報(bào)錯(cuò)，但一直HANG在那很久都沒反應(yīng)。新時(shí)代清算等待案例現(xiàn)象描述29新時(shí)代清算等待案例通過owi排查通過v$session_wait視圖查看當(dāng)前正在等待的非空閑會(huì)話可以清楚看到這些會(huì)話正處于事務(wù)等待新時(shí)代清算等待案例通過owi排查30新時(shí)代清算等待案例由于是RAC環(huán)境，先得確認(rèn)是哪個(gè)節(jié)點(diǎn)出現(xiàn)此問題的。經(jīng)檢查，此清算是連接到第二個(gè)節(jié)點(diǎn)上執(zhí)行的。通過v$lock視圖，v$session視圖等聯(lián)立查詢發(fā)現(xiàn)有個(gè)sid=672的進(jìn)程阻塞了正在做清算的幾個(gè)會(huì)話。查詢此會(huì)話正在做的SQL發(fā)現(xiàn)其一直在執(zhí)行：INSERTINTOHISOFFROZENJOURSELECT*FROMHS_OPFUND.OFFROZENJOURWHERECLEAR_DATE=:B2ANDFUND_COMPANY=:B1其等待事件為：kksfbcpletion新時(shí)代清算等待案例由于是RAC環(huán)境，先得確認(rèn)是哪個(gè)節(jié)點(diǎn)出現(xiàn)此31新時(shí)代清算等待案例通過sql：selects.sidblocker,s.eventhevent,w.eventwevent,w.sidblockedfromv$sessions,v$sessionwwherew.blocking_session=s.sidandw.blocking_session_status='VALID';阻塞者blocker672阻塞了731，780,894等會(huì)話新時(shí)代清算等待案例通過sql：32新時(shí)代清算等待案例通過metalink查找kksfbcpletion等待事件發(fā)現(xiàn)此為bug,需打補(bǔ)丁解決。應(yīng)急根據(jù)sid=672找到os進(jìn)程殺掉即可。通過等待時(shí)間，可以精確量化的知道數(shù)據(jù)庫(kù)現(xiàn)在等待什么，從而找到問題原因，而不是靠現(xiàn)象，靠經(jīng)驗(yàn)盲目推測(cè)問題。新時(shí)代清算等待案例通過metalink查找kksfbcpl33根據(jù)ash查找as積壓原因我們經(jīng)常會(huì)遇到as積壓?jiǎn)栴}，往往當(dāng)時(shí)應(yīng)急解決了，客戶需要查原因，而此時(shí)會(huì)話已經(jīng)中斷。通過ash（ACTIVE

SESSION

HISTORY）我們也能在事后查到原因。ASH每秒鐘收集一次當(dāng)前處于非空閑等待事件的、活動(dòng)狀態(tài)的、session的信息，并保存在V$ACTIVE_SESSION_HISTORY視圖中根據(jù)ash查找as積壓原因我們經(jīng)常會(huì)遇到as積壓?jiǎn)栴}，往往當(dāng)34根據(jù)ash查找as積壓原因一個(gè)會(huì)話在活動(dòng)時(shí)大量占用了資源（比如：CPU，內(nèi)存，I/O等），該會(huì)話信息的丟失，將無法評(píng)測(cè)當(dāng)時(shí)的系統(tǒng)瓶頸究竟是什么。10g后我們通過v$session_wait就能找到這些信息。這個(gè)視圖保存了每個(gè)活動(dòng)session在v$session_wait中最近10次的等待事件。結(jié)合v$active_session_history視圖我們就能完整的找到發(fā)生事故的時(shí)候oracle的所有信息了?？梢哉fash就是owi的歷史追溯根據(jù)ash查找as積壓原因一個(gè)會(huì)話在活動(dòng)時(shí)大量占用了資源（比35某期貨公司時(shí)候查as積壓案例首先我們通過as日志（as_log.txt）發(fā)現(xiàn)在某一時(shí)候系統(tǒng)產(chǎn)生了大量積壓2014-12-279:49:52:提示：AS[F-AS-T-8888]編號(hào)[1]請(qǐng)求積壓[254]個(gè)!2014-12-279:49:55:提示：AS[F-AS-T-8888]編號(hào)[0]請(qǐng)求積壓[277]個(gè)!經(jīng)查詢積壓基本發(fā)生在9點(diǎn)49分到50分的時(shí)候某期貨公司時(shí)候查as積壓案例首先我們通過as日志（as_lo36使用ASH找到當(dāng)時(shí)情況通過v$active_session_history和dba_hist_sgastat以及dba_hist_active_sess_history等視圖發(fā)現(xiàn)當(dāng)時(shí)正在執(zhí)行select/*+index(aIDX_HISFUNDJOUR_DATE)+*/a.init_date,a.serial_no,a.curr_time,t.business_name,a.post_balance-a.occur_balanceasbegin_balance,a.post_balanceaspost_balance,(casewhena.occur_balance>0thena.occur_balanceelse0end)asfund_in,(casewhena.occur_balance<0thenabs(a.occur_balance)else0end)asfund_out,a.remarkfromhs_his.hisfundjoura,hs_user.businflagtwherea.business_flag=t.business_flagand((t.business_grouplike'AA%'anda.business_flagbetween2000and3000)ora.business_flagin(6120,6121,6128,6129,6122,6123,6124,6125))anda.fund_account=20100612anda.money_type='0'anda.init_date>=20100501anda.init_date<=20101201orderbya.init_date,a.curr_time,a.serial_no由于在高峰時(shí)間執(zhí)行了跨度較長(zhǎng)的報(bào)表查詢語(yǔ)句并導(dǎo)致了gcbuffer內(nèi)部等待使得cpu利用率和內(nèi)存消耗比較厲害故引起了其他業(yè)務(wù)產(chǎn)生了等待。使用ASH找到當(dāng)時(shí)情況通過v$active_session_37某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)現(xiàn)象描述客戶在某一天對(duì)交易系統(tǒng)進(jìn)行正常重啟的之后發(fā)現(xiàn)節(jié)點(diǎn)2無法正常啟動(dòng)。某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)現(xiàn)象描述38某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)故障分析首先查看了數(shù)據(jù)庫(kù)alter日志，CRS日志，結(jié)合節(jié)點(diǎn)1個(gè)節(jié)點(diǎn)2，發(fā)現(xiàn)節(jié)點(diǎn)2在服務(wù)器重啟以后所有和數(shù)據(jù)庫(kù)以及集群有關(guān)的日志全部沒有產(chǎn)生，節(jié)點(diǎn)1不停的向節(jié)點(diǎn)2發(fā)送信息全部失敗。

在節(jié)點(diǎn)1上查看集群狀態(tài)，節(jié)點(diǎn)2上所有應(yīng)用全部漂移到節(jié)點(diǎn)1.

由此判斷，節(jié)點(diǎn)2在服務(wù)器啟動(dòng)以后沒有成功的進(jìn)入集群。某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)故障分析39某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)故障分析接著進(jìn)行了節(jié)點(diǎn)1和節(jié)點(diǎn)2的系統(tǒng)日志/var/log/message查看，發(fā)現(xiàn)節(jié)點(diǎn)2的啟動(dòng)日志里面有網(wǎng)卡被強(qiáng)制shutdown的操作，而且此網(wǎng)卡是綁定了bond用于private網(wǎng)絡(luò)的。

到此問題已經(jīng)很明確了，操作系統(tǒng)層面網(wǎng)卡被down，導(dǎo)致心跳網(wǎng)絡(luò)斷了，節(jié)點(diǎn)1無法和節(jié)點(diǎn)2通信，節(jié)點(diǎn)2也無法進(jìn)行任何操作。某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)故障分析40某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)故障解決后經(jīng)排查，綁定的心跳網(wǎng)絡(luò)的2個(gè)網(wǎng)口，不在交換機(jī)同一個(gè)vlan上，導(dǎo)致一個(gè)網(wǎng)口壞掉的時(shí)候，整個(gè)bond就被強(qiáng)制down掉了。

解決：恢復(fù)網(wǎng)絡(luò)，重啟節(jié)點(diǎn)2的服務(wù)器，重新加入集群，問題解決。

-----論日志的重要性以及合理的時(shí)間點(diǎn)的判斷。某公司核心交易系統(tǒng)在重啟后節(jié)點(diǎn)2宕機(jī)故障解決41THANKSTHANKS培訓(xùn)-Oracle性能分析及一般故障排查培訓(xùn)-Oracle性能分析及一般故障排查培訓(xùn)目標(biāo)Oracle性能基本分析手段OracleSQL優(yōu)化基本知識(shí)Oracle具體案例處理培訓(xùn)目標(biāo)Oracle性能基本分析手段切入點(diǎn)Linux系統(tǒng)可以通過top命令查看系統(tǒng)的CPU、內(nèi)存、運(yùn)行時(shí)間、交換分區(qū)、執(zhí)行的線程等信息。通過top命令可以有效的發(fā)現(xiàn)系統(tǒng)的缺陷出在哪里。是內(nèi)存不夠、CPU處理能力不夠、IO讀寫過高。切入點(diǎn)Linux系統(tǒng)可以通過top命令查看系統(tǒng)的CPU、內(nèi)存切入點(diǎn)切入點(diǎn)切入點(diǎn)切入點(diǎn)

OracleSQL性能優(yōu)化知識(shí)

非SQL調(diào)整

(*2)

單個(gè)SQL的性能調(diào)優(yōu)

(*1)多個(gè)SQL的性能調(diào)優(yōu)如何快速定位及認(rèn)知性能問題點(diǎn)

應(yīng)用調(diào)整

應(yīng)用變慢

DB調(diào)整(*1)

(*1)Oracle

Database調(diào)優(yōu)或功能調(diào)整(*2)應(yīng)用開發(fā)員的程序邏輯調(diào)整，性能調(diào)優(yōu)應(yīng)用調(diào)整

OracleSQL性能優(yōu)化知識(shí)

非SQL調(diào)整 單個(gè)SQL

OracleSQL性能優(yōu)化知識(shí)

應(yīng)用變得反應(yīng)很慢！是因?yàn)?？非SQL原因需對(duì)應(yīng)用邏輯進(jìn)行審查SQL原因僅一兩個(gè)SQL需要優(yōu)化(索引，增加查詢條件限制)非常多的SQL需要優(yōu)化，如多個(gè)SQL的單一化SQL修改等(則需要審查整個(gè)應(yīng)用程序邏輯)前端應(yīng)用服務(wù)器/網(wǎng)絡(luò)CPU資源等待DISKI/ODBServerOracleElaps響應(yīng)STARTEND前端CPU資源等待DISKI/ODBServerOracleElaps響應(yīng)STARTEND應(yīng)用服務(wù)器/網(wǎng)路

OracleSQL性能優(yōu)化知識(shí)

應(yīng)用變得反應(yīng)很慢！是

應(yīng)用變得反應(yīng)很慢！數(shù)據(jù)庫(kù)整體變慢？由于應(yīng)用程序影響導(dǎo)致數(shù)據(jù)庫(kù)整體變慢對(duì)應(yīng)用程序進(jìn)行調(diào)整Oracle數(shù)據(jù)庫(kù)在變慢數(shù)據(jù)庫(kù)調(diào)整（參數(shù)調(diào)整，擴(kuò)大硬件內(nèi)存等）修改應(yīng)用程序(綁定變量，查詢調(diào)優(yōu)等)

OracleSQL性能優(yōu)化知識(shí)

應(yīng)用變得反應(yīng)很慢！數(shù)據(jù)庫(kù)整體變慢？

OracleSQL

OracleSQL性能優(yōu)化知識(shí)

開發(fā)員的SQL問題定位思路DBA指出的問題SQL及當(dāng)前正開發(fā)測(cè)試或正運(yùn)行的SQL考慮本質(zhì)影響因素

?

是否有效使用CPU

?

磁盤IO的使用情況

?

同步處理情況

查看外在表現(xiàn)形式?

鎖等待情況?

表連接和索引使用情況?

軟硬解析情況?

掃描讀取情況

分析SQL使用工具命令

set

autotrace

execute

plan

sql_trace

OracleSQL性能優(yōu)化知識(shí)

開發(fā)員的SQL問

OracleSQL性能優(yōu)化知識(shí)

STARTENDDB處理DB連接SQL執(zhí)行FetchCOMMITROLLBACKDB斷連應(yīng)用邏輯數(shù)據(jù)庫(kù)服務(wù)器服務(wù)器進(jìn)程接收請(qǐng)求（連接，SQL）SQL語(yǔ)法、語(yǔ)義分析生成SQL執(zhí)行計(jì)劃SQL執(zhí)行取得結(jié)果

OracleSQL性能優(yōu)化知識(shí)

STARTENDDB處理

OracleSQL性能優(yōu)化知識(shí)

數(shù)據(jù)庫(kù)處理時(shí)間通過下圖，了解哪些因素可能會(huì)成為瓶頸。可以看到數(shù)據(jù)庫(kù)處理時(shí)間被細(xì)分成三部分：1）CPU處理時(shí)間

2）等待時(shí)間(資源等待，同步處理)3）等待時(shí)間(磁盤I/O處理)前端應(yīng)用服務(wù)器/網(wǎng)絡(luò)CPU資源等待DISKI/O數(shù)據(jù)庫(kù)服務(wù)器?非規(guī)范化表設(shè)計(jì)?數(shù)據(jù)放置不合理?執(zhí)行計(jì)劃不正確?數(shù)據(jù)碎片化?不必要的SQL解析?并發(fā)(資源競(jìng)爭(zhēng))?資源枯竭?不必要的SQL解析?結(jié)合負(fù)載排序OracleElaps響應(yīng)時(shí)間可成為瓶頸的因素STARTEND

OracleSQL性能優(yōu)化知識(shí)

數(shù)據(jù)庫(kù)處理時(shí)間前端應(yīng)用服

OracleSQL性能優(yōu)化知識(shí)

CPU處理時(shí)間（使用率20％）同步處理等待時(shí)間DiskI/O(10000block)CPU處理時(shí)間（使用率80％）同步?等待DiskI/O(6000block)高速化Oracle處理時(shí)間如何使數(shù)據(jù)庫(kù)處理得更快有效利用CPU資源減少等待時(shí)間降低磁盤I/O降低同步處理所需時(shí)間Oracle處理時(shí)間CPU處理時(shí)間DiskI/O等待時(shí)間＝同步處理等待時(shí)間＋＋等待(Wait)時(shí)間

OracleSQL性能優(yōu)化知識(shí)

CPU處理時(shí)間同步處理D

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用–共享池解析SQL語(yǔ)句的執(zhí)行計(jì)劃被存儲(chǔ)在共享SQL區(qū)(SharedSQLArea)如果每個(gè)用戶已經(jīng)運(yùn)行相同的SQL，并使用相同的共享SQL區(qū)共享池庫(kù)緩存SELECTnameFROMempSELECTnameFROM

deptSELECTnameFROMempREDO日志緩沖區(qū)數(shù)據(jù)字典緩存數(shù)據(jù)庫(kù)高速緩沖區(qū)系統(tǒng)全局區(qū)SGA…

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用–共享

OracleSQL性能優(yōu)化知識(shí)

共享SQL語(yǔ)句Sql語(yǔ)句在第一次解析之后,ORACLE將SQL語(yǔ)句存放在SGA的sharedpool中,被所有用戶共享。再次執(zhí)行的時(shí)候，ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執(zhí)行路徑。提升了執(zhí)行性能并節(jié)省了內(nèi)存的使用注：ORACLE只對(duì)簡(jiǎn)單的表提供高速緩沖(cachebuffering),這個(gè)功能并不適用于多表連接查詢.ORACLE對(duì)兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,SQL語(yǔ)句必須完全相同(包括空格,換行等).

OracleSQL性能優(yōu)化知識(shí)

共享SQL語(yǔ)句

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（1）使用綁定變量不使用綁定變量SELECT*FROMEMPWHEREEMPNO=:v_empno共享池庫(kù)緩存REDO日志緩沖區(qū)數(shù)據(jù)字典緩存數(shù)據(jù)庫(kù)高速緩沖區(qū)系統(tǒng)全局區(qū)SGA…SELECT*FROMempWHEREempno=5223SELECT*FROMempWHEREempno=8826SELECT*FROMempWHEREempno=4328SELECT*FROMempWHEREempno=5211SELECT*FROMempWHEREempno=2671共享池浪費(fèi)SQL硬解析（HARDPARSE）過多的了數(shù)據(jù)庫(kù)負(fù)荷數(shù)據(jù)字典引用處理加大

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（1）SE

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（2）創(chuàng)建一個(gè)SQL編碼規(guī)范，并按規(guī)范進(jìn)行編碼。（請(qǐng)注意:下列看似相同的語(yǔ)句，Oracle并不認(rèn)為其相同！！因此并不會(huì)得到重用）SELECT*FROMEMPWHEREEMPNO=:v_empnoSELECT*FROMEMP

□WHEREEMPNO=:v_empnoSELECT*FROMEMPWHEREEMPNO=:v_empno

OracleSQL性能優(yōu)化知識(shí)

SQL語(yǔ)句重用（2）SE

OracleSQL性能優(yōu)化知識(shí)

表連接（NestedLoop）查詢流程：通過優(yōu)化器確定驅(qū)動(dòng)表（外部表）進(jìn)行以下的循環(huán)處理：提取驅(qū)動(dòng)表中有效數(shù)據(jù)的一行（可以訪問索引，也可以無索引）在其他表（內(nèi)部表）查找匹配的有效數(shù)據(jù)并提?。ㄔL問索引）將數(shù)據(jù)返回到Oracle客戶端注意事項(xiàng)：被驅(qū)動(dòng)表(內(nèi)部表)如果沒有索引的話，查詢性能將很差

OracleSQL性能優(yōu)化知識(shí)

表連接（NestedL

OracleSQL性能優(yōu)化知識(shí)

舉例:NestedLoopSELECTd.dname,e.empno,e.ename

FROMempe,deptdWHEREe.deptno=d.deptno

ANDe.empnobetween7000and7500;

IdOperationName0SELECTSTATEMENT1NESTEDLOOPS*2TABLEACCESSBYINDEXROWIDEMP*3INDEXRANGESCANPK_EMP4TABLEACCESSBYINDEXROWIDDEPT*5INDEXUNIQUESCANPK_DEPTPredicateInformation(identifiedbyoperationid):2-filter("E"."DEPTNO"ISNOTNULL)3-access("E"."EMPNO">=7000AND"E"."EMPNO"<=7500)5-access("E"."DEPTNO"="D"."DEPTNO")①③②④

在①~④反復(fù)循環(huán)執(zhí)行

OracleSQL性能優(yōu)化知識(shí)

舉例:NestedL

OracleSQL性能優(yōu)化知識(shí)

舉例:MergeJoinSELECTd.dname,e.empno,e.ename

FROMempe,deptdWHEREe.deptno=d.deptno;

IdOperationName0SELECTSTATEMENT1MERGEJOIN2TABLEACCESSBYINDEXROWIDDEPT3INDEXFULLSCANPK_DEPT*4SORTJOIN*5TABLEACCESSFULLEMPPredicateInformation(identifiedbyoperationid):4-access("E"."DEPTNO"="D"."DEPTNO")filter("E"."DEPTNO"="D"."DEPTNO")5-filter("E"."DEPTNO"ISNOTNULL)①③②④

①、②在執(zhí)行后、③、④再執(zhí)行最后⑤進(jìn)行合并處理⑤

OracleSQL性能優(yōu)化知識(shí)

舉例:MergeJo

OracleSQL性能優(yōu)化知識(shí)

表連接（HashJoin）查詢流程：對(duì)數(shù)據(jù)量小的表進(jìn)行全表讀取在內(nèi)存中創(chuàng)建一個(gè)對(duì)應(yīng)的哈希表對(duì)大表進(jìn)行讀取并Hash（檢查哈希表，找到匹配行哈希值后返回大表的對(duì)應(yīng)行）注意事項(xiàng)：當(dāng)連接條件是非等價(jià)的鍵（范圍指定）連接，則不推薦使用哈希聯(lián)接。OLTP場(chǎng)景下哈希連接需要注意。表A表B內(nèi)存區(qū)域哈希表（表A內(nèi)容）Hash函數(shù)Hash函數(shù)

OracleSQL性能優(yōu)化知識(shí)

表連接（HashJoiOracleSQL性能優(yōu)化知識(shí)OracleSQL性能優(yōu)化知識(shí)63OracleSQL性能優(yōu)化知識(shí)OracleSQL性能優(yōu)化知識(shí)64

不僅僅將SQL作為一種語(yǔ)言，更要了解SQL在數(shù)據(jù)庫(kù)的運(yùn)作，從而實(shí)現(xiàn)有效的編碼更多得去了解表中的數(shù)據(jù)，清楚表的行數(shù)大小和查詢條件。Sql優(yōu)化是一個(gè)長(zhǎng)期的積累與不斷學(xué)習(xí)的過程希望大家在理解oraclesql解析的過程以及優(yōu)化器，執(zhí)行計(jì)劃等基礎(chǔ)上，在sql語(yǔ)句書寫與開發(fā)上面能保持一個(gè)良好的規(guī)范使代碼可讀性更好，代碼健壯性更佳OracleSQL性能優(yōu)化知識(shí)

OracleSQL性能優(yōu)化知識(shí)一般故障處理故障樹分析法一般故障處理故障樹分析法分析故障樹分析法分析故障樹分析法案例業(yè)務(wù)過程中發(fā)生等待現(xiàn)象Racvip切換漂移根據(jù)ash查找as積壓原因案例業(yè)務(wù)過程中發(fā)生等待現(xiàn)象68業(yè)務(wù)過程中發(fā)生等待現(xiàn)象業(yè)務(wù)過程中發(fā)生等待現(xiàn)象通過owi（oraclewaitinterface）可以找到當(dāng)前oracle到底在等待什么，從而找到解決方法業(yè)務(wù)過程中發(fā)生等待現(xiàn)象業(yè)務(wù)過程中發(fā)生等待現(xiàn)象69Owi相關(guān)視圖v$system_event從實(shí)例啟動(dòng)至今，所有會(huì)話上發(fā)生的等待事件的積累信息?？梢耘袛鄬?shí)例整體的穩(wěn)定性，比較特定時(shí)段的差值，還可以進(jìn)一步區(qū)間分析。v$session_event擁有當(dāng)前已連接的各會(huì)話的等待事件累積信息。（total_waits,total_timeouts,time_waited）v$session_wait提供當(dāng)前正等待事件的詳細(xì)信息。不是累積信息而是實(shí)時(shí)信息。state為waiting,wait_time顯示實(shí)際等待時(shí)間，即只需分析WAITING狀態(tài)+相應(yīng)時(shí)間不是Idle狀態(tài)v$system_wait_class整體分類Owi相關(guān)視圖v$system_event70新時(shí)代清算等待案例現(xiàn)象描述晚上10點(diǎn)左右業(yè)務(wù)部門做清算，發(fā)現(xiàn)幾個(gè)清算無法繼續(xù)雖不報(bào)錯(cuò)，但一直HANG在那很久都沒反應(yīng)。新時(shí)代清算等待案例現(xiàn)象描述71新時(shí)代清算等待案例通過owi排查通過v$session_wait視圖查看當(dāng)前正在等待的非空閑會(huì)話可以清楚看到這些會(huì)話正處于事務(wù)等待新時(shí)代清算等待案例通過owi排查72新時(shí)代清算等待案例由于是RAC環(huán)境，先得確認(rèn)是哪個(gè)節(jié)點(diǎn)出現(xiàn)此問題的。經(jīng)檢查，此清算是連接到第二個(gè)節(jié)點(diǎn)上執(zhí)行的。通過v$lock視圖，v$session視圖等聯(lián)立查詢發(fā)現(xiàn)有個(gè)sid=672的進(jìn)程阻塞了正在做清算的幾個(gè)會(huì)話。查詢此會(huì)話正在做的SQL發(fā)現(xiàn)其一直在執(zhí)行：INSERTINTOHISOFFROZENJOURSELECT*FROMHS_OPFUND.OFFROZENJOURWHERECLEAR_DATE=:B2ANDFUND_COMPANY=:B1其等待事件為：kksfbcpletion新時(shí)代清算等待案例由于是RAC環(huán)境，先得確認(rèn)是哪個(gè)節(jié)點(diǎn)出現(xiàn)此73新時(shí)代清算等待案例通過sql：selects.sidblocker,s.eventhevent,w.eventwevent,w.sidblockedfromv$sessions,v$sessionwwherew.blocking_session=s.sidandw.blocking_session_status='VALID';阻塞者blocker672阻塞了731，780,894等會(huì)話新時(shí)代清算等待案例通過sql：74新時(shí)代清算等待案例通過metalink查找kksfbcpletion等待事件發(fā)現(xiàn)此為bug,需打補(bǔ)丁解決。應(yīng)急根據(jù)sid=672找到os進(jìn)程殺掉即可。通過等待時(shí)間，可以精確量化的知道數(shù)據(jù)庫(kù)現(xiàn)在等待什么，從而找到問題原因，而不是靠現(xiàn)象，靠經(jīng)驗(yàn)盲目推測(cè)問題。新時(shí)代清算等待案例通過metalink查找kksfbcpl75根據(jù)ash查找as積壓原因我們經(jīng)常會(huì)遇到as積壓?jiǎn)栴}，往往當(dāng)時(shí)應(yīng)急解決了，客戶需要查原因，而此時(shí)會(huì)話已經(jīng)中斷。通過ash（ACTIVE

SESSION

HISTORY）我們也能在事后查到原因。ASH每秒鐘收集一次當(dāng)前處于非空閑等待事件的、活動(dòng)狀態(tài)的、session的信息，并保存在V$ACTIVE_SESSION_HISTORY視圖中根據(jù)ash查找as積壓原因我們經(jīng)常會(huì)遇到as積壓?jiǎn)栴}，往往當(dāng)76根據(jù)ash查找as積壓原因一個(gè)會(huì)話在活動(dòng)時(shí)大量占用了資源（比如：CPU，內(nèi)存，I/O等），該會(huì)話信息的丟失，將無法評(píng)測(cè)當(dāng)時(shí)的系統(tǒng)瓶頸究竟是什么。10g后我們通過v$session_wait就能找到這些信息。這個(gè)視圖保存了每個(gè)活動(dòng)session在v$session_wait中最近10次的等待事件。結(jié)合v$active_session_history視圖我們就能完整的找到發(fā)生事故的時(shí)候oracle的所有信息了?？梢哉fash就是owi的歷史追溯根據(jù)ash查找as積壓原因