CBO( Cost BasedOptimizer)優(yōu)化器是目前ORACLE默認(rèn)使用的優(yōu)化器，它使用統(tǒng)計(jì)信息、查詢轉(zhuǎn)換等計(jì)算各種可能的訪問(wèn)路徑成本，并生成多種備選執(zhí)行計(jì)劃，最終ORACLE選擇成本最低的作為最終執(zhí)行計(jì)劃。與舊的RBO(RuleBased Optimizer)相比，更加靈活，可根據(jù)實(shí)際情況選擇最佳執(zhí)行路徑。

但是，由于其自身非常復(fù)雜，CBO的限制以及存在的BUG非常多，這時(shí)，作為SQL開(kāi)發(fā)和優(yōu)化人員，應(yīng)該根據(jù)CBO特性，編寫(xiě)高效語(yǔ)句，以避免踩坑CBO優(yōu)化器。

本文以三類常見(jiàn)的SQL優(yōu)化問(wèn)題來(lái)探討基于ORACLE的高效SQL編寫(xiě)和優(yōu)化。

ORACLE中主要使用ROWNUM來(lái)實(shí)現(xiàn)TOP-N分頁(yè)查詢，分頁(yè)SQL編寫(xiě)有如下規(guī)則：

1. 分頁(yè)查詢一般需要排序，內(nèi)層查詢需要先ORDER BY。

   
   

2. 如果查詢TOP–N行，需要兩層嵌套：內(nèi)層先排序，在外層嵌套查詢ROWNUM，并且同一層按照WHERE ROWNUM <或<=進(jìn)行過(guò)濾。

   
   

3. 如果查詢第M行到第N行，需要三層嵌套：內(nèi)層先排序，之后外層嵌套查詢ROWNUM并且將ROWNUM取別名，比如取別名為RN，且同一層按照WHERE ROWNUM <或<=進(jìn)行過(guò)濾，之后最外層RN按照WHERE rn >或>=進(jìn)行過(guò)濾。

分頁(yè)查詢優(yōu)化要點(diǎn)

分頁(yè)查詢的排序是高代價(jià)操作，如果不能避免排序，則需要所有結(jié)果集查詢完畢后進(jìn)行排序操作后，才能進(jìn)行分頁(yè)選擇。如果能夠避免排序，則可以充分使用到ORACLE分頁(yè)查詢的COUNTSTOPKEY算法，比如找前100行，則只要找到100行整條語(yǔ)句則可結(jié)束計(jì)算，這樣就可以提升分頁(yè)查詢效率了。很顯然，高效分頁(yè)查詢必須做到：

1. 避免排序：通過(guò)創(chuàng)建索引

2. 執(zhí)行計(jì)劃使用COUNT STOPKEY算法，進(jìn)行分頁(yè)裁剪。

   
   

錯(cuò)誤的分頁(yè)SQL寫(xiě)法

分頁(yè)SQL編寫(xiě)必須遵守查詢前面說(shuō)的3個(gè)規(guī)則，如下例是錯(cuò)誤的分頁(yè)語(yǔ)句寫(xiě)法：

這條語(yǔ)句查詢前20行，應(yīng)該使用兩層嵌套規(guī)則：最內(nèi)層排序，外層查詢r(jià)ownum，并且在同一層用whererownum<或<=進(jìn)行過(guò)濾。仔細(xì)分析這條語(yǔ)句，發(fā)現(xiàn)是兩層嵌套，但是不符合“并且在同一層用whererownum<或<=進(jìn)行過(guò)濾”這個(gè)條件，此語(yǔ)句查詢r(jià)ownum之后取了別名rn,在最外層進(jìn)行rn過(guò)濾，可以從執(zhí)行計(jì)劃看到，走了全表掃描：

這里的表TM_TESTX_TEMP的列DONE_DATE有索引，但是因?yàn)槭褂昧隋e(cuò)誤的分頁(yè)SQL寫(xiě)法，導(dǎo)致執(zhí)行計(jì)劃無(wú)法使用COUNTSTOPKEY進(jìn)行裁剪（執(zhí)行計(jì)劃中未出現(xiàn)COUNTSTOPKEY），這樣ORACLE需要按照條件查詢所有的結(jié)果集，從而走索引COST更大，最終走了全表掃描。

正確的分頁(yè)SQL寫(xiě)法

如果按照規(guī)則進(jìn)行SQL編寫(xiě)，則可以完美進(jìn)行高效分頁(yè)，<=分頁(yè)只需要2層嵌套，done_date列有索引，根據(jù)條件done_date>to_date(‘20150916’,‘YYYYMMDD’)，只獲取前20行，可高效利用索引和COUNTSTOPKEY算法，改寫(xiě)完成后使用索引降序掃描，執(zhí)行時(shí)間從1.72s到0.01s,邏輯IO從42648到59，效率提升百倍。如下所示：

語(yǔ)句改寫(xiě)為外層取rownum的同時(shí)按照WHEREROWNUM <= 20進(jìn)行過(guò)濾，而不是原來(lái)的在最外層進(jìn)行過(guò)濾，符合分頁(yè)SQL編寫(xiě)規(guī)則，執(zhí)行計(jì)劃變?yōu)椋?/span>

修改完后，可以看到根據(jù)ORDERBY DONE_DATE DESC，執(zhí)行計(jì)劃走了索引降序掃描，這樣避免了排序，并且使用到了COUNTSTOPKEY算法，找到前20行，則SQL運(yùn)算結(jié)束，從而提高效率。

表關(guān)聯(lián)SQL分頁(yè)優(yōu)化

以上只是單表分頁(yè)查詢的高效SQL編寫(xiě)和優(yōu)化思路，如果是多表關(guān)聯(lián)SQL分頁(yè)，也需要遵循分頁(yè)SQL編寫(xiě)規(guī)則，優(yōu)化方式同樣是利用索引消除排序，并且能夠使用COUNTSTOPKEY算法，很顯然，要做到這些，必須以O(shè)RDERBY列所在表為驅(qū)動(dòng)表，JOIN方式為NESTEDLOOPS，這樣可全部走索引并且可使用STOPKEY算法進(jìn)行結(jié)果集裁剪，提高效率。如下例：

這條語(yǔ)句是test1和test2進(jìn)行半連接，并按照test1的object_id列進(jìn)行降序排列，最終返回test1的前10行數(shù)據(jù)。從子查詢關(guān)聯(lián)條件上看，按照object_id和object_name列關(guān)聯(lián)，沒(méi)有額外的過(guò)濾條件，從語(yǔ)句結(jié)構(gòu)上看執(zhí)行計(jì)劃應(yīng)該中表test1應(yīng)該是全表掃描，如下所示：

很顯然，這不是最佳執(zhí)行計(jì)劃：沒(méi)有消除排序，兩表都是全表掃描，所有結(jié)果集返回后，才進(jìn)行STOPKEY（SORTORDER BY STOPKEY）。前面已經(jīng)說(shuō)過(guò)，對(duì)于表關(guān)聯(lián)的分頁(yè)查詢，應(yīng)該用排序鍵所在的表為驅(qū)動(dòng)表，JOIN方式為NESTEDLOOPS，并且消除排序，根據(jù)這個(gè)思想，應(yīng)該在兩表的object_id列分別建立索引即可：

create indexidx_test1 on test1(object_id);

create indexidx_test2 on test2(object_id);

索引創(chuàng)建完畢后的執(zhí)行計(jì)劃如下：

現(xiàn)在的執(zhí)行計(jì)劃完全符合多表關(guān)聯(lián)分頁(yè)查詢優(yōu)化思路，以test1表為驅(qū)動(dòng)表，消除排序，test1和test2之間走NESTEDLOOPS，可以從執(zhí)行計(jì)劃上看出，ID=4的步驟雖然E-ROWS估算為69444行，但是實(shí)際只找到10行，也就結(jié)束了，最終邏輯讀從原先的2184降低到15，大幅度提升了效率。

因?yàn)榉猪?yè)查詢要利用到STOPKEY算法，就算除關(guān)聯(lián)條件外沒(méi)有額外的過(guò)濾條件，也可以通過(guò)索引來(lái)提升效率。

FILTER操作是執(zhí)行計(jì)劃中常見(jiàn)的操作，這種操作有兩種情況：

1. 只有一個(gè)子節(jié)點(diǎn)，那么就是簡(jiǎn)單過(guò)濾操作。

2. 有多個(gè)子節(jié)點(diǎn)，那么就是類似NESTED LOOPS操作，只不過(guò)與NESTED LOOPS差別在于，F(xiàn)ILTER內(nèi)部會(huì)構(gòu)建HASH表，對(duì)于重復(fù)匹配的，不會(huì)再次進(jìn)行循環(huán)查找，而是利用已有結(jié)果，提高效率。但是一旦重復(fù)匹配的較少，循環(huán)次數(shù)多，那么，F(xiàn)ILTER操作將是嚴(yán)重影響性能的操作，這是經(jīng)常導(dǎo)致性能問(wèn)題的原因。

NOT IN子查詢中的FILTER

對(duì)于NOTIN子查詢，在11g之前經(jīng)常會(huì)出現(xiàn)性能問(wèn)題，如下例SQL所示：

針對(duì)上面的NOTIN子查詢，如果子查詢object_id有NULL存在，則整個(gè)查詢都不會(huì)有結(jié)果，在11g之前，如果主表和子表的object_id未同時(shí)有NOTNULL約束，或都未加ISNOT NULL限制，則ORACLE會(huì)走FILTER。11g有新的ANTINA（NULLAWARE）優(yōu)化，可以對(duì)子查詢進(jìn)行UNNEST查詢轉(zhuǎn)換，從而提高效率。

對(duì)于未UNNEST的子查詢，走了FILTER，有至少2個(gè)子節(jié)點(diǎn)，執(zhí)行計(jì)劃還有個(gè)特點(diǎn)就是Predicate謂詞部分有:B1這種類似綁定變量的東西，內(nèi)部操作走類似NESTEDLOOPS操作,執(zhí)行計(jì)劃如下：

可以從執(zhí)行計(jì)劃上看到，F(xiàn)ILTER有子節(jié)點(diǎn)ID=2和ID=3，并且ID=3部分出現(xiàn)綁定變量:B1，這是典型的NOTIN子查詢未UNNEST的執(zhí)行計(jì)劃，性能很差。

11g有NULLAWARE專門(mén)針對(duì)NOTIN問(wèn)題進(jìn)行優(yōu)化（要求參數(shù)：_optimizer_squ_bottomup、_optimizer_null_aware_antijoin同時(shí)為true），如下所示：

11g中可以走HASHJOIN RIGHT ANTI NA，其中NA就是NULLAWARE的意思，邏輯讀從原先的23w降低到6105，效率提升明顯。如果在11g之前，針對(duì)這種SQL的優(yōu)化方式有：

1. 子查詢選擇條件的列增加NOT NULL約束。如上SQL需要對(duì)anti_test1和anti_test2的object_id列增加NOT NULL約束。

   
   

2. 改寫(xiě)SQL：對(duì)子查詢選擇條件的列增加IS NOT NULL條件，如下所示：

3. 改寫(xiě)SQL：將NOT IN改為JOIN形式。如下所示：

NOT IN子查詢改為JOIN的等價(jià)形式必須是外連接+子查詢表對(duì)應(yīng)的選擇條件ISNULL。

4. 改寫(xiě)SQL：將NOT IN子查詢改為NOT EXISTS子查詢。如下所示：

以上四種方式的執(zhí)行計(jì)劃都是可以走HASHJOIN RIGHT ANTI的正確計(jì)劃：

當(dāng)然,如果NOTIN子查詢的確存在NULL，可能不返回結(jié)果，這種情況下是不可以用以上方式進(jìn)行等價(jià)改寫(xiě)的，只有在NOTIN子查詢肯定會(huì)返回結(jié)果，而且執(zhí)行計(jì)劃出現(xiàn)FILTER的時(shí)候才考慮以上方式進(jìn)行優(yōu)化。

OR子查詢中的FILTER

再來(lái)看下常見(jiàn)的OR與子查詢連用情況，在實(shí)際優(yōu)化過(guò)程中，遇到OR與子查詢連用，一般都不能unnestsubquery了，這樣執(zhí)行計(jì)劃出現(xiàn)FILTER，可能會(huì)導(dǎo)致嚴(yán)重性能問(wèn)題，OR與子查詢連用有兩種可能：

1. condition or subquery

2. subquery內(nèi)部包含or,如in (select … from tab where condition1 or condition 2)

如下例所示：

上面SQL的子查詢關(guān)聯(lián)條件包含OR，執(zhí)行計(jì)劃如下：

可以看到執(zhí)行計(jì)劃走FILTER，子查詢表DBA_OBJECTS_B被全表驅(qū)動(dòng)9999次，邏輯讀10M，耗時(shí)35s,性能低下。其根本原因就是因?yàn)镃BO對(duì)包含OR的子查詢此處沒(méi)有進(jìn)行unnest，導(dǎo)致走了FILTER。當(dāng)然，在不考慮改寫(xiě)的情況下，可以對(duì)DBA_OBJECTS_B的OBJECT_ID和OBJECT_NAME分別建立索引，從而避免對(duì)DBA_OBJECTS_B進(jìn)行上萬(wàn)次的全表掃描來(lái)提高效率。如下：

現(xiàn)在邏輯讀從10M變?yōu)?341，執(zhí)行時(shí)間從從35s變?yōu)?.05s，這里建立索引還是沒(méi)有消除FILTER，索引被執(zhí)行9999次，很顯然，如果ID=3的結(jié)果行數(shù)增大，索引的掃描次數(shù)就會(huì)增多，這顯然是治標(biāo)不治本的方式。

針對(duì)OR子查詢無(wú)法unnest導(dǎo)致走FILTER的問(wèn)題，一般需要通過(guò)改寫(xiě)，改寫(xiě)思路如下：

1. 將OR條件改為UNION或UNION ALL。

2. 根據(jù)語(yǔ)義改寫(xiě)，徹底消除OR條件。

根據(jù)以上優(yōu)化指導(dǎo)思想，這條語(yǔ)句可改寫(xiě)為UNION形式，如下：

將OR條件改寫(xiě)為兩條語(yǔ)句，使用UNION合并，最終查詢COUNT(*)，執(zhí)行計(jì)劃如下：

現(xiàn)在的執(zhí)行計(jì)劃兩個(gè)子查詢語(yǔ)句都可以進(jìn)行UNNEST，走HASHJOINSEMI，避免了FILTER操作，最終執(zhí)行時(shí)間從原來(lái)的35s變?yōu)?.04s，邏輯讀從10M減少為2550，子查詢都是執(zhí)行一次，也避免了建立索引，子查詢效率依賴于驅(qū)動(dòng)表結(jié)果行數(shù)的目的。

下面再看一個(gè)徹底消除OR條件的改寫(xiě)案例：

這里是NOTEXISTS子查詢帶OR條件，執(zhí)行計(jì)劃如下：

同樣，這里的執(zhí)行計(jì)劃走FILTER，耗時(shí)21s,邏輯讀468w，效率低下。如何徹底改寫(xiě)消除OR條件呢？可以使用集合運(yùn)算的思路，集合運(yùn)算中NOT (A OR B) 等價(jià)于NOT A AND NOT B。則可以將OR條件改寫(xiě)為AND條件：

執(zhí)行計(jì)劃如下：

將子查詢OR改寫(xiě)為AND后，子查詢可以UNNEST，走HASHJOIN RIGHT ANTI，最終執(zhí)行時(shí)間從21s到0.03s,邏輯讀從468w到1450，效率提升明顯。

直方圖與綁定變量問(wèn)題是困擾SQL性能優(yōu)化的一個(gè)典型問(wèn)題：一方面綁定變量是為了讓執(zhí)行計(jì)劃共享，從而減少或避免解析，但是如果一個(gè)列分布不均，傳入不同的值最佳執(zhí)行計(jì)劃應(yīng)該不一樣，比如當(dāng)status=’INVALID’的時(shí)候最佳執(zhí)行計(jì)劃是走索引，當(dāng)status=’VALID’時(shí)候最佳執(zhí)行計(jì)劃是要求全表掃描，遇到這種情況，必須要再次窺視傳入的綁定變量值，才能走正確執(zhí)行計(jì)劃，因此，11G引入了AdaptiveCursorSharing(ACS)來(lái)解決這個(gè)問(wèn)題，但是因?yàn)锽UG多，一般情況下生產(chǎn)庫(kù)是建議關(guān)閉的。那么還能不能解決這個(gè)問(wèn)題呢？在11.2及之后答案是肯定的。在11.2的時(shí)候，我們使用SQLPATCH來(lái)解決，這個(gè)類似以前的SQLPROFILE。如下例所示：

對(duì)于表T的STATUS列分布如下：

STATUS          COUNT(*)

------------------------

VALID              72398

INVALID                1

其中STATUS列有索引，顯然，傳入INVALID時(shí)候應(yīng)該走索引，傳入VALID時(shí)候應(yīng)該走全表掃描。如果關(guān)閉了ACS，則誰(shuí)先執(zhí)行，后面的共享前面的執(zhí)行計(jì)劃。如下先執(zhí)行INVALID走索引：

但是再執(zhí)行VALID時(shí)候，還是一樣執(zhí)行計(jì)劃：

可以看出，現(xiàn)在返回行數(shù)從1行變成72396行，但是執(zhí)行計(jì)劃沒(méi)有變，根本原因是沒(méi)有再次對(duì)綁定變量進(jìn)行PEEKING，PEEKING的值還是原來(lái)的INVALID。知道這點(diǎn)就好辦了，其實(shí)ACS的本質(zhì)是使用了HINTS：BIND_AWARE，那么在11.2時(shí)候使用SQLPATCH就可以了，如下所示：

11G寫(xiě)法如下（dbms_sqldiag_internal這個(gè)是內(nèi)部存儲(chǔ)過(guò)程，一般不建議使用）：

declare

l_sql_text clob;

begin

SELECT sql_fulltextINTO l_sql_text FROM v$sql WHERE sql_id = bbj7tdztdu843 AND ROWNUM< 2;

sys.dbms_sqldiag_internal.i_create_patch(sql_text  => l_sql_text,

hint_text => BIND_AWARE,

name      => bind_aware_bbj7tdztdu843

,description => test_sql_patch);

end ;

/

12.2及之后可以使用官方的DBMS_SQLDIAG.create_sql_patch：

DECLARE

  l VARCHAR2(32767);

BEGIN

  l :=SYS.DBMS_SQLDIAG.create_sql_patch(

   sql_id    => bbj7tdztdu843,

   hint_text => q[BIND_AWARE],

   name      => bind_aware_bbj7tdztdu843);

END;

/

在19c里測(cè)試，使用SQLPROFILE也是有效的，11g里使用SQLPROFILE無(wú)效。

這樣，我們先執(zhí)行INVALID，還是走索引：

但是再執(zhí)行VALID，可以看到，綁定變量以及窺視了，變成VALID，走了全表掃描，通過(guò)NOTE也可以看到走了SQLPATCH。

可以看出，使用SQLPATCH特性，可以很好地解決直方圖與綁定變量的問(wèn)題。

總結(jié)：本文通過(guò)“分頁(yè)查詢優(yōu)化”、“FILTER性能殺手”、“直方圖與綁定變量”這三個(gè)常見(jiàn)問(wèn)題，探討CBO優(yōu)化器的特性以及通過(guò)編寫(xiě)高質(zhì)量SQL語(yǔ)句來(lái)達(dá)到提升性能的目的。SQL語(yǔ)句性能，涉及的因素很多，如統(tǒng)計(jì)信息、索引等，更為重要的是，熟知優(yōu)化器特性，從而能夠編寫(xiě)與CBO優(yōu)化器特性相匹配的SQL語(yǔ)句，這樣才能使用到CBO優(yōu)秀的特性，保證SQL語(yǔ)句的執(zhí)行性能。