深入了解SQL性能殺手FILTER操作
?????????????????點擊上方“IT那活兒”，關注后了解更多內容，不管IT什么活兒，干就完了！！！
FILTER操作是執行計劃中常見的操作，這種操作有兩種情況：1.1 只有一個子節點，那么就是簡單過濾操作。（不是本文重點）
1.2 有多個子節點，那么就是類似NESTED LOOPS操作，只不過與NESTED LOOPS差別在于，FILTER內部會構建HASH表（有緩存提高效率，緩存的bucket數目是1024個），對于重復匹配的，不會再次進行循環查找，而是利用已有結果，提高效率。但是一旦重復匹配的較少，循環次數多，也就是類似NESTED LOOPS，那么這種FILTER操作將是嚴重影響性能的操作，可能你的SQL幾天都執行不完了。（本文討論重點）
很顯然ID=1的FILTER操作只有一個子節點ID=2，這種情況下的FILTER操作也就是單純的過濾操作。
FILTER多子節點往往就是性能殺手，主要出現在子查詢無法UNNEST查詢轉換，經常遇到的情況就是NOT IN子查詢、子查詢和OR連用、復雜子查詢、CBO未做子查詢UNNEST等情況。
3.1 NOT IN子查詢中的FILTER
針對上面的NOT IN子查詢，如果子查詢object_id有NULL存在，則整個查詢都不會有結果，在11g之前，如果主表和子表的object_id未同時有NOT NULL約束，或都未加IS NOT NULL限制，則ORACLE會走FILTER。11g有新的ANTI NA（NULL AWARE）優化，可以對子查詢進行UNNEST，從而提高效率。對于未UNNEST的子查詢，走了FILTER，有至少2個子節點，執行計劃還有個特點就是Predicate謂詞部分有：B1這種類似綁定變量的東西，內部操作走類似NESTED LOOPS操作。11g有NULL AWARE專門針對NOT IN問題進行優化，如下所示：通過NULL AWARE操作，對無法UNNEST的NOT IN子查詢可以轉換成JOIN形式，這樣效率就大幅度提升了。如果在11g之前，遇到NOT IN無法UNNEST，那該怎么做呢？以上四種方式，大部分情況下均能達到讓優化器走JOIN的目的，如下所示：說白了，unnest subquery就是轉換成JOIN形式，如果能轉換成JOIN就可以利用高效JOIN特性來提高操作效率，不能轉換就走FILTER，可能影響效率，11g的NULL AWARE從執行計劃里可以看出，還是有點區別，沒有走INDEX FULL SCAN掃描，因為沒有條件讓ORACLE知道object_id可能存在NULL，所以也就走不了索引了。OK，現在來說一個數據庫升級過程中碰到的案例，背景是11.2.0.2升級到11.2.0.4后下面SQL出現性能問題： 這里的ID=4和ID=8兩個FILTER均有2個子節點，很顯然是NOT IN子查詢無法UNNEST導致的。上面說了在11g ORACLE CBO可以將NOT IN轉換成NULL AWARE ANTI JOIN，并且在11.2.0.2上是可以轉換的，到11.2.0.4上就不行了。兩個FILTER操作的危害到底有多大呢，可以通過查詢實際執行計劃來看： 使用ALTER SESSION SET STATISTICS_LEVEL=ALL;截取2分25s的記錄查看實際情況，ID=10步驟的CARD=141行就需要2分25s,主要是ID=11的索引較差，ID=11回表需要過濾大量數據。也就是這條SQL要運行10天以上了，簡直太恐怖了。那么此時，只能寄希望于第三種情況（分析3）：可能是BUG或者升級過程中修改了其它參數影響了無法走NULL AWARE ANTI JOIN。ORACLE BUG和參數那么多，那么我們怎么快速找到問題根源導致是哪個BUG或者參數導致的呢？這里給大家分享一個神器SQLT，全稱（SQLTXPLAIN），這是ORACLE內部性能部門開發的工具，可以在MOS上下載，功能非常強勁。此工具詳細用法不做贅述，針對此工具，Apress也出了一本書籍，感興趣的可以學習一下：回歸正題，現在要找出是不是新版本BUG或者修改了某個參數導致問題產生，那么就要用到SQLT的高級方法：XPLORE。XPLORE會針對ORACLE中的各種參數不停打開、關閉，來輸出執行計劃，最終我們可以通過生成的報告，找到匹配的執行計劃來判斷是BUG問題還是參數設置問題。使用很簡單，參考readme.txt將需要測試的SQL多帶帶編輯一個文件，一般，我們測試都使用XPLAIN方法，調用EXPLAIN PLAN FOR進行測試，這樣保證測試效率。最終通過SQLT XPLORE找出問題根源在于新版本關閉了_optimier_squ_bottomup參數（和子查詢相關）。從這點上也可以看出來，很多查詢轉換能夠成功，不光是一個參數起作用，可能多個參數共同作用。因此，關閉默認參數，除非有強大的理由，否則，不可輕易修改其默認值。至此，此問題在SQLT的幫助下，快速得以解決，如果不使用SQLT，那么解決問題的過程顯然更為曲折，一般情況下，估計是讓開發先修改SQL了。 很顯然，如果要進一步優化，要徹底對SQL進行重寫。通過觀察，2個子查詢部分有相同點，經過分析語義：查找表DT_MBY_TEST_LOG在指定INSERT_TIME范圍內的，按照每個TBILL_ID取最小的INSERT_TIME，并且ID不在子查詢中，然后結果按照INSERT_TIME排序，最后取TOP 199。原SQL使用自連接、兩個子查詢，冗余繁雜。自然想到用分析函數進行改寫，避免自連接，從而提高效率。至此，這條SQL從原來的走FILTER需要耗時10天，到找出問題根源可以走NULL AWARE ANTI JOIN需要耗時7秒多，最后通過徹底改寫耗時3.8s。3.2 OR子查詢中的FILTER
再來看下常見的OR與子查詢連用情況，在實際優化過程中，遇到OR與子查詢連用，一般都不能unnest subquery了，可能會導致嚴重性能問題，OR與子查詢連用有兩種可能：通過一個具體案例，分享下對于OR子查詢優化的處理方式，在某庫11g R2中碰到如下SQL，幾個小時都沒有執行完：
2）怎么通過看到這個執行計劃，一眼定位性能慢的原因呢？主要通過下列幾點來分析定位：- 執行計劃中的Rows，也就是每個步驟返回的cardinality很少，都是幾行，在分析表也不是太大，那么怎么可能導致運行幾個小時都執行不完呢？執行時間與執行計劃關鍵指標不匹配。很大原因可能就在于統計信息不準，導致CBO優化器估算錯誤，錯誤的統計信息導致錯誤的執行計劃，這是第一點。
- 看ID=15到18部分，它們是ID=1 FILTER操作的第二子節點，第一子節點是ID=2部分，很顯然，如果ID=2部分估算的cardinality錯誤，實際情況很大的話，那么對ID=15到18部分四個表全掃描次數將會巨大，那么也就導致災難產生。
- 很顯然，ID=2部分的一堆NESTED LOOPS也是很可疑的，找到ID=2操作的入口在ID=6部分，全表掃描DEALREC_ERR_201608，估算返回1行，很顯然，這是導致NESTED LOOPS操作的根源，因此，需要檢驗其準確性。
主表DEALREC_ERR_201608在ID=6查詢條件中經查要返回2000w行，計劃中估算只有1行，因此，會導致NESTED LOOPS次數實際執行千萬次，導致效率低下，應該走HASH JOIN，需要更新統計信息。另外ID=1是FILTER,它的子節點是ID=2和ID=15、16、17、18，同樣的ID 15-18也被驅動千萬次。找出問題根源后，逐步解決。首先要解決ID=6部分針對DEALREC_ERR_201608表按照查詢條件substr(other_class, 1, 3) NOT IN (‘147’,‘151’, …)獲得的cardinality的準確性，也就是要收集統計信息。 然而發現使用size auto,size repeat,對other_class收集直方圖均無效果，執行計劃中對other_class的查詢條件返回行估算還是1（實際2000w行）。DEALREC_ERR_201608與B_DEALING_DONE_TYPE原來走NL的現在正確走HASH JOIN。Build table是小結果集，probe table是ERR表大結果集，正確。
但是ID=2與ID=11到14，也就是與TMI_NO_INFOS的OR子查詢，還是FILTER,驅動數千萬次子節點查詢，下一步優化要解決的問題。
性能從12小時到2小時。
現在要解決的就是FILTER問題，對子查詢有OR條件的，簡單條件如果能夠查詢轉換，一般會轉為一個union all view后再進行semi join、anti join（轉換成union all view,如果謂詞類型不同，則SQL可能會報錯）。對于這種復雜的，優化器就無法查詢轉換了，因此，改寫是唯一可行的方法。分析SQL，原來查詢的是同一張表，而且條件類似，只是取的長度不同，那么就好辦了！4）如何讓帶OR的子查詢執行計劃從FILTER變成JOIN。兩種方法：上面含義是ERR表的TMISID截取前8,9,10,11位與TMI_NO_INFOS.BILLID_HEAD匹配，對應匹配BILLID_HEAD長度正好為8,9,10,11。ERR表與TMI_NO_INFOS表關聯，ERR.TMISID前8位與ITMI_NO_INFOS.BILLID_HEAD長度在8-11之間的前8位完全匹配，在此前提下，TMISID like BILLID_HEAD ||’%’。6）現在就動手徹底改變多個OR子查詢，讓SQL更加精簡，效率更高。改寫如下：現在的執行計劃終于變的更短，更易讀，通過邏輯改寫走了HASH JOIN，最終一條返回300多萬行數據的SQL原先需要12小時運行的SQL，現在3分鐘就執行完了。思考：結構良好，語義清晰的SQL編寫，有助于優化器選擇更合理的執行計劃，所以說，寫好SQL也是門技術活。 通過上述這個案例，希望能給大家一些啟發，寫SQL如何能夠自己充當查詢轉換器，編寫的SQL能夠減少表、索引、分區等的訪問，能夠讓ORACLE更易使用一些高效算法進行運算，從而提高SQL執行效率。 其實，OR子查詢也不一定就完全不能unnest，只是絕大多數情況下無法unnest而已，請看下例：這2條SQL的差別也就是將條件or id3=id2+1000轉換成or id3-1000=id2，前者不可以unnest,后者可以unnest，通過分析10053可以得知：SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest；
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing；
SU: Considering subquery unnest on query block SEL$1 (#1)；
SU: Checking validity of unnesting subquery SEL$2 (#2)；
SU: SU bypassed: Invalid correlated predicates；
SU: Validity checks failed。
最終CBO先查詢T3條件，做個UNION ALL視圖，之后與T2關聯。從這里來看，對于OR子查詢的unnest要求比較嚴格，從這條語句分析，ORACLE可進行unnest必須要求對主表列不要進行運算操作，優化器自身并未將+1000條件左移，正因為嚴格，所以大部分情況下，OR子查詢也就無法進行unnest了，從而導致各種性能問題。主要體現在UPDATE關聯更新和標量子查詢中，雖然此類SQL語句中并未顯式出現FILTER關鍵字，但是內部操作和FILTER操作如出一轍。ID=2部分是where exists選擇部分，先把需要更新的條件查詢出來，之后執行UPDATE關聯子查詢更新，可以看到ID=5部分出現綁定變量：B1，顯然UPDATE操作就類似于原來的FILTER，對于選出的每行與子查詢表NEW_TAB關聯查詢，如果ID列重復值較少，那么子查詢執行的次數就會很多，從而影響效率，也就是ID=5的操作要執行很多次。當然，這里字段ID唯一性很強，可以建立UNIQUE INDEX，普通INDEX燈，這樣第5步就可以走索引了。這里為了舉例這種UPDATE的優化方式，不建索引，也可以搞定這樣的UPDATE：MERGR和UPDATE INLINE VIEW方式。MERGE中直接利用HASH JOIN，避免多次訪問操作，從而效率大增，再來看看UPDATE LINE VIEW寫法：UPDATE
(SELECT a.status astatus,
b.status bstatus
FROM old_tab a,
new_tab b
WHERE a.id=b.id
AND a.id >9000000
)
SET astatus=bstatus;
要求b.id是preserved key (唯一索引、唯一約束、主鍵），11g bypass_ujvc會報錯，類似MERGE操作。2）再來看看標量子查詢，標量子查詢往往也是引發嚴重性能問題的殺手。標量子查詢的計劃和普通計劃的執行順序不同，標量子查詢雖然在上面，但是它由下面的CUSTOMERS表結果驅動（上面的在后面執行，這個與普通執行計劃順序不同），每行驅動查詢一次標量子查詢（有CACHE例外），同樣類似FILTER操作。如果對標量子查詢進行優化，一般就是改寫SQL，將標量子查詢改為外連接形式（在約束和業務滿足的情況下也可改寫為普通JOIN）：通過改寫之后效率大增，并且使用HASH JOIN算法。3）下面看一下標量子查詢中的CACHE（FILTER和UPDATE關聯更新類似），如果關聯的列重復值特別多，那么子查詢執行次數就會很少，這時候效率會比較好。標量子查詢和FILTER一樣，有CACHE，如上面的emp_a有108K的行，但是重復的department_id只有11,這樣只查詢只掃描11次，掃描子查詢表的次數少了，效率會提升。針對FILTER性能殺手問題，主要分享這3點（3.1,3.2,3.3），當然，還有很多其它值得注意的地方，這需要我們日常多留心和積累，從而熟悉優化器一些問題的處理方法。
數據物理分布對FILTER節點執行次數的影響。
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LEVEL ID FROM dual CONNECT BY LEVEL<=1024;
INSERT INTO t2 SELECT*FROM t2;
COMMIT;
對應的SQL如下：
SELECT COUNT(*)
FROM t2
WHERE ID NOT IN
(SELECT ID
FROM(SELECT ID
FROM t2
ORDER BY ID DESC)
WHERE ROWNUM<=100);
以上SQL的含義很簡單，也就是對T2表中的ID降序排列，查詢不在前100的數據量，然而執行卻比正常的慢很多（這里作為演示，只是構造少量數據，實際數據量很多，SQL執行非常慢）。 通過前面有關FILTER內容可以知道，表T2的ID只有1024個不同值，由于FILTER有緩存，那么這個子節點正常的執行次數應該是1024次，但是上述執行計劃的執行次數卻是12432次，子節點的執行次數增加N倍是導致SQL變慢的主要原因。 解決這個問題，主要要搞清楚為什么執行次數不是1024次，在實際的應用中，表是按天分表的，每天的數據累計到前一天，執行計劃未變，但是突然某一天變慢，通過分析，出現這種情況的原因，大概率是數據的分布不同。由于這里是按照ID構造HASH表，可以測試下按照ID順序重新組織是什么情況。DROP TABLE t3;
CREATE TABLE t3 AS SELECT*FROM t2 ORDER BY ID;--按照關聯列ID重新組織順序
SELECT COUNT(*)
FROM t3
WHERE ID NOT IN
(SELECT ID
FROM(SELECT ID
FROM t3
ORDER BY ID DESC)WHERE ROWNUM<=100);
現在執行計劃中子節點執行次數是1024次了，的確是數據的物理分布原因，看來FITER算法還不夠完善，這點值得注意。 如果要進一步優化上述語句，可以通過改寫成NOT EXISTS避免FILTER來提高效率，改寫如下：SELECT COUNT(*)
FROM t3 a
WHERE not exists
(SELECT 1 from
(SELECT ID
FROM(SELECT ID
FROM t3
ORDER BY ID DESC)WHERE ROWNUM<=100) b
where a.ID = b.ID
);
通過執行計劃可以看出，改寫為NOT EXISTS后走HASH JOIN效率得到極大提升。 通過本文可以了解到，FILTER往往是導致SQL執行性能緩慢的元兇，主要是由于子查詢未做UNNEST SUBQUERY查詢轉換，未UNNEST的原因有很多，比如統計信息不準，如果發現統計信息準確了還是無法UNNEST，那么要考慮SQL寫法是否遇到優化器的限制或BUG，比如OR子查詢。當然，FILTER由于內部構建HASH表，有緩存和HASH算法，對特定的查詢效率可能不錯，很顯然是要求關聯條件的重復值較少，這樣子節點執行次數少，從而提高效率，所以在實際應用和優化中，還需要具體問題具體分析。
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129606.html
