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