Oracle-sql優(yōu)化經(jīng)驗(yàn)總結(jié)

ORACLESQL優(yōu)化經(jīng)驗(yàn)交流

系統(tǒng)支撐部董建忠1第一章:概述什么樣的SQL需要優(yōu)化常見的問題發(fā)現(xiàn)問題的方法2什么樣的SQL需要優(yōu)化引發(fā)嚴(yán)重的等待事件消耗大量的系統(tǒng)資源（CPU/IO/MEM）運(yùn)行時(shí)間超長(zhǎng)不能滿足壓力測(cè)試指標(biāo)3常見的問題沒有恰當(dāng)?shù)乃饕ㄈ頀呙瑁]有使用到恰當(dāng)?shù)乃饕鼐幾g問題多表關(guān)聯(lián)條件不當(dāng)或關(guān)聯(lián)太多分區(qū)表沒有分析，未能使用索引死鎖4發(fā)現(xiàn)問題的方法從v$session_wait查看等待事件SQLPLUS使用AUTOTRACE查看執(zhí)行計(jì)劃在TOAD中直接查看執(zhí)行計(jì)劃從STATSPACK查看資源(CPU、I/O)消耗狀況生成SESSIONTRACE文件（一般為DBA使用）用命令tkprof對(duì)TRACE文件進(jìn)行分析5第二章：從等待事件中發(fā)現(xiàn)問題查看SESSIONWAIT的語(yǔ)句典型事件：DbFileSequentialRead典型事件：DbFileSequentialRead典型事件：LatchFree（latch釋放）6查看SESSIONWAIT的語(yǔ)句setpagesize2000setlinesize110coleventformata25colprogramformata20selecta.event,substr(gram,1,20)program,b.sid,a.p1,a.p2,a.p3fromgv$session_wait

a,v$sessionbwherea.sid=b.sidanda.eventnotlike'%SQL%'anda.eventnotlike'%message%'anda.eventnotlike'%time%'7典型事件：DbFileScatteredRead

數(shù)據(jù)文件分散讀取這種情況通常顯示與全表掃描相關(guān)的等待。一般表明該表找不到索引，或者只能找到有限的索引。特定條件下執(zhí)行全表掃描可能比索引掃描更有效，但如果出現(xiàn)這種等待時(shí)，最好檢查一下這些全表掃描是否必要。建議將小而常用的表CACHE到內(nèi)存中，以避免一次又一次地重復(fù)讀取它們8典型事件：DbFileSequentialRead

數(shù)據(jù)文件順序讀取這一事件通常顯示單個(gè)塊的讀取(如索引讀取)表示表的連接順序不佳，或者使用了不恰當(dāng)?shù)乃饕龣z查每個(gè)掃描是否必要的，并檢查多表連接的連接順序一般會(huì)消耗大量PGA內(nèi)存，從而在順序讀取時(shí)導(dǎo)致大量等待。9典型事件：LatchFree（latch釋放）latch是一種低級(jí)排隊(duì)機(jī)制，用于保護(hù)系統(tǒng)全局區(qū)域（SGA）中共享內(nèi)存結(jié)構(gòu)。latch就像是一種快速地被獲取和釋放的內(nèi)存鎖。latch用于防止共享內(nèi)存結(jié)構(gòu)被多個(gè)用戶同時(shí)訪問。如果latch不可用，就會(huì)記錄latch釋放失敗。大多數(shù)latch問題都與以下操作相關(guān)：不能使用綁定變量（庫(kù)緩存latch）、重復(fù)生成問題（重復(fù)分配latch）、緩沖存儲(chǔ)器競(jìng)爭(zhēng)問題（緩沖器存儲(chǔ)LRU鏈），以及緩沖存儲(chǔ)器中的“熱”塊（緩沖存儲(chǔ)器鏈）。也有一些latch等待與bug（程序錯(cuò)誤）有關(guān)當(dāng)latch不命中率大于0.5%時(shí)，就應(yīng)當(dāng)研究這一問題10第三章：SQL語(yǔ)句的執(zhí)行計(jì)劃SQL語(yǔ)句的執(zhí)行步驟ORACLE的優(yōu)化器在SQLPLUS配置AUTOTRACE使用QUESTTOAD查看執(zhí)行計(jì)劃安裝AUTOTRACE環(huán)境使用QUESTTOAD查看執(zhí)行計(jì)劃查看執(zhí)行計(jì)劃11SQL語(yǔ)句的執(zhí)行步驟語(yǔ)法分析，分析語(yǔ)句的語(yǔ)法是否符合規(guī)范，衡量語(yǔ)句中各表達(dá)式的意義。語(yǔ)義分析，檢查語(yǔ)句中涉及的所有數(shù)據(jù)庫(kù)對(duì)象是否存在，且用戶有相應(yīng)的權(quán)限。視圖轉(zhuǎn)換，將涉及視圖的查詢語(yǔ)句轉(zhuǎn)換為相應(yīng)的對(duì)基表查詢語(yǔ)句。表達(dá)式轉(zhuǎn)換，將復(fù)雜的SQL表達(dá)式轉(zhuǎn)換為較簡(jiǎn)單的等效連接表達(dá)式。

選擇優(yōu)化器，不同的優(yōu)化器一般產(chǎn)生不同的“執(zhí)行計(jì)劃”選擇連接方式，ORACLE有三種連接方式，對(duì)多表連接ORACLE可選擇適當(dāng)?shù)倪B接方式。

選擇連接順序，對(duì)多表連接ORACLE選擇哪一對(duì)表先連接，選擇這兩表中哪個(gè)表做為源數(shù)據(jù)表。選擇數(shù)據(jù)的搜索路徑，根據(jù)以上條件選擇合適的數(shù)據(jù)搜索路徑，如是選用全表搜索還是利用索引或是其他的方式。運(yùn)行“執(zhí)行計(jì)劃”12ORACLE的優(yōu)化器ORACLE有兩種優(yōu)化器：基于規(guī)則的優(yōu)化器（RBO，RuleBasedOptimizer），和基于代價(jià)的優(yōu)化器（CBO，CostBasedOptimizer）ORACLEV7以來缺省的設(shè)置應(yīng)是“choose”，即如果對(duì)已分析的表查詢的話選擇CBO，否則選擇RBO。如果該參數(shù)設(shè)為“rule”，則不論表是否分析過，一概選用RBO，除非在語(yǔ)句中用hint強(qiáng)制各“執(zhí)行計(jì)劃”的cost的計(jì)算根據(jù)，依賴于數(shù)據(jù)表中數(shù)據(jù)的統(tǒng)計(jì)分布，須要分析表和相關(guān)的索引，才能搜集到CBO所需的數(shù)據(jù)13在SQLPLUS配置AUTOTRACEAUTOTRACE參數(shù)解釋SETAUTOTRACEOFF不能獲得AUTOTRACE報(bào)告.這是默認(rèn)的.SETAUTOTRACEONEXPLAIN僅僅顯示優(yōu)化器執(zhí)行計(jì)劃的AUTOTRACE報(bào)告SETAUTOTRACEONSTATISTICS僅僅顯示SQL語(yǔ)句執(zhí)行的統(tǒng)計(jì)結(jié)果的AUTOTRACE報(bào)告SETAUTOTRACEON包括上面兩項(xiàng)內(nèi)容的AUTOTRACE報(bào)告SETAUTOTRACETRACEONLY與SET

AUTOTRACE

ON類似,所有的統(tǒng)計(jì)和數(shù)據(jù)都在，但不可以打印14安裝AUTOTRACE環(huán)境用戶必須被賦予PLUSTRACE角色，而PLUSTRACE角色需要DBA來賦予。另外，該用戶必須創(chuàng)建PLAN_TABLE表1、首先創(chuàng)建PLUSTRACE角色并且賦給DBA：

CONNECTsys/sys’spasswordASSYSDBA@$ORACLE_HOME/sqlplus/admin/plustrce.sql2、賦權(quán)限給用戶

CONNECT/ASSYSDBAGRANTPLUSTRACETOUSER（預(yù)賦權(quán)的用戶名）;這樣，就可以在該用戶下設(shè)置AUTOTRACE報(bào)告的顯示與否了。15使用QUESTTOAD查看執(zhí)行計(jì)劃安裝QUESTTOAD軟件建立數(shù)據(jù)庫(kù)連接進(jìn)入SQL語(yǔ)句執(zhí)行窗口輸入并選定SQL語(yǔ)句在’SQL-WINDOW’菜單中選‘EXPLAINPLANCURRENTSQL’，即可看到執(zhí)行計(jì)劃，并不真正執(zhí)行語(yǔ)句，不需要等待結(jié)果16查看執(zhí)行計(jì)劃ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'TEST'Statistics0recursivecalls0dbblockgets4consistentgets0physicalreads0redosize547bytessentviaSQL*Nettoclient655bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)4rowsprocessed17第四章：如何分析問題的原因查找原因的一般步驟18查找原因的步驟（一）檢查被索引的列或組合索引的首列是否出現(xiàn)在PL/SQL語(yǔ)句的WHERE子句看采用了哪種類型的連接方式。ORACLE的共有SortMergeJoin（歸并SMJ）、HashJoin（散列HJ）和NestedLoopJoin（嵌套循環(huán)NL）。在兩張表連接，且內(nèi)表的目標(biāo)列上建有索引時(shí)，只有NestedLoop才能有效地利用到該索引。SMJ即使相關(guān)列上建有索引，最多只能因索引的存在，避免數(shù)據(jù)排序過程。HJ由于須做HASH運(yùn)算，索引的存在對(duì)數(shù)據(jù)查詢速度幾乎沒有影響19查找原因的步驟（二）看連接順序是否允許使用相關(guān)索引。假設(shè)表emp的deptno列上有索引，表dept的列deptno上無(wú)索引，WHERE語(yǔ)句有emp.deptno=dept.deptno條件。在做NL連接時(shí)，emp做為外表，先被訪問，由于連接機(jī)制原因，外表的數(shù)據(jù)訪問方式是全表掃描，emp.deptno上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描20查找原因的步驟（三）是否用到系統(tǒng)數(shù)據(jù)字典表或視圖。由于系統(tǒng)數(shù)據(jù)字典表都未被分析過，可能導(dǎo)致極差的“執(zhí)行計(jì)劃”。但是不要擅自對(duì)數(shù)據(jù)字典表做分析，否則可能導(dǎo)致死鎖，或系統(tǒng)性能下降索引列是否函數(shù)的參數(shù)。如是，索引在查詢時(shí)用不上是否存在潛在的數(shù)據(jù)類型轉(zhuǎn)換。如將字符型數(shù)據(jù)與數(shù)值型數(shù)據(jù)比較，ORACLE會(huì)自動(dòng)將字符型用to_number()函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致第六種現(xiàn)象的發(fā)生21查找原因的步驟（四）是否為表和相關(guān)的索引搜集足夠的統(tǒng)計(jì)數(shù)據(jù)。對(duì)數(shù)據(jù)經(jīng)常有增、刪、改的表最好定期對(duì)表和索引進(jìn)行分析，可用SQL語(yǔ)句“analyzetablexxxxcomputestatisticsforallindexes;”。ORACLE掌握了充分反映實(shí)際的統(tǒng)計(jì)數(shù)據(jù)，才有可能做出正確的選擇索引列的選擇性不高（字段值重復(fù)率高）22查找原因的步驟（五）索引列值是否可為空（NULL）。如果索引列值可以是空值，在SQL語(yǔ)句中那些需要返回NULL值的操作，將不會(huì)用到索引，如COUNT（*），而是用全表掃描。這是因?yàn)樗饕写鎯?chǔ)值不能為全空看是否有用到并行查詢（PQO）。并行查詢將不會(huì)用到索引看PL/SQL語(yǔ)句中是否有用到bind變量。由于數(shù)據(jù)庫(kù)不知道bind變量具體是什么值，在做非相等連接時(shí)，如“<”，“>”,“l(fā)ike”等。ORACLE將引用缺省值，在某些情況下會(huì)對(duì)執(zhí)行計(jì)劃造成影響23第五章：SQL重編譯問題SQL共享原理SQL共享的三個(gè)條件PROC程序的SQL共享PROC程序中以下類型的語(yǔ)句不需進(jìn)行變量綁定PROC程序的CLIENT參數(shù)存儲(chǔ)過程的SQL共享SQL共享的數(shù)據(jù)庫(kù)參數(shù)的利弊24SQL共享原理

ORACLE將執(zhí)行過的SQL語(yǔ)句存放在內(nèi)存的共享池(sharedbufferpool)中，可以被所有的數(shù)據(jù)庫(kù)用戶共享當(dāng)你執(zhí)行一個(gè)SQL語(yǔ)句(有時(shí)被稱為一個(gè)游標(biāo))時(shí),如果它和之前的執(zhí)行過的語(yǔ)句完全相同,ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執(zhí)行路徑.這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用25SQL共享的三個(gè)條件當(dāng)前被執(zhí)行的語(yǔ)句和共享池中的語(yǔ)句必須完全相同（包括大小寫、空格、換行等）兩個(gè)語(yǔ)句所指的對(duì)象必須完全相同（同義詞與表是不同的對(duì)象）兩個(gè)SQL語(yǔ)句中必須使用相同的名字的綁定變量(bindvariables)26PROC程序的SQL共享未使用綁定變量的語(yǔ)句

sprintf(sqlstr,"insertintoscott.test1(num1,num2)values(%d,%d)",n_var1,n_var2);EXECSQLEXECUTEIMMEDIATE:sqlstr;EXECSQLCOMMIT;

使用綁定變量的語(yǔ)句

strcpy(sqlstr,"insertintotest(num1,num2)values(:v1,:v2)"); EXECSQLPREPAREsql_stmtFROM:sqlstr; EXECSQLEXECUTEsql_stmtUSING:n_var1,:n_var2; EXECSQLCOMMIT;動(dòng)態(tài)表也可以使用以上方式避免重編譯27PROC程序中以下類型的語(yǔ)句不需進(jìn)行變量綁定

for(i=0;i<10000;i++){EXECSQLinsertintotab_test1(id)selectidfromtab_testwhereid=:i;EXECSQLCOMMIT;}

本語(yǔ)句在數(shù)據(jù)庫(kù)解析后的結(jié)果

insertintotab_test1(id)selectidfromtab_testwhereid=:b028PROC程序的CLIENT參數(shù)（一）PROC編譯參數(shù)，控制客戶端游標(biāo)緩存，使用的是本地的內(nèi)存，與服務(wù)器無(wú)關(guān)

HOLD_CURSOR=yes－－保留游標(biāo)在緩存中，默認(rèn)為noRELEASE_CURSOR=no－－釋放緩存,默認(rèn)為no

這兩個(gè)參數(shù)也可以在程序中設(shè)置

EXECORACLEOPTION(HOLD_CURSOR=NO);EXECORACLEOPTION(RELEASE_CURSOR=YES);29PROC程序的CLIENT參數(shù)（二）數(shù)據(jù)庫(kù)參數(shù)OPEN_CURSORS指保留在SERVER的sharedpool中的游標(biāo)數(shù)（默認(rèn)為50）PROC參數(shù)MAXOPENCURSORS指保留在CLIENT端的游標(biāo)數(shù)（默認(rèn)為10）30存儲(chǔ)過程的SQL共享存儲(chǔ)過程中以下類型的語(yǔ)句不需要綁定CREATEORREPLACEprocedureproc_testas

n_id

int:=0;BEGINFORiIN1..1000LOOPinsertintoTAB_TEST(ID)values(i);commit;ENDLOOP;END;31強(qiáng)制SQL共享的數(shù)據(jù)庫(kù)參數(shù)的利弊ORACLE8i以后，可以使用數(shù)據(jù)庫(kù)參數(shù)cursor_sharing

＝FORCE或者SIMILAR（9i）使程序SQL在數(shù)據(jù)庫(kù)中硬分析之前共享類似SQL分析的結(jié)果，只進(jìn)行軟分析，避免重編譯，設(shè)置了該參數(shù)之后，程序?qū)⒉恍枰褂媒壎ㄗ兞浚藚?shù)會(huì)觸發(fā)一些BUG，建議小范圍使用或不使用32第六章：索引33索引的類型B-tree索引位圖索引（一般用于數(shù)據(jù)倉(cāng)庫(kù)中的靜態(tài)數(shù)據(jù)）函數(shù)索引－－需要設(shè)置兩個(gè)數(shù)據(jù)庫(kù)參數(shù)QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTED反向索引（一般少用）34表和索引的分析execdbms_stats.GATHER_TABLE_STATS('ST',‘DCUSTMSG',ESTIMATE_PERCENT=>50);ANALYZEtableTABLE_NAMEESTIMATESTATISTICSSAMPLE50PERCENT;ORACLE9i建議使用dbms_stats.GATHER_TABLE_STATS35分區(qū)表、索引的特點(diǎn)分區(qū)表應(yīng)盡量建立分區(qū)索引分區(qū)表的主鍵索引若不包含分區(qū)字段，則只能建為全局索引分區(qū)表和索引便于管理，但對(duì)于底層IO均勻的存儲(chǔ)，并不能提高查詢性能分區(qū)表和索引需要定期分析，才能恰當(dāng)?shù)谋粓?zhí)行計(jì)劃使用36第七章：雜項(xiàng)37IN和EXISTS...wherecolumnin(select*from...where...);...whereexists(select'X'from...where...);第二種格式要遠(yuǎn)比第一種格式的效率高。在Oracle中可以幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢使用EXIST，Oracle系統(tǒng)會(huì)首先檢查主查詢，然后運(yùn)行子查詢直到它找到第一個(gè)匹配項(xiàng)，這就節(jié)省了時(shí)間Oracle系統(tǒng)在執(zhí)行IN子查詢時(shí)，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在在一個(gè)加了索引的臨時(shí)表中38ISNULL與ISNOTNULL不能用null作索引，任何包含null值的列都將不會(huì)被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會(huì)從索引中排除。也就是說如果某列存在空值，即使對(duì)該列建索引也不會(huì)提高性能任何在where子句中使用isnull或isnotnull的語(yǔ)句優(yōu)化器是不允許使用索引的39Orderby語(yǔ)句ORDERBY語(yǔ)句決定了Oracle如何將返回的查詢結(jié)果排序