第一章 適用范圍
本案例中的問題現象發生于當前主流的ORACLE 11G環境。不排除隨著后續版本的升級可能會有一定細微的差異表現。但問題的原因及解決方案,在所有版本中都是通用的。
數據庫版本:ORACLE 11G
第二章 問題概述
問題SQL是至少包含對三個表的查詢,且表1、表2是通過內連接做的等值關聯。表3同時對表1、表2的等值連接列做關聯。
造成的現象是預估行數遠遠低于實際行數。分析此類問題原因,優化器計算表3返回行數時,通過單個條件計算預估行數,在施加了第二個連接條件時,還會在第一個連接列的基礎上再次根據連接列計算返回行數。兩次的選擇性計算導致預估行數不準的問題。
這類問題與兩個連接條件存在某些關系的查詢場景比較類似,一般是通過建立組合索引、擴展統計信息、動態采樣等方案來解決。而本案例中前述表1、表2是兩個表。上述方案均不可行。故此對此類問題做了深入的研究測試:
下面通過SQL案例進行說明:
select sum(a)
from (select 1 as a
from (select T3.policy_id,
T2.type_num,
T1.org_id,
T4.real_name,
T4.real_code,
T5.account_code,
T5.id1 ,
T6.id_name
from
T_TEST3 T3,
T_TEST1 T1,
T_TEST2 T2,
T_TEST4 T4,
T_TEST5 T5,
T_TEST6 T6
where 1 = 1
and T3.policy_id = T1.policy_id
and T3.policy_id = T2.policy_id
and T1.policy_id = T5.policy_id
and T1.item_id = T5.item_id
and T1.policy_id = T2.policy_id
and T1.org_id = T2.org_id
and T1.org_id = T4.customer_id
and T5.id1 = T6.id1
and T5.type <> 16
and T1.product_id in (1001,1002,1003,1004,1005,1006,1007,1008))
group by policy_id,
item_id,
type_num,
org_id,
real_name,
real_code,
id1,
id_name);
上述關鍵部分做了標粗處理。T_TEST3同時與T_TEST1、T_TEST2做關聯。且T_TEST1、T_TEST2已通過相同列做了關聯。
查看執行計劃如下:

關聯了T3(后續以表別名簡稱)后,返回結果只有1行。分析執行計劃的耗時步驟:

主要來自于第14步驟的多次索引訪問。

而之所以出現上述訪問步驟,主要原因來自于T3表的預估行數為1。這里分析預估為1的原因很可能由于T3同時與T1、T2表做了關聯。
第三章 問題分析優化
讓我們分析查詢邏輯:T1與T2的關聯條件(T1.policy_id = T2.policy_id)已經限定了返回結果集是包含policy_id相等的記錄。T3在與上面的結果集關聯時,與任何一個表的policy_id關聯后,得到的結果集必然也意味著能滿足與另一個表的關聯。因此我們分析:
SQL中T3的兩個關聯條件:
and T3.policy_id = T1.policy_id
and T3.policy_id = T2.policy_id
是完全可以省去任意一個的。
調整后測試執行效率表現:

調整后,數據庫不會受到T3的兩個關聯條件的影響,最后的返回行數也是接近于T1、T2關聯后的初始行數。也正是對上述T3表的預估行數的改變,導致數據庫將最耗時的步驟(T5表的多次循環訪問)提前。并改變連接方式通過HASH關聯。只訪問一次T5表,其執行效率也是能得到明顯提升的。
為了驗證上述設想方案是否符合預期,設計了如下更為簡便、易讀的查詢腳本:
CREATE TABLE TEST_t1 as select * from dba_objects;
CREATE TABLE TEST_t2 as select * from dba_objects;
CREATE TABLE TEST_t3 as select * from dba_objects;
CREATE INDEX IDX_t3_id on TEST_t3(object_id);
exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'TEST_t1');
exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'TEST_t2');
exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'TEST_t3');
查詢語句如下:
select count(*)
from TEST_t1, TEST_t2, TEST_t3
where TEST_t1.object_id = TEST_t2.object_id
and TEST_t1.object_type = 'SEQUENCE'
and TEST_t3.object_id = TEST_t1.object_id
and TEST_t3.object_id = TEST_t2.object_id;
查詢真實的執行計劃如下:

T3關聯后,直接將T1、T2關聯后的預估行數(1970)降低為1。而實際上基于前述的分析,T3是對T1、T2的等值連接列做關聯。T1、T2又可以看做是一個完整的集合。對集合的同一個列關聯兩次,理論上只應該計算一次選擇率。而本例中優化器是將T1、T2作為兩個表,通過access+filter共同作用,懷疑是這里造成了預估行數為1的問題。
進一步查看10053的優化成本信息:

正是由于與兩個表同時過濾,優化器在計算選擇率時,使用了帶過濾的選擇率計算公式ix_sel_with_filters。也就是ix_sel* ix_sel。最后選擇率接近于0。循環嵌套時,又使用接近于0的選擇率ix_sel_with_filters。最后預估返回行數僅為1。
而根據執行計劃及SQL語義理解,在關聯T3表時,T1、T2表已經滿足了連接條件相等。且T3又是與上述滿足相等條件的連接集合做關聯。因此僅與一個表關聯即可得到準確的選擇率。而優化器卻沒有這么智能,重復的以兩個條件做關聯最后計算選擇率,得到了不準確的預估返回結果。
基于此分析,進一步確定了我們的設想,可以去掉一個T3的關聯條件:
select count(*)
from TEST_t1, TEST_t2, TEST_t3
where TEST_t1.object_id = TEST_t2.object_id
and TEST_t1.object_type = 'SEQUENCE'
and TEST_t3.object_id = TEST_t1.object_id;
調整后的執行計劃測試:

調整后,省去了一步filter過濾的步驟,預估行數與實際更為接近。

進一步對比10053信息,可知優化器采用了ix_sel的索引選擇率。也能真實的評估返回行數。
第四章 解決方案
經過上述章節的分析,對具有等值關聯條件的多個連接表,再與其他表做相同條件的關聯時,可以只寫其中一個表即可。不必將所有的關聯條件寫全(這會導致計算選擇率不準確)
即使字段名不同,只要滿足對相等關聯條件做其他表的等值關聯,也是可以省去不必要的連接條件的。
如下舉例說明:
select count(*)
from TEST_t1, TEST_t2, TEST_t3
where TEST_t1.object_id = TEST_t2.data_object_id
and TEST_t1.object_type = 'SEQUENCE'
and TEST_t3.OBJECT_ID2 = TEST_t1.object_id
and TEST_t3.OBJECT_ID2 = TEST_t2.data_object_id;
執行計劃如下:

只要滿足T1、T2表的內連接,且包含等值關聯條件。
在與T3關聯時,就可以省去一部分的T3關聯條件:
select count(*)
from TEST_t1, TEST_t2, TEST_t3
where TEST_t1.object_id = TEST_t2.data_object_id
and TEST_t1.object_type = 'SEQUENCE'
and TEST_t3.OBJECT_ID2 = TEST_t1.object_id;
執行計劃如下:

第五章 問題總結
本案例是通過實驗模擬了內連接時相等連接條件,再與其他表關聯過程中如果寫了額外連接條件所導致的后果。優化器并沒有做足夠多的判斷,T3表所關聯的列是否已經滿足了相等條件。造成了錯誤的預估。因此這類問題我們在明確問題原理后,需要在實際的代碼編寫過程中進行預防,避免預估行數不準造成的SQL性能問題。




