ORACLE培訓SQL性能優(yōu)化

1、ORACLE培訓-SQL性能優(yōu)化內(nèi)容概述述課程主要要討論：SQL語句執(zhí)行行的過程程、ORACLE優(yōu)化器，表之間間的關(guān)聯(lián)聯(lián)，如何何得到SQL執(zhí)行計劃劃，如何何分析執(zhí)執(zhí)行計劃等等內(nèi)容，從而由由淺到深深的方式式了解SQL優(yōu)化的過過程，使大大家逐步步掌握SQL優(yōu)化。目錄優(yōu)化基礎(chǔ)礎(chǔ)知識性能調(diào)整整綜述有效的應應用設(shè)計計SQL語句的處處理過程程Oracle的優(yōu)化器器Oracle的執(zhí)行計計劃注意事項項SQL語句優(yōu)化化的過程程定位有問問題的語語句檢查執(zhí)行行計劃檢查執(zhí)行行過程中中優(yōu)化器器的統(tǒng)計計信息分析相關(guān)關(guān)表的記記錄數(shù)、索引情情況改寫SQL語句、使使用HINT、調(diào)整索索引、表表分析有些SQL語句不具具備優(yōu)化化的

2、可能能，需要要優(yōu)化處處理方式式達到最佳佳執(zhí)行計計劃什么是好好的SQL語句？盡量簡單單，模塊塊化易讀、易易維護節(jié)省資源源內(nèi)存CPU掃描的數(shù)數(shù)據(jù)塊要要少少排序不造成死死鎖為什么要要bind variables?字符級的的比較:SELECT*FROM USER_FILESWHEREUSER_NO =10001234;與SELECT*FROM USER_FILESWHEREUSER_NO =:BV1;檢查：selectname,executionsfrom v$db_object_cachewherename likeselect* fromuser_files%什么叫做做重編譯譯問題什么叫做做重編譯

3、譯？下面這個個語句每每執(zhí)行一一次就需需要在SHAREPOOL硬解析一一次，一百百萬用戶戶就是一一百萬次次，消耗耗CPU和內(nèi)存，如果業(yè)業(yè)務量大，很很可能導導致宕庫庫如果綁定定變量，則只需需要硬解解析一次次，重復復調(diào)用即即可select*from dConMsgwherecontract_no= 32013484095139綁定變量量解決重重編譯問問題未使用綁綁定變量量的語句句sprintf(sqlstr,insertinto scott.test1 (num1,num2)values(%d,%d),n_var1, n_var2);EXEC SQLEXECUTE IMMEDIATE:sqlstr

4、;EXEC SQLCOMMIT;使用綁定定變量的的語句strcpy(sqlstr,insert intotest(num1,num2)values (:v1, :v2);EXEC SQLPREPARE sql_stmt FROM:sqlstr;EXEC SQLEXECUTE sql_stmt USING :n_var1, :n_var2;EXEC SQLCOMMIT;綁定變量量的注意意事項注意：1、不要使使用數(shù)據(jù)據(jù)庫級的的變量綁綁定參數(shù)數(shù)cursor_sharing來強制綁定，無論其其值為force還是similar2、有些帶帶 0性能優(yōu)于于selectcount(*)from tab；盡量少

5、嵌嵌套子查查詢，這這種查詢詢會消耗耗大量的的CPU資源；對對于有比比較多or運算的查查詢，建建議分成成多個查查詢，用用unionall聯(lián)結(jié)起來來. 盡量量多用commit語句提交交事務，可以及及時釋放放資源、解鎖、釋放放日志空空間、減減少管理理花費；在頻繁繁的、性性能要求求比較高高的數(shù)據(jù)操作作中，盡盡量避免免遠程訪訪問，如如數(shù)據(jù)庫庫鏈等，訪問頻頻繁的表表可以常駐內(nèi)內(nèi)存：altertablecache； 在Oracle中動態(tài)執(zhí)執(zhí)行SQL，盡量用用execute方式，不不用dbms_sql包。sql語句的編編寫原則則和優(yōu)化化在編寫SQL語句時我我們應清清楚優(yōu)化化器根據(jù)據(jù)何種原原則來使使用索引引，這

6、有有助于寫寫出高性性能的SQL語句。SQL語句的編編寫原則則和SQL語句的優(yōu)優(yōu)化，請請跟我一一起學習習以下幾幾方面：避免復雜雜的多表表關(guān)聯(lián)selectfrom user_filesuf,df_money_filesdm,cw_charge_record ccwhereuf.user_no =dm.user_noanddm.user_no= cc.user_noandandnot exists(select)?很難優(yōu)化化，隨著著數(shù)據(jù)量量的增加加性能的的風險很很大。避免使用用耗費資資源的操操作帶有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL語句會啟啟動SQL

7、引擎執(zhí)行行耗費資資源的排排序(SORT)功能.DISTINCT需要一次次排序操操作,而其他的的至少需需要執(zhí)行行兩次排序.例如,一個UNION查詢,其中每個個查詢都都帶有GROUPBY子句, GROUP BY會觸發(fā)嵌嵌入排序序(NESTED SORT) ;這樣,每個查詢需要要執(zhí)行一一次排序序,然后在執(zhí)執(zhí)行UNION時,又一個唯唯一排序(SORTUNIQUE)操作被執(zhí)執(zhí)行而且且它只能能在前面面的嵌入入排序結(jié)束束后才能能開始執(zhí)執(zhí)行.嵌入的排排序的深深度會大大大影響響查詢的效率率.通常,帶有UNION,MINUS,INTERSECT的SQL語句都可可以用其他方方式重寫寫.例如:低效:SELECTDIS

8、TINCTDEPT_NO,DEPT_NAMEFROM DEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROM DEPTDWHEREEXISTS(SELECTXFROM EMPEWHEREE.DEPT_NO=D.DEPT_NO);用EXISTS替換DISTINCT用UNION-ALL替換UNION( if possible)當SQL語句需要要UNION兩個查詢詢結(jié)果集集合時,這兩個結(jié)結(jié)果集合合會以UNION-ALL的方式被被合并,然后在輸輸出最終終結(jié)果前前進行排排序.舉例:低效：SELECTACCT_NUM, BALANC

9、E_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONSELECTACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95高效:SELECTACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONALLSELECTACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-952.給優(yōu)化器器更明確

10、確的命令令自動選擇擇索引如果表中中有兩個個以上（包括兩兩個）索索引，其其中有一一個唯一一性索引，而而其他是是非唯一一性在這種情情況下，ORACLE將使用唯唯一性索索引而完完全忽略略非唯一性索引引舉例:SELECTENAMEFROM EMPWHEREEMPNO= 2326ANDDEPTNO= 20 ;這里，只只有EMPNO上的索引引是唯一一性的，所以EMPNO索引將用來來檢索記記錄TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP_NO_IDX至少要包包含組合合索引的的第一列列如果索引引是建立立在多個個列上,只有在它它的第一一個列(leadingcolumn)

11、被where子句引用用時,優(yōu)化器才才會選擇擇使用該該索引.SQL createtablemultiindexusage(inda number, indbnumber ,descrvarchar2(10);Tablecreated.SQL createindexmultindexonmultiindexusage(inda,indb);Indexcreated.SQL setautotracetraceonlySQLselect*frommultiindexusagewhereinda= 1;ExecutionPlan-0SELECT STATEMENTOptimizer=CHOOSE10TA

12、BLEACCESS(BY INDEX ROWID)OFMULTIINDEXUSAGE21INDEX (RANGESCAN)OFMULTINDEX(NON-UNIQUE)SQL select* frommultiindexusagewhereindb =1;ExecutionPlan-0SELECT STATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL) OF MULTIINDEXUSAGE 很明顯,當僅引用用索引的的第二個個列時,優(yōu)化器使使用了全全表掃描描而忽略略了索引引避免在索索引列上上使用函函數(shù)WHERE子句中，如果索索引列是是函數(shù)的的一部分分優(yōu)化化器將不

13、不使用索引引而使用用全表掃掃描舉例:低效：SELECTFROM DEPTWHERESAL*12 25000;高效:SELECTFROM DEPTWHERESAL25000/12;避免使用用前置通通配符WHERE子句中,如果索引引列所對對應的值值的第一一個字符符由通配符(WILDCARD)開始,索引將不不被采用用. SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHEREUSER_NO LIKE%109204421;在這種情情況下，ORACLE將使用全全表掃描描.避免在索索引列上上使用NOT通常，我我們要避避免在索索引列上上使用NOT, NOT會產(chǎn)生

14、在在和在索引列上上使用函函數(shù)相同同的影響響.當ORACLE”遇到”NOT,他就會停止使使用索引引轉(zhuǎn)而執(zhí)執(zhí)行全表表掃描.舉例:低效: (這里,不使用索索引)SELECTFROM DEPTWHEREDEPT_CODENOT =0; 高效: (這里,使用了索索引)SELECTFROM DEPTWHEREDEPT_CODE0;避免在索索引列上上使用ISNULL和ISNOTNULL避免在索索引中使使用任何何可以為為空的列列，ORACLE將無法使使用該索引對于單單列索引引，如果果列包含含空值，索引中中將不存存在此記記錄.對于復合合索引，如果每每個列都都為空，索引中中同樣不不存在此此記錄.如果至少少有一個個

15、列不為為空，則則記錄存存在于索索引中如果唯一一性索引引建立在在表的A列和B列上,并且表中中存在一一條記錄的A,B值為(123,null) ,ORACLE將不接受受下一條條具有相相同A,B值（123,null）的記錄錄(插入).然而如果果所有的的索引列列都為空，ORACLE將認為整整個鍵值值為空而而空不等等于空.因此你可可以插入1000條具有相相同鍵值值的記錄錄,當然它們們都是空空!因為空值值不存在在于索引引列中,所以WHERE子句中對對索引列列進行空值比較較將使ORACLE停用該索索引.任何在where子句中使使用isnull或isnotnull的語句優(yōu)優(yōu)化器是是不允許使使用索引引的。避免出現(xiàn)

16、現(xiàn)索引列列自動轉(zhuǎn)轉(zhuǎn)換當比較不不同數(shù)據(jù)據(jù)類型的的數(shù)據(jù)時時, ORACLE自動對列列進行簡簡單的類型轉(zhuǎn)轉(zhuǎn)換.假設(shè)USER_NO是一個字字符類型型的索引引列.SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHEREUSER_NO =109204421這個語句句被ORACLE轉(zhuǎn)換為:SELECTUSER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERETO_NUMBER(USER_NO)=109204421因為內(nèi)部部發(fā)生的的類型轉(zhuǎn)轉(zhuǎn)換,這個索引引將不會會被用到到!在查詢時時盡量少少用格式式轉(zhuǎn)換如用WHEREa.order_n

17、o =b.order_no不用 WHERETO_NUMBER(substr(a.order_no,instr(b.order_no,.) -1)= TO_NUMBER(substr(a.order_no, instr(b.order_no, .)-1)3.減少訪問問次數(shù)減少訪問問數(shù)據(jù)庫庫的次數(shù)數(shù)當執(zhí)行每每條SQL語句時, ORACLE在內(nèi)部執(zhí)執(zhí)行了許許多工作作:解析SQL語句,估算索引引的利用用率,綁定變量量,讀數(shù)據(jù)塊塊等等.由此可見見,減少訪問問數(shù)據(jù)庫庫的次數(shù)數(shù),就能實際際上減少少ORACLE的工作量量.類比，工工程實施施使用DECODE來減少處處理時間間例如:SELECTCOUNT(*)，

18、SUM(SAL)FROMEMPWHEREDEPT_NO =0020ANDENAMELIKESMITH%;SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO =0030ANDENAMELIKESMITH%;你可以用用DECODE函數(shù)高效效地得到到相同結(jié)結(jié)果SELECTCOUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,X,NULL)D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL,SUM(DECODE(DEPT

19、_NO,0030,SAL,NULL) D0030_SALFROM EMPWHEREENAMELIKE SMITH%;減少對表表的查詢詢在含有子子查詢的的SQL語句中,要特別注注意減少少對表的的查詢.例如:低效SELECTTAB_NAMEFROM TABLESWHERETAB_NAME= (SELECT TAB_NAMEFROM TAB_COLUMNSWHEREVERSION =604)ANDDB_VER= (SELECT DB_VERFROM TAB_COLUMNSWHEREVERSION =604)高效SELECTTAB_NAMEFROM TABLESWHERE(TAB_NAME,DB_VE

20、R)= (SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHEREVERSION =604)4.細節(jié)上的的影響WHERE子句中的的連接順順序ORACLE采用自下下而上的的順序解解析WHERE子句,根據(jù)這個個原理,當在WHERE子句中有有多個表表聯(lián)接時時，WHERE子句中排排在最后的的表應當當是返回回行數(shù)可可能最少少的表，有過濾濾條件的的子句應放在在WHERE子句中的的最后。如：設(shè)從從emp表查到的的數(shù)據(jù)比比較少或或該表的的過濾條條件比較較確定，能大大大縮小查查詢范圍圍，則將將最具有有選擇性性部分放放在WHERE子句中的的最后：select*from empe,d

21、eptdwhered.deptno10ande.deptno=30;如果dept表返回的的記錄數(shù)數(shù)較多的的話，上上面的查查詢語句句會比下下面的查查詢語句句響應快快得多。select*from empe,deptdwheree.deptno=30andd.deptno10;WHERE子句函數(shù)、表表達式使使用最好不要要在WHERE子句中中使用函函或表達達式，如如果要使使用的話話，最好好統(tǒng)一使使用相同同的表達達式或函函數(shù)，這這樣便于于以后使使用合理理的索引引。Orderby語句ORDERBY語句決定定了Oracle如何將返返回的查查詢結(jié)果果排序。Orderby語句對要要排序的的列沒有有什么特特別的限

22、限制，也也可以將將函數(shù)加加入列中中（象聯(lián)聯(lián)接或者者附加等等）。任何在Orderby語句的非非索引項項或者有有計算表表達式都都將降低低查詢速速度。仔細檢查查orderby語句以找找出非索索引項或或者表達達式，它它們會降降低性能能。解決決這個問問題的辦辦法就是是重寫orderby語句以使使用索引引，也可可以為所所使用的的列建立立另外一一個索引引，同時時應絕對對避免在在orderby子句中使使用表達達式。聯(lián)接列對于有聯(lián)聯(lián)接的列列，即使使最后的的聯(lián)接值值為一個個靜態(tài)值值，優(yōu)化化器是不會使使用索引引的。select*from employsswherefirst_name|last_name =Beil

23、lCliton;系統(tǒng)優(yōu)化化器對基基于last_name創(chuàng)建的索索引沒有有使用。當采用下下面這種種SQL語句的編編寫，Oracle系統(tǒng)就可可以采用用基于last_name創(chuàng)建的索索引。select*from employeewherefirst_name =Beill andlast_name=Cliton;帶通配符符（%）的like語句通配符（%）在搜尋尋詞首出出現(xiàn)，Oracle系統(tǒng)不使使用last_name的索引。select*from employee where last_namelike %cliton%;在很多情情況下可可能無法法避免這這種情況況，但是是一定要要心中有有底，通配符符如

24、此使使用會降降低查詢詢速度。然而當當通配符符出現(xiàn)在在字符串其他他位置時時，優(yōu)化化器就能能利用索索引。在在下面的的查詢中中索引得到了了使用：select*from employee where last_namelike c%;用Where子句替換換HAVING子句避免使用用HAVING子句, HAVING只會在檢檢索出所所有記錄錄之后才才對結(jié)果果集進行過過濾.這個處理理需要排排序,總計等操操作.如果能通通過WHERE子句限制記錄的的數(shù)目,那就能減減少這方方面的開開銷.例如:低效:SELECTREGION，AVG(LOG_SIZE)FROM LOCATIONGROUPBYREGIONHAVING

25、REGION REGION!=SYDNEYANDREGION != PERTH高效SELECTREGION，AVG(LOG_SIZE)FROM LOCATIONWHEREREGIONREGION != SYDNEYANDREGION != PERTHGROUPBYREGION 順序WHERE GROUP HAVING用NOTEXISTS替代NOTIN在子查詢詢中,NOT IN子句將執(zhí)執(zhí)行一個個內(nèi)部的的排序和和合并.無論在哪哪種情況況下,NOT IN都是最低低效的(因為它對對子查詢詢中的表表執(zhí)行了了一個全全表遍歷歷).使用NOTEXISTS子子句可可以有效效地利用用索引。盡可能能使用NOTEXI

26、STS來代替NOTIN，盡管二二者都使使用了NOT（不能使使用索引引而降低低速度），NOTEXISTS要比NOTIN查查詢效率率更高。例如:語句1SELECTdname, deptnoFROM deptWHEREdeptnoNOT IN (SELECTdeptno FROMemp);語句2SELECTdname, deptnoFROM deptWHERENOTEXISTS(SELECT deptnoFROM empWHEREdept.deptno= emp.deptno);2要比1的執(zhí)行性性能好很很多。因為1中對emp進行了full table scan,這是很浪浪費時間間的操作作。而且且1中

27、沒有用用到emp的index， 因為為沒有where子句。而而2中的語句句對emp進行的是是縮小范范圍的查查詢。用索引提提高效率率索引是表表的一個個概念部部分,用來提高高檢索數(shù)數(shù)據(jù)的效效率，ORACLE使用了一個個復雜的的自平衡衡B-tree結(jié)構(gòu).通常,通過索引引查詢數(shù)數(shù)據(jù)比全全表掃描要快快.當ORACLE找出執(zhí)行行查詢和和Update語句的最最佳路徑徑時,ORACLE優(yōu)化器將將使用索索引.同樣在聯(lián)聯(lián)結(jié)多個個表時使使用索引引也可以以提高效率率.另一個使使用索引引的好處處是,它提供了了主鍵(primarykey)的唯一性性驗證。通常,在大型表表中使用用索引特特別有效效.當然,你也會發(fā)發(fā)現(xiàn),在掃描

28、小小表時,使用索引引同樣能能提高效效率.雖然使用用索引能能得到查查詢效率率的提高,但是我們們也必須須注意到到它的代代價.索引需要要空間來來存儲,也需要定期維護護,每當有記記錄在表表中增減減或索引引列被修修改時,索引本身身也會被修改改.這意味著著每條記記錄的INSERT,DELETE,UPDATE將為此多付出出4 ,5次的磁盤盤I/O.因為索引引需要額額外的存存儲空間間和處理理,那些不必必要的索索引反而而會使查查詢反應應時間變變慢.。定期的的重構(gòu)索索引是有必要要的。避免在索索引列上上使用計計算WHERE子句中，如果索索引列是是函數(shù)的的一部分分優(yōu)化化器將不不使用索引引而使用用全表掃掃描低效：SEL

29、ECTFROMDEPTWHERESAL*12 25000;高效:SELECTFROM DEPTWHERESAL 25000/12;用=替代如果DEPTNO上有一個個索引。高效:SELECT*FROM EMPWHEREDEPTNO=4 低效:SELECT*FROM EMPWHEREDEPTNO3通過使用用=、=等，避免免使用NOT命令例子：select*from employee wheresalary3000;對這個查查詢，可可以改寫寫為不使使用NOT：select*from employee wheresalary3000;雖然這兩兩種查詢詢的結(jié)果果一樣，但是第第二種查查詢方案案會比第第一種

30、查查詢方案案更快些些。第二二種查詢詢允許Oracle對salary列使用索索引，而而第一種種查詢則則不能使使用索引引。如果有其其它辦法法，不要要使用子子查詢。用TRUNCATE替代DELETE當刪除表表中的記記錄時,在通常情情況下,回滾段(rollbacksegments)用來存放放可以被被恢復的的信息.如果你沒沒有COMMIT事務,ORACLE會將數(shù)據(jù)據(jù)恢復到到刪除之之前的狀狀態(tài)(準確地說是是恢復到到執(zhí)行刪刪除命令令之前的的狀況)而當運用用TRUNCATE時,回滾段不不再存放放任何可可被恢復復的信息.當命令運運行后,數(shù)據(jù)不能能被恢復復.因此很少少的資源源被調(diào)用用,執(zhí)行時間間也會很很短.比如有

31、的的表PHONE_NO字段是CHAR型,而且創(chuàng)建建有索引引，但在WHERE條件中忘忘記了加加引號，就不會會用到索索引。WHEREPHONE_NOHEREPHONE_NO符型字字段的引引號優(yōu)化EXPORT和IMPORT使用較大大的BUFFER(比如10MB ,10,240,000)可以提高高EXPORT和IMPORT的速度;ORACLE將盡可能能地獲取取你所指指定的內(nèi)內(nèi)存大小小,即使在內(nèi)內(nèi)存不滿足,也不會報報錯.這個值至至少要和和表中最最大的列列相當,否則列值會被被截斷;*優(yōu)化Tools*優(yōu)化器與與執(zhí)行計計劃Oracle在執(zhí)行一一個SQL之前,

32、首先要分分析一下下語句的的執(zhí)行計計劃,然后再按按執(zhí)行計計劃去執(zhí)執(zhí)行。分分析語句句的執(zhí)行行計劃的的工作是由優(yōu)化化器(Optimizer)來完成的的Oracle的優(yōu)化器器共有兩兩種的優(yōu)優(yōu)化方式式,即基于規(guī)則則的優(yōu)化化方式(Rule-BasedOptimization,簡稱為RBO)和基于代價價的優(yōu)化方式(Cost-BasedOptimization,簡稱為CBO)。A、RBO方式：優(yōu)優(yōu)化器在在分析SQL語句時,所遵循的的是Oracle內(nèi)部預定定的一些規(guī)規(guī)則。比比如我們們常見的的,當一個where子句中的的一列有有索引時時去走索引。B、CBO方式：是是看語句句的代價價(Cost)了,這里的代代價主要

33、要指Cpu和內(nèi)存。優(yōu)化器器在判斷斷是否用用這種方方式時,主要參照照的是表表及索引引的統(tǒng)計計信息，很多的時時侯過期期統(tǒng)計信信息會令令優(yōu)化器器做出一一個錯誤誤的執(zhí)行行計劃在在Oracle8及以后的的版本,Oracle推薦用CBO的方式。在Oracle10g中,取消了RBO的支持。優(yōu)化器與與執(zhí)行計計劃Rule:即走基于于規(guī)則的的方式Choose:默認的情情況下Oracle用的便是是這種方方式。當當一個表表或或索索引有統(tǒng)計信息息,則走CBO的方式,如果表或或索引沒沒統(tǒng)計信信息,表又不是是特別的的小,而且相應應的列有有索引時時,那么就走走索引,走RBO的方式FirstRows:它與Choose方式是類類

34、似的,所不同的的是當一一個表有有統(tǒng)計信信息時,它將是以以最快的的方式返返回查詢詢的最先先的幾行行,從總體上上減少了了響應時時間AllRows:all_rows是oracle優(yōu)化器默默認的模模式,它將選擇擇一種在在最短時時間內(nèi)返回回所有數(shù)數(shù)據(jù)的執(zhí)執(zhí)行計劃劃,它將基于于整體成成本的考考慮.first_rows_n:first_rows_n是根據(jù)成成本而不不是基于于硬編碼碼的規(guī)則則來選擇擇執(zhí)行計劃劃.n可以是1,10,100,1000或者直接接用first_rows(n) hint指定任意意正數(shù).這里的n是我們想想獲取結(jié)結(jié)果集的的前n條記錄,這種需求求在很多多分頁語語句的需求求中會碰碰到.Autot

35、race解讀Current mode:對于修改改的數(shù)據(jù)據(jù)從數(shù)據(jù)據(jù)段中讀讀Read-consistentmode:讀一致性性模式Physicalblock:物理塊（如8192字節(jié)）Recursivecalls:嵌套調(diào)用用次數(shù)在SQLPLUS配置AUTOTRACE在SQLPLUS配置AUTOTRACE1、首先創(chuàng)建建PLUSTRACE角色并且且賦給public：Sql $ORACLE_HOME/sqlplus/admin/plustrce.sql2、 賦權(quán)權(quán)限給用用戶Sql grant plustracetopublic（預賦權(quán)權(quán)的用戶戶名）;3、以SYSTEM用戶創(chuàng)建建PLAN_TABLE表Sql

36、 $ORACLE_HOME/rdbms/admin/utlxplan.sqlSql createpublicsynonymplan_table forplan_table;Sql grant allonplan_table to public;在每個用用戶下設(shè)設(shè)置AUTOTRACE可顯示其其執(zhí)行計計劃。SQL selectename,dnamefrom emp,deptwhere emp.deptno=dept.deptnoanddept.dname in (ACCOUNTING,RESEARCH,SALES,OPERATIONS);ExecutionPlan-0SELECT STATEMEN

37、TOptimizer=CHOOSE10NESTEDLOOPS21TABLE ACCESS(FULL)OFEMP31TABLE ACCESS(BYINDEXROWID) OF DEPT43INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)最起碼要要解決全全表掃描描問題改變where條件的次次序一般般沒有用用優(yōu)化器與與執(zhí)行計計劃SQL調(diào)整的目目標去掉不必必要的大大型表的的全表掃掃描。緩存小型型表的全全表掃描描。校驗優(yōu)化化索引的的使用。檢驗優(yōu)化化的連接接技術(shù)。以上目標標任務將將占據(jù)SQL調(diào)整90%以上的工工作。理論上來來說比NL與SMJ更高效，而且只只用在CBO優(yōu)化器中中。較小的r

38、owsource被用來構(gòu)構(gòu)建hash table與bitmap，第2個rowsource被用來被被hansed，并與第第一個rowsource生成的hash table進行匹配配，以便便進行進進一步的的連接。Bitmap被用來作作為一種種比較快快的查找找方法，來檢查查在hash table中是否有有匹配的的行。特特別的，當hash table比較大而而不能全全部容納納在內(nèi)存存中時，這種查查找方法法更為有有用。這這種連接接方法也也有NL連接中所所謂的驅(qū)驅(qū)動表的的概念，被構(gòu)建建為hash table與bitmap的表為驅(qū)驅(qū)動表，當被構(gòu)構(gòu)建的hash table與bitmap能被容納納在內(nèi)存存中時，

39、這種連連接方式式的效率率極高。要使哈希希連接有有效，需需要設(shè)置置HASH_JOIN_ENABLED=TRUE，缺省情情況下該該參數(shù)為為TRUE，另外，不要忘忘了還要要設(shè)置hash_area_size參數(shù)，以以使哈希希連接高高效運行行，因為為哈希連連接會在在該參數(shù)數(shù)指定大大小的內(nèi)內(nèi)存中運運行，過過小的參參數(shù)會使使哈希連連接的性性能比其其他連接接方式還還要低。哈希連接接(HashJoin,HJ)HASH連接的的例子：SQL explainplanforselect/*+use_hash(emp) */ empnofrom emp,deptwhereemp.deptno =dept.deptno;Q

40、ueryPlan-SELECTSTATEMENT CHOOSE Cost=3HASH JOINTABLEACCESSFULLDEPTTABLEACCESSFULLEMP在哪種情情況下用用哪種連連接方法法比較好好：排序- -合并連接接(SortMergeJoin,SMJ)：對于非等等值連接接，這種種連接方方式的效效率是比比較高的的。如果在關(guān)關(guān)聯(lián)的列列上都有有索引，效果更更好。對將2個較大的的rowsource做連接，該連接接方法比比NL連接要好好些。但是如果果sort merge返回的rowsource過大，則則又會導導致使用用過多的rowid在表中查查詢數(shù)據(jù)據(jù)時，數(shù)數(shù)據(jù)庫性性能下降降，因為為過

41、多的的I/O。 嵌套循環(huán)環(huán)(Nested Loops,NL)：如果driving rowsource(外部表)比較小，并且在在innerrowsource(內(nèi)部表)上有唯一一索引，或有高高選擇性性非唯一一索引時時，使用用這種方方法可以以得到較較好的效效率。NESTEDLOOPS有其它連連接方法法沒有的的的一個個優(yōu)點是是：可以以先返回回已經(jīng)連連接的的行，而而不必等等待所有有的連接接操作處處理完才才返回數(shù)數(shù)據(jù)，這這可以實實現(xiàn)快速速的響應應時間?？?結(jié)在哪種情情況下用用哪種連連接方法法比較好好：哈希連接接(HashJoin,HJ)：此方法是是在oracle7后來引入入的，使使用了比比較先進進的連接接

42、理論，其效率應應該好于于其它2種連接，但是這這種連接接只能用用在CBO優(yōu)化器中中，而且且需要設(shè)設(shè)置合適適的hash_area_size參數(shù)，才才能取得較好好的性能能。在2個較大的的rowsource之間連接接時會取取得相對對較好的的效率，在一個rowsource較小時則則能取得得更好的的效率。只能用于于等值連連接中總 結(jié)要為一個個語句生生成執(zhí)行行計劃，可以有有3種方法：1)最簡單單的辦法法Sql setautotraceonSql select* fromdual;執(zhí)行完語語句后，會顯示示explain plan與 統(tǒng)計計信息。這個語句句的優(yōu)點點就是它它的缺點點，這樣樣在用該該方法查查看執(zhí)行行

43、時間較較長的sql語句時，需要等等待該語語句執(zhí)行行成功后后，才返返回執(zhí)行行計劃，使優(yōu)化化的周期期大大增增長。如果不想想執(zhí)行語語句而只只是想得得到執(zhí)行行計劃可可以采用用：Sql setautotracetraceonly這樣，就就只會列列出執(zhí)行行計劃，而不會會真正的的執(zhí)行語語句，大大大減少少了優(yōu)化化時間。雖然也也列出了了統(tǒng)計信信息，但但是因為為沒有執(zhí)執(zhí)行語句句，所以以該統(tǒng)計計信息沒沒有用處處，如果果執(zhí)行該該語句時時遇到錯錯誤，解解決方法法為：a.在要分析析的用戶戶下：Sqlplus ?rdbmsadminutlxplan.sqlb.用sys用戶登陸陸Sqlplus ?sqlplusadminpl

44、ustrce.sqlSqlplus grantplustrace to user_name;-user_name是上面所所說的分分析用戶戶如何產(chǎn)生生執(zhí)行計計劃2)用explain plan命令(1)sqlplus ?rdbmsadminutlxplan.sql(2)sqlplusexplain planset statement_id=?for select注意：此方法時時，并不不執(zhí)行sql語句，所所以只會會列出執(zhí)執(zhí)行計劃劃，不會會列出統(tǒng)統(tǒng)計信息息，并且且執(zhí)行計計劃只存存在plan_table中。所以以該語句句比起setautotrace traceonly可用性要要差。需需要用下下面的命命令

45、格式式化輸出出，所以以這種方方式我用用的不多多：如何產(chǎn)生生執(zhí)行計計劃setlinesize150setpagesize500colPLANLINEfor a120SELECTEXECORDEXEC_ORDER,PLANLINEFROM (SELECTPLANLINE, ROWNUMEXECORD,ID,RIDFROM (SELECTPLANLINE, ID,RID, LEVFROM (SELECTlpad( ,2*(LEVEL),rpad(,80,)|OPERATION| - OperationDECODE(OPTIONS,NULL,(|OPTIONS|)| - OptionsDECODE(O

46、BJECT_OWNER,null,OF | OBJECT_OWNER|.)| - OwnerDECODE(OBJECT_NAME,null,OBJECT_NAME| )| - ObjectNameDECODE(OBJECT_TYPE,null,(|OBJECT_TYPE|)|-ObjectTypeDECODE(ID,0,OPT_MODE:)|-OptimizerDECODE(OPTIMIZER,null,ANALYZED,OPTIMIZER)|DECODE(NVL(COST,0)+NVL(CARDINALITY,0)+NVL(BYTES,0),0,null,(COST=|TO_CHAR(COS

47、T)|,CARD=|TO_CHAR(CARDINALITY)|,BYTES=|TO_CHAR(BYTES)|)PLANLINE,ID, LEVEL LEV,(SELECT MAX(ID)FROM PLAN_TABLEPL2CONNECT BY PRIOR ID =PARENT_IDANDPRIORSTATEMENT_ID =STATEMENT_IDSTARTWITH ID =PL1.IDANDSTATEMENT_ID =PL1.STATEMENT_ID)RIDFROM PLAN_TABLEPL1CONNECT BY PRIOR ID =PARENT_IDANDPRIORSTATEMENT_ID

48、 =STATEMENT_IDSTARTWITH ID =0ANDSTATEMENT_ID =aaa)ORDERBYRID, -LEV)ORDERBYID;2)用explain plan命令以上2種方法只只能為在在本會話話中正在在運行的的語句產(chǎn)產(chǎn)生執(zhí)行行計劃，即我們們需要已已經(jīng)知道道了哪條條語句運運行的效效率很差差，我們們是有目目的只對對這條SQL語句去優(yōu)優(yōu)化。在在很多情情況下，我們只只會聽一一個客戶戶抱怨說說現(xiàn)在系系統(tǒng)運行行很慢，而我們們不知道道是哪個個SQL引起的。此時有有許多現(xiàn)現(xiàn)成的語語句可以以找出耗耗費資源源比較多多的語句句，如：SELECTADDRESS,substr(SQL_TEXT

49、,1,20)Text,buffer_gets,executions,buffer_gets/executions AVGFROM v$sqlareaWHEREexecutions0ANDbuffer_gets100000ORDERBY5;從而對找找出的語語句進行行進一步步優(yōu)化。還可以以為一個個正在運運行的會會話中運運行的所所有SQL語句生成成執(zhí)行計計劃，這這需要對對該會話話進行跟跟蹤，產(chǎn)產(chǎn)生trace文件，然然后對該該文件用用tkprof程序格式式化一下下，這種種得到執(zhí)執(zhí)行計劃劃的方式式很有用用，因為為它包含含其它額額外信息息，如SQL語句執(zhí)行行的每個個階段(如Parse、Execute、Fe

50、tch)分別耗費費的各個個資源情情況(如CPU、DISK、elapsed等)。如何產(chǎn)生生執(zhí)行計計劃通過執(zhí)行行計劃如如何判斷斷驅(qū)動表表：在執(zhí)行計計劃中，需要知知道哪個個操作是是先執(zhí)行行的，哪哪個操作作是后執(zhí)執(zhí)行的，這對于于判斷哪哪個表為為驅(qū)動表表有用處處。判斷斷之前，如果對對表的訪訪問是通通過rowid，且該rowid的值是從從索引掃掃描中來來得，則則將該索索引掃描描先從執(zhí)執(zhí)行計劃劃中暫時時去掉。然后在在執(zhí)行計計劃剩下下的部分分中，判判斷執(zhí)行行順序的的指導原原則就是是：最右、最最上的操操作先執(zhí)執(zhí)行。具體解解釋如下下：得到去除除妨礙判判斷的索索引掃描描后的執(zhí)執(zhí)行計劃劃：ExecutionPlan-0SELECTSTATEMENT Optimizer=CHOOSE10MERGEJO