ORACLE執(zhí)行計劃和SQL調優(yōu)

1、ORACLE 執(zhí)行計劃和SQL調優(yōu)內容安排n第一部分：背景知識n第二部分：SQL調優(yōu)n第三部分：工具介紹第一部分 背景知識執(zhí)行計劃的相關概念Rowid的概念 nrowid是一個偽列，既然是偽列，那么這個列就不是用戶定義，而是系統(tǒng)自己給加上的。對每個表都有一個rowid的偽列，但是表中并不物理存儲ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對該列的值進行修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫，則rowid在該行的生命周期內是唯一的，即即使該行產(chǎn)生行遷移，行的rowid也不會改變。 Recursive SQL概念 n有時為了執(zhí)行用戶發(fā)出的一個sql語句，Oracle必須

2、執(zhí)行一些額外的語句，我們將這些額外的語句稱之為recursive calls或recursive SQL statements。如當一個DDL語句發(fā)出后，ORACLE總是隱含的發(fā)出一些recursive SQL語句，來修改數(shù)據(jù)字典信息，以便用戶可以成功的執(zhí)行該DDL語句。當需要的數(shù)據(jù)字典信息沒有在共享內存中時，經(jīng)常會發(fā)生Recursive calls，這些Recursive calls會將數(shù)據(jù)字典信息從硬盤讀入內存中。用戶不比關心這些recursive SQL語句的執(zhí)行情況，在需要的時候，ORACLE會自動的在內部執(zhí)行這些語句。當然DML語句也都可能引起recursive SQL。簡單的說，我

3、們可以將觸發(fā)器視為recursive SQL。 Row Source and PredicatenRow Source(行源)：用在查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數(shù)據(jù)的集合；也可以是表的部分行數(shù)據(jù)的集合；也可以為對上2個row source進行連接操作(如join連接)后得到的行數(shù)據(jù)集合。nPredicate(謂詞)：一個查詢中的WHERE限制條件Driving TablenDriving Table(驅動表)：該表又稱為外層表(OUTER TABLE)。這個概念用于嵌套與HASH連接中。如果該row source返回較多的行數(shù)據(jù)，則對所有的后續(xù)操作有負面影響。

4、注意此處雖然翻譯為驅動表，但實際上翻譯為驅動行源(driving row source)更為確切。一般說來，是應用查詢的限制條件后，返回較少行源的表作為驅動表，所以如果一個大表在WHERE條件有有限制條件(如等值限制)，則該大表作為驅動表也是合適的，所以并不是只有較小的表可以作為驅動表，正確說法應該為應用查詢的限制條件后，返回較少行源的表作為驅動表。在執(zhí)行計劃中，應該為靠上的那個row source，后面會給出具體說明。Probed TablenProbed Table(被探查表)：該表又稱為內層表(INNER TABLE)。在我們從驅動表中得到具體一行的數(shù)據(jù)后，在該表中尋找符合連接條件的行。

5、所以該表應當為大表(實際上應該為返回較大row source的表)且相應的列上應該有索引。 組合索引(concatenated index) n由多個列構成的索引，如create index idx_emp on emp(col1, col2, col3, )，則我們稱idx_emp索引為組合索引。在組合索引中有一個重要的概念：引導列(leading column)，在上面的例子中，col1列為引導列。當我們進行查詢時可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，這樣的限制條件都會使用索引，但是”where col2 = ?

6、”查詢就不會使用該索引。所以限制條件中包含先導列時，該限制條件才會使用該組合索引。 可選擇性(selectivity)n比較一下列中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。如果該列的”唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在可選擇性高的列上進行查詢時，返回的數(shù)據(jù)就較少，比較適合使用索引查詢。 oracle訪問數(shù)據(jù)的存取方法訪問數(shù)據(jù)的存取方法 n全表掃描（Full Table Scans, FTS）n通過ROWID的表存取 (Table Access by ROWID )n索引掃描（Index Scan） 索引

7、掃描（Index Scan）n索引唯一掃描(index unique scan)n索引范圍掃描(index range scan)n索引全掃描(index full scan)n索引快速掃描(index fast full scan) 表訪問方式n全表掃描：讀取表中每一條記錄，順序讀??；n散列獲取：使用符號散列主鍵來為帶有匹配散列值表中的記錄創(chuàng)建ROWID ；nROWID訪問：通過指定的ROWID的方式在表中選定一個單獨的記錄；是訪問記錄的最快方式；表之間的連接表之間的連接 nrow source(表)之間的連接順序對于查詢的效率有非常大的影響。通過首先存取特定的表，即將該表作為驅動表，這樣可

8、以先應用某些限制條件，從而得到一個較小的row source，使連接的效率較高，這也就是我們常說的要先執(zhí)行限制條件的原因。一般是在將表讀入內存時，應用where子句中對該表的限制條件。 SQL 執(zhí)行的步驟n解析：安全性檢查，語法檢查；n創(chuàng)建：評估多個執(zhí)行計劃，并選擇一個最優(yōu)的執(zhí)行計劃；n執(zhí)行：捆綁變量，執(zhí)行已經(jīng)創(chuàng)建的執(zhí)行計劃；n獲?。韩@取結果集，進行轉換，排序等；索引訪問方式n索引范圍掃描：指從索引中讀取多個rowid，是最常見到的方式；n單個索引掃描：指從索引中讀取一個單獨的rowid；n降序索引范圍掃描：指按降序的方式從索引中讀取多個rowid；導致排序的操作nOrder by 子句nGr

9、oup by 子句nSelect distinct 子句nUnion 或 minus 操作n優(yōu)化器調用sort merge join 操作n創(chuàng)建索引SQL優(yōu)化器n概念：是一個為所有的sql語句創(chuàng)建執(zhí)行計劃的工具。n目的：生成最快的，消耗資源最少的執(zhí)行計劃。n兩種優(yōu)化器：基于規(guī)則的優(yōu)化器（RBO)，基于成本的優(yōu)化器（CBO）。優(yōu)化器模式nRule 模式：完全基于數(shù)據(jù)字典生成執(zhí)行計劃；最古老、比較穩(wěn)定；nChoose 模式：默認的優(yōu)化器模式。根據(jù)統(tǒng)計數(shù)據(jù)的存在與否確定調用哪一個優(yōu)化器；nFirst_rows 模式：基于成本的優(yōu)化器模式，以最快的速度返回記錄；nAll_rows 模式：基于成本的優(yōu)化

10、器模式，確保總體時間最短，使用的資源最小；設置優(yōu)化器模式的方法nInit.ora參數(shù) optimizer_mode = rule/choose/all_rows/first_rows;n在會話層使用alter session set optimizer_goal= rule/choose/all_rows/first_rows;n在SQL中添加提示/*+ rule/all_rows/first_rows */n設置choose模式時候，將根據(jù)是否存在表或索引的統(tǒng)計資料來決定選擇RBO或CBO；第二部分 SQL 調整SQL 調整的目標n去掉不必要的大表全表掃描；n緩存小表全表掃描；n盡量使用主機

11、變量代替直接量，減少SQL語句的解析時間；n優(yōu)化索引的使用；n優(yōu)化表連接方法；n優(yōu)化子查詢；常見可能導致全表掃描的操作n使用null條件的查詢：where xxx is null;n對沒有索引的字段查詢；n帶有l(wèi)ike條件的查詢：where xxx like %x；n帶有not equals條件的查詢： , !=, not in等（除非字段分布不平衡，而且存在字段矩形圖）n內置函數(shù)使索引無效：substr()，to_char()等；n使用all_rows提示；n使用parallel 提示；基于 RBO 調整設置RBO模式的方法nInit.ora參數(shù) optimizer_mode = rule/

12、choose;n在會話層使用alter session set optimizer_goal= rule/choose;n在SQL中添加提示 /*+ rule */n設置choose模式時候，將根據(jù)是否存在表或索引的統(tǒng)計資料來決定選擇RBO或CBO；RBO特性n總是使用索引（不識別位圖索引或基于函數(shù)的索引）n總是從驅動表開始n只有在不可避免的情況下，才使用全表掃描n索引選擇的隨機特性基于RBO的調整原則n驅動表的設置：在RBO中，驅動表是from子句的最后一個表；驅動表應該是返回記錄最少的那個表；nWhere子句設置：限制性最強的布爾表達式放在最底層；n添加基于成本的提示，來獲得更快的執(zhí)行計劃

13、；基于 CBO 調整設置CBO模式的方法nInit.ora參數(shù) optimizer_mode = all_rows/first_rows/choose;n在會話層使用alter session set optimizer_goal= all_rows/first_rows/choose;n在SQL中添加提示 /*+ hint */n設置choose模式時候，將根據(jù)是否存在表或索引的統(tǒng)計資料來決定選擇RBO或CBO；CBO 特性n前提條件：存在表和索引的統(tǒng)計資料；使用analyze table 和 analyze index 命令從表或索引中收集統(tǒng)計資料（表的記錄平均長度，記錄數(shù)等）；如果沒有現(xiàn)

14、存的統(tǒng)計資料，將在sql運行時收集資料，會大大降低性能；n影響CBO執(zhí)行計劃成本評估的初始化參數(shù)較多（optimizer_search_limit,optimizer_max_permutations,optimizer_index_caching,hash_area_size,hash_join_enable,hash_multiblock_io_count,star_transformation_enable,optimizer_index_cost_adj等）調整表連接表連接方法n嵌套循環(huán)連接（nested loop join）：一個小的內部表和一個外部表。比較內部表的每一條記錄和外部表

15、的每一條記錄，返回滿足條件的記錄；n散列連接（hash join）：為較小的表在RAM創(chuàng)建散列表（可以用來從較大的那個表讀取記錄）；n排序合并連接（sort merge join）：使用連接字段將兩個記錄集排序然后合并；n星型連接（star join）：幾個小型表（將組成虛擬表）和一個大型事實表，然后進行嵌套循環(huán)連接；表連接類型n等連接：標準連接；from a,b where a.f1=b.f1；oracle提供nested loop、hash join、sort merge三種連接方式；n外部連接: 通過在where子句的等式謂詞展覽館放置一個（）來實現(xiàn)；from a,b where a.f

16、1=b.f1(+)，將包括b表中不匹配的字段；n自連接：一個表與自己連接的情況；from emp a,emp b where a.f1-b.f1 =10 ；經(jīng)常調用nested loop連接；表連接類型（續(xù)）n反連接：指使用包含not in 或not exists子句的子查詢進行的連接；默認使用嵌套循環(huán)算法；n半連接：指在子查詢中使用in或exists子句時進行的操作；表連接調整原則nRBO只能調用nested loop和merge sort連接；hash join和star 連接只能在CBO中得到；n盡量不要使用not in反連接子查詢，把它替換成標準等連接，用外連接和where colum

17、n is null子句刪除多余的記錄；或者盡量替換成not exists子查詢，因為它將調用相關聯(lián)的子查詢；n半連接子查詢可以重新書寫成標準等連接，用select distinct子句刪除重復的記錄；表連接調整原則（續(xù)）n如果驅動表較小，可以完全裝入hash_area_size內存中，使用散列連接（hash join）速度比嵌套循環(huán)連接（nested loop）快；n在兩個表非常大的情況下，經(jīng)常使用嵌套循環(huán)連接（nested loop）；n生成大型結果集的查詢、不使用where子句的大表連接或表中無可用索引的查詢，經(jīng)常使用排序合并連接（srot merge)；n對于多個小的維表和一個大的事實表

18、的情況下（數(shù)據(jù)倉庫），經(jīng)常使用星型連接(star)；不同表連接方法的相對速度星型連接嵌套循環(huán)連接散列連接排序合并連接連接速度連接表中記錄的數(shù)目少多慢快調整SQL子查詢子查詢類型n標準子查詢：in 和 exists；n反連接子查詢：not in 和 not exists；n關聯(lián)子查詢：指在子查詢內部引用外部數(shù)據(jù)表；from table1 a where (selectfrom table2 b Where a.f1 = b.f1)；對于外部數(shù)據(jù)集的每一條記錄，都將重新執(zhí)行一次內部子查詢；n非關聯(lián)子查詢：指在子查詢內部不會引用外部的數(shù)據(jù)表；from table1 a where (select f

19、rom table2 b )；內部子查詢只執(zhí)行一次；子查詢調整原則n只要可能的話，盡可能的避免使用子查詢，而用標準的連接操作來代替，這樣可以使用提示來更改執(zhí)行計劃；n先考慮子查詢的合法性，再考慮進行改寫；n使用一個關聯(lián)子查詢時，in與exists子句的子查詢的執(zhí)行計劃基本相同；n在外部查詢返回相對較少的記錄時，關聯(lián)子查詢比非關聯(lián)子查詢執(zhí)行得更快；子查詢調整原則（續(xù)）n在內部子查詢只有少量的記錄時，非關聯(lián)子查詢比關聯(lián)子查詢執(zhí)行得更快；n關聯(lián)子查詢使用in子句是多余的；而非關聯(lián)子查詢使用exists子句是不恰當?shù)摹使用in子句的非關聯(lián)子查詢可以轉換為標準連接操作以及使用select distin

20、ct來刪除重復的記錄；n使用exists子句的關聯(lián)子查詢可以轉換為標準連接，但子查詢最好只能返回一個記錄；子查詢調整原則（續(xù)）n非關聯(lián)子查詢使用not exists子句是沒有意義的；n使用not in子句的非關聯(lián)子查詢可以轉sql minus子句，性能相對會高一些；n使用not in子句的關聯(lián)子查詢，可以使用帶有select distinct子句的外部連接操作改寫；各種子查詢技術總結標準子查詢反連接子查詢inexistsNot inNot exists關聯(lián)子查詢多余的自動轉換為嵌套的循環(huán)連接可以重寫為select distinct 外部連接可以重寫為select distinct 外部連接非關

21、聯(lián)子查詢自動轉換為嵌套的循環(huán)連接不適合可以重寫為minus操作符的嵌套循環(huán)連接不適合例子：使用in子句的非關聯(lián)子查詢可以使用標準連接操作以及使用select distinct來刪除重復的記錄；原sql語句：Select ename From empWhere empno in(select empno from bad_credit where bad_credit_datesysdate-365);改寫后：Select distinct /* + rule */ enameFrom emp,bad_creditWhere a.empno=b.empno and bad_credit_date

22、sysdate-365;使用提示進行調整提示簡介n歷史：第一次引入是在oracle7，用來彌補CBO的缺陷，oracle8i中工作得較好；n目的：用來更改SQL語句 的執(zhí)行計劃；n格式：select(update,delete) /*+ hints */ 或 select (update,delete) - + hint使用提示遵循的原則n注意檢查語法：select /*+ hint */ ， /*和+之間不能有空格，必須緊跟在select之后，否則無效n使用表別名：如果指定了表別名，就不能使用表名稱；n不能使用模式名稱：如果指定了模式所有者，那么提示將被忽略；n檢驗提示：如果指定不可用的訪問

23、路徑，如：first_rows優(yōu)化器模式與order by子句不兼容，那么提示將被忽略；使提示無效的條件nCluster，hash：與非簇表一起使用；nMerge_aj,push_subq,Hash_aj：不存在子查詢；nIndex：指定的索引不存在；nIndex_combine：不存在位圖索引；nParellel：調用的不是全表掃描計劃；nStar：事實表中存在不恰當索引；nUse_concat：在where子句中不存在多個索引；nUse_nl：表中不存在索引；提示之一：優(yōu)化器提示nRule：使oracle為查詢應用基于規(guī)則的優(yōu)化模式。在懷疑CBO使用了非優(yōu)化的執(zhí)行計劃時，使用rule提示；

24、它將忽略表和索引的統(tǒng)計資料；/*+ rule */nAll_rows：基于成本的優(yōu)化方法。目的是提供最佳的吞吐量和最小的資源消耗。傾向于全表掃描，不適用于OLTP系統(tǒng)；依賴于表和索引的統(tǒng)計資料；/*+ all_rows */nFirst_rows:基于成本的優(yōu)化方法。目的是提供最快的反映時間。依賴于表和索引的統(tǒng)計資料；/*+ first_rows */提示之二：表連接提示nUse_hash：對指定的表執(zhí)行一個散列連接；如果有一個表較小，通?？煊谇短籽h(huán)連接；在兩個表非常大的情況下，散列連接經(jīng)常與并行查詢連接結合使用；select /*+ use_hash(a,b) parallel(a,4)

25、parallel(b,4) */;nUse_merge：強制執(zhí)行一個排序合并操作；對表執(zhí)行全表掃描；通常與并行查詢結合使用；最適用于生成大型結果集的查詢、不使用where子句的大表連接或表中無可用索引的查詢；select /*+ use_merge(a,b) parallel(a,4) parallel(b,4) */表連接提示（續(xù)）nUse_nl：強制對目標表執(zhí)行嵌套循環(huán)連接；對包含兩個大表的連接通常最快；可以不用更改from子句表的順序來更改驅動表（使用CBO時，from子句的第一個表）；是CBO的默認行為，比較少用；/*+ use_nl(a) */nStar：強制使用星型查詢計劃；查詢中

26、至少存在三個表（一個事實表和幾個維表），而且事實表（大表）存在恰當?shù)乃饕?i可以使用位圖索引）；/*+ star */提示之三：反連接提示n反連接是在SQL語句中包含not in或not exist 子句時執(zhí)行的操作；如果子查詢返回的任何一條記錄包含空值，那么該查詢將不會返回記錄；應盡量避免使用。nHash_aj，Merge_aj：在not in子查詢的字段中不存在空值的時候，根據(jù)連接的類型，考慮使用這兩個提示之一，可以在很大程度上提高not in子查詢的性能；/*+ hash_aj */提示之四：索引提示nIndex：優(yōu)化器將使用指定的索引；如果沒有指定索引，優(yōu)化器將使用表中最佳的索引；/

27、*+ index(table,index) */nIndex_join：要求優(yōu)化器使用索引連接作為訪問路徑；nAnd_equal：如果表擁有非唯一的單獨字段索引，而且期望使用多個索引服務于該查詢，那么使用該提示將合并這些索引；至少兩個索引名，但不能超過五個；/*+ and_equal(table,index1,index2,) */索引提示（續(xù)）nIndex_asc：要求在范圍掃描中使用升序索引；優(yōu)化器的默認行為，不常用；nNo_index：強制優(yōu)化器忽略索引得存在。一般用在并行全表掃描性能高于索引范圍掃描性能的情況下；等同于full提示；nIndex_desc：要求在范圍掃描中使用降序索引；

28、如在max（）計算字段的最大值的時候使用；/*+ index_desc(table,index) */索引提示（續(xù)）nIndex_combine：強制使用位圖索引作為表的訪問路徑，對兩個位圖索引執(zhí)行ROWID交集操作。如果沒有指定索引作參數(shù)，優(yōu)化器將自動選擇最佳的位圖索引；/*+ table(bitmap1,bitmap2) */nIndex_ffs：強制使用快速完全索引掃描；如果大表中不存在被查詢字段的高層索引主鍵，比如需要選擇復合索引中第二個字段的值時，那么快速完全索引掃描總是比全表掃描速度更快；/*+ index_ffs(table,comindex) */索引提示（續(xù)）nUse_con

29、cat：要求為查詢中所有or條件使用union all執(zhí)行計劃；一般用在where子句中存在大量的or條件；提示之五：并行提示nParallel：要求表查詢以并行模式執(zhí)行；一般用在多個cpu的服務器上，與full提示一起使用；/*+ full(table) parallel(table,8) */nNoparallel：不希望對全表掃描使用并行機制，如對小表執(zhí)行的全表掃描時，使用該提示；提示之六：表訪問提示nFull：要求避開索引，調用全表掃描；讀取表中大量的數(shù)據(jù)塊時；與parallel一起使用；nHash：選擇散列掃描來訪問指定的簇表；/*+ hash */nCluster：選擇簇掃描來訪問

30、指定的簇表；/*+ cluster */nNocache：指定為keep池分配的表數(shù)據(jù)塊放置在default池的中點；很少使用；表訪問提示（續(xù)）nOrdered：要求表按照from子句指定的順序進行連接；對連接多于4個表的查詢非常有用，可以節(jié)省sql解析的時間；/*+ ordered */nOrdered_predicates：用來指定where子句中布爾條件評估的順序；nPush_subq：要求查詢數(shù)據(jù)塊中的所有子查詢在執(zhí)行計劃中盡可能早的被執(zhí)行；用在子查詢相對來說不很昂貴，并且在很大程度上降低返回到記錄數(shù)；如果子查詢使用的是排序合并連接或調用遠程表，提示將不起作用；調整索引常見的索引類型n

31、普通索引：最常見到的索引，create index idx1 on table1 (f1);n復合索引：索引建立在多個字段上；create idx2 on table2 (f1,f3);n位圖索引：使用位圖的方式存儲索引；create bitmap index idx3 on table3(f1);n基于函數(shù)的索引：create idx4 on table4(upper(f2)；索引無效n內置函數(shù)使索引無效： from table1 a where substr(a.f1,2,4) ，f1上的索引無效；n執(zhí)行一個數(shù)學函數(shù)也會使索引無效： from table1 a where a.f1*3 1000, f1上的索引無效；n不相等運算符有時也會使索引無效： from table1 a where a.f1 1000, 導致全表掃描，f1上的索引無效；nWhere 子句中使用復合函數(shù)的非第一字段將無法使用索引；索引無效（續(xù)）n帶有l(wèi)ike條件的查詢：where xxx like %x；nNull字符串與字段相連接使索引無效；where f1| = ；索引調整n使用索引來消除一些不必要（如表中沒有索引，排序合并連接）的排序操作；n通過增加索引來避免不合法的