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

但是，由于其自身非常復雜，CBO的限制以及存在的BUG非常多，這時，作為SQL開發和優化人員，應該根據CBO特性，編寫高效語句，以避免踩坑CBO優化器。

本文以三類常見的SQL優化問題來探討基于ORACLE的高效SQL編寫和優化。

ORACLE中主要使用ROWNUM來實現TOP-N分頁查詢，分頁SQL編寫有如下規則：

1. 分頁查詢一般需要排序，內層查詢需要先ORDER BY。

   
   

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

   
   

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

分頁查詢優化要點

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

1. 避免排序：通過創建索引

2. 執行計劃使用COUNT STOPKEY算法，進行分頁裁剪。

   
   

錯誤的分頁SQL寫法

分頁SQL編寫必須遵守查詢前面說的3個規則，如下例是錯誤的分頁語句寫法：

這條語句查詢前20行，應該使用兩層嵌套規則：最內層排序，外層查詢rownum，并且在同一層用whererownum<或<=進行過濾。仔細分析這條語句，發現是兩層嵌套，但是不符合“并且在同一層用whererownum<或<=進行過濾”這個條件，此語句查詢rownum之后取了別名rn,在最外層進行rn過濾，可以從執行計劃看到，走了全表掃描：

這里的表TM_TESTX_TEMP的列DONE_DATE有索引，但是因為使用了錯誤的分頁SQL寫法，導致執行計劃無法使用COUNTSTOPKEY進行裁剪（執行計劃中未出現COUNTSTOPKEY），這樣ORACLE需要按照條件查詢所有的結果集，從而走索引COST更大，最終走了全表掃描。

正確的分頁SQL寫法

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

語句改寫為外層取rownum的同時按照WHEREROWNUM <= 20進行過濾，而不是原來的在最外層進行過濾，符合分頁SQL編寫規則，執行計劃變為：

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

表關聯SQL分頁優化

以上只是單表分頁查詢的高效SQL編寫和優化思路，如果是多表關聯SQL分頁，也需要遵循分頁SQL編寫規則，優化方式同樣是利用索引消除排序，并且能夠使用COUNTSTOPKEY算法，很顯然，要做到這些，必須以ORDERBY列所在表為驅動表，JOIN方式為NESTEDLOOPS，這樣可全部走索引并且可使用STOPKEY算法進行結果集裁剪，提高效率。如下例：

這條語句是test1和test2進行半連接，并按照test1的object_id列進行降序排列，最終返回test1的前10行數據。從子查詢關聯條件上看，按照object_id和object_name列關聯，沒有額外的過濾條件，從語句結構上看執行計劃應該中表test1應該是全表掃描，如下所示：

很顯然，這不是最佳執行計劃：沒有消除排序，兩表都是全表掃描，所有結果集返回后，才進行STOPKEY（SORTORDER BY STOPKEY）。前面已經說過，對于表關聯的分頁查詢，應該用排序鍵所在的表為驅動表，JOIN方式為NESTEDLOOPS，并且消除排序，根據這個思想，應該在兩表的object_id列分別建立索引即可：

create indexidx_test1 on test1(object_id);

create indexidx_test2 on test2(object_id);

索引創建完畢后的執行計劃如下：

現在的執行計劃完全符合多表關聯分頁查詢優化思路，以test1表為驅動表，消除排序，test1和test2之間走NESTEDLOOPS，可以從執行計劃上看出，ID=4的步驟雖然E-ROWS估算為69444行，但是實際只找到10行，也就結束了，最終邏輯讀從原先的2184降低到15，大幅度提升了效率。

因為分頁查詢要利用到STOPKEY算法，就算除關聯條件外沒有額外的過濾條件，也可以通過索引來提升效率。

FILTER操作是執行計劃中常見的操作，這種操作有兩種情況：

1. 只有一個子節點，那么就是簡單過濾操作。

2. 有多個子節點，那么就是類似NESTED LOOPS操作，只不過與NESTED LOOPS差別在于，FILTER內部會構建HASH表，對于重復匹配的，不會再次進行循環查找，而是利用已有結果，提高效率。但是一旦重復匹配的較少，循環次數多，那么，FILTER操作將是嚴重影響性能的操作，這是經常導致性能問題的原因。

NOT IN子查詢中的FILTER

對于NOTIN子查詢，在11g之前經常會出現性能問題，如下例SQL所示：

針對上面的NOTIN子查詢，如果子查詢object_id有NULL存在，則整個查詢都不會有結果，在11g之前，如果主表和子表的object_id未同時有NOTNULL約束，或都未加ISNOT NULL限制，則ORACLE會走FILTER。11g有新的ANTINA（NULLAWARE）優化，可以對子查詢進行UNNEST查詢轉換，從而提高效率。

對于未UNNEST的子查詢，走了FILTER，有至少2個子節點，執行計劃還有個特點就是Predicate謂詞部分有:B1這種類似綁定變量的東西，內部操作走類似NESTEDLOOPS操作,執行計劃如下：

可以從執行計劃上看到，FILTER有子節點ID=2和ID=3，并且ID=3部分出現綁定變量:B1，這是典型的NOTIN子查詢未UNNEST的執行計劃，性能很差。

11g有NULLAWARE專門針對NOTIN問題進行優化（要求參數：_optimizer_squ_bottomup、_optimizer_null_aware_antijoin同時為true），如下所示：

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

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

   
   

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

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

NOT IN子查詢改為JOIN的等價形式必須是外連接+子查詢表對應的選擇條件ISNULL。

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

以上四種方式的執行計劃都是可以走HASHJOIN RIGHT ANTI的正確計劃：

當然,如果NOTIN子查詢的確存在NULL，可能不返回結果，這種情況下是不可以用以上方式進行等價改寫的，只有在NOTIN子查詢肯定會返回結果，而且執行計劃出現FILTER的時候才考慮以上方式進行優化。

OR子查詢中的FILTER

再來看下常見的OR與子查詢連用情況，在實際優化過程中，遇到OR與子查詢連用，一般都不能unnestsubquery了，這樣執行計劃出現FILTER，可能會導致嚴重性能問題，OR與子查詢連用有兩種可能：

1. condition or subquery

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

如下例所示：

上面SQL的子查詢關聯條件包含OR，執行計劃如下：

可以看到執行計劃走FILTER，子查詢表DBA_OBJECTS_B被全表驅動9999次，邏輯讀10M，耗時35s,性能低下。其根本原因就是因為CBO對包含OR的子查詢此處沒有進行unnest，導致走了FILTER。當然，在不考慮改寫的情況下，可以對DBA_OBJECTS_B的OBJECT_ID和OBJECT_NAME分別建立索引，從而避免對DBA_OBJECTS_B進行上萬次的全表掃描來提高效率。如下：

現在邏輯讀從10M變為6341，執行時間從從35s變為0.05s，這里建立索引還是沒有消除FILTER，索引被執行9999次，很顯然，如果ID=3的結果行數增大，索引的掃描次數就會增多，這顯然是治標不治本的方式。

針對OR子查詢無法unnest導致走FILTER的問題，一般需要通過改寫，改寫思路如下：

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

2. 根據語義改寫，徹底消除OR條件。

根據以上優化指導思想，這條語句可改寫為UNION形式，如下：

將OR條件改寫為兩條語句，使用UNION合并，最終查詢COUNT(*)，執行計劃如下：

現在的執行計劃兩個子查詢語句都可以進行UNNEST，走HASHJOINSEMI，避免了FILTER操作，最終執行時間從原來的35s變為0.04s，邏輯讀從10M減少為2550，子查詢都是執行一次，也避免了建立索引，子查詢效率依賴于驅動表結果行數的目的。

下面再看一個徹底消除OR條件的改寫案例：

這里是NOTEXISTS子查詢帶OR條件，執行計劃如下：

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

執行計劃如下：

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

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

對于表T的STATUS列分布如下：

STATUS          COUNT(*)

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

VALID              72398

INVALID                1

其中STATUS列有索引，顯然，傳入INVALID時候應該走索引，傳入VALID時候應該走全表掃描。如果關閉了ACS，則誰先執行，后面的共享前面的執行計劃。如下先執行INVALID走索引：

但是再執行VALID時候，還是一樣執行計劃：

可以看出，現在返回行數從1行變成72396行，但是執行計劃沒有變，根本原因是沒有再次對綁定變量進行PEEKING，PEEKING的值還是原來的INVALID。知道這點就好辦了，其實ACS的本質是使用了HINTS：BIND_AWARE，那么在11.2時候使用SQLPATCH就可以了，如下所示：

11G寫法如下（dbms_sqldiag_internal這個是內部存儲過程，一般不建議使用）：

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里測試，使用SQLPROFILE也是有效的，11g里使用SQLPROFILE無效。

這樣，我們先執行INVALID，還是走索引：

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

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

總結：本文通過“分頁查詢優化”、“FILTER性能殺手”、“直方圖與綁定變量”這三個常見問題，探討CBO優化器的特性以及通過編寫高質量SQL語句來達到提升性能的目的。SQL語句性能，涉及的因素很多，如統計信息、索引等，更為重要的是，熟知優化器特性，從而能夠編寫與CBO優化器特性相匹配的SQL語句，這樣才能使用到CBO優秀的特性，保證SQL語句的執行性能。