[20181220]使用提示OR_EXPAND優化.txt

--//連結http://www.itpub.net/thread-2107240-2-1.html,http://www.itpub.net/thread-2107231-2-1.html的討論.

--//ZALBB建議在18c下嘗試看看,我們這裡僅僅1臺18c,而且還是生產系統,正好前幾天在辦公機器重新安裝12c,在12c測試看看.

--//主要問題感覺oracle對於這樣的sql有點奇怪....

1.環境:

SCOTT@test01p> @ ver1

PORT_STRING VERSION BANNER CON_ID

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

IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

create table t1 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;

create table t2 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;

create index i_t1_id1 on t1(id1);

create index i_t1_id2 on t1(id2);

create index i_t2_id1 on t2(id1);

--//分析略.

2.測試:

SCOTT@test01p> alter session set statistics_level = all;

Session altered.

SCOTT@test01p> select * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

ID1 ID2 NAME

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

10 10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

11 11 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCOTT@test01p> @ dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID gz5pqkg6svm7k, child number 0

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

select * from t1 where t1.id1 in (select t2.id1 from t2 where

t2.id1=11 ) or (t1.id2=10 )

Plan hash value: 1962644737

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

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

| 0 | SELECT STATEMENT | | 1 | | | 30 (100)| | 2 |00:00:00.01 | 115 |

|* 1 | FILTER | | 1 | | | | | 2 |00:00:00.01 | 115 |

| 2 | TABLE ACCESS FULL| T1 | 1 | 6000 | 638K| 30 (0)| 00:00:01 | 6000 |00:00:00.01 | 113 |

|* 3 | FILTER | | 5999 | | | | | 1 |00:00:00.01 | 2 |

|* 4 | INDEX RANGE SCAN| I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / T1@SEL$1

3 - SEL$2

4 - SEL$2 / T2@SEL$2

Predicate Information (identified by operation id):

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

1 - filter(("T1"."ID2"=10 OR IS NOT NULL))

3 - filter(11=:B1)

4 - access("T2"."ID1"=:B1)

32 rows selected.

--//執行計劃存在1個全表掃描.裡面的索引選擇性很好,oracle並沒有選擇合理的執行計劃.

--//而且有1個小小的細節,id=4的starts=1,而前面的id=3的starts=5999.你可以看出這裡oracle顯示執行計劃有1個小小的bug.

--//id=4的starts應該是5999.這樣看到的邏輯讀不應該是後面的2而是2*5999 = 11998.

--//而且你可以看出oracle忽略的id=4多次INDEX RANGE SCAN的成本.

--//連結http://www.itpub.net/thread-2107240-2-1.html裡面的顯示倒是正確的.它的版本是11.2.0.4.180717.

3.是否通過提示優化sql語句:

--//首先想到的是USE_CONCAT.

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in (select /*+unnest */ t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

--//執行計劃如下:

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

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

| 0 | SELECT STATEMENT | | 1 | | | 33 (100)| | 2 |00:00:00.01 | 118 |

| 1 | CONCATENATION | | 1 | | | | | 2 |00:00:00.01 | 118 |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 3 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

|* 4 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 114 |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 5999 | 638K| 30 (0)| 00:00:01 | 5999 |00:00:00.01 | 112 |

|* 6 | FILTER | | 5999 | | | | | 1 |00:00:00.01 | 2 |

|* 7 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1_1 / T1@SEL$1

3 - SEL$1_1 / T1@SEL$1

5 - SEL$1_2 / T1@SEL$1_2

6 - SEL$2

7 - SEL$2 / T2@SEL$2

Predicate Information (identified by operation id):

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

3 - access("T1"."ID2"=10)

4 - filter( IS NOT NULL)

5 - filter(LNNVL("T1"."ID2"=10))

6 - filter(11=:B1)

7 - access("T2"."ID1"=:B1)

--//很奇怪id=4,依舊選擇過濾,unnest提示沒有用.實際上使用USE_CONCAT相當每個or分支加入LNNVL(條件)來排他符合條件的記錄.

--//也就是oracle依舊選擇的執行計劃不是很理想,甚至比前面還要差.

4.嘗試OR_EXPAND提示:

select /*+ OR_EXPAND */ * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

--//執行計劃如下:

Plan hash value: 1716482303

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

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

| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 2 |00:00:00.01 | 9 |

| 1 | VIEW | VW_ORE_BA8ECEFB | 1 | 2 | 156 | 5 (0)| 00:00:01 | 2 |00:00:00.01 | 9 |

| 2 | UNION-ALL | | 1 | | | | | 2 |00:00:00.01 | 9 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 4 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

| 5 | NESTED LOOPS SEMI | | 1 | 1 | 113 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |

|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

|* 7 | INDEX RANGE SCAN | I_T1_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

|* 8 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SET$9162BF3C / VW_ORE_BA8ECEFB@SEL$BA8ECEFB

2 - SET$9162BF3C

3 - SET$9162BF3C_1 / T1@SEL$1

4 - SET$9162BF3C_1 / T1@SEL$1

5 - SEL$C90BA1D5

6 - SEL$C90BA1D5 / T1@SEL$1

7 - SEL$C90BA1D5 / T1@SEL$1

8 - SEL$C90BA1D5 / T2@SEL$2

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('12.2.0.1')

DB_VERSION('12.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$C90BA1D5")

UNNEST(@"SEL$2")

OUTLINE_LEAF(@"SET$9162BF3C_1")

OUTLINE_LEAF(@"SET$9162BF3C")

OR_EXPAND(@"SEL$1" (1) (2))

OUTLINE_LEAF(@"SEL$BA8ECEFB")

OUTLINE(@"SET$9162BF3C_2")

OUTLINE(@"SEL$2")

OUTLINE(@"SET$9162BF3C")

OR_EXPAND(@"SEL$1" (1) (2))

OUTLINE(@"SEL$1")

NO_ACCESS(@"SEL$BA8ECEFB" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB")

INDEX_RS_ASC(@"SET$9162BF3C_1" "T1"@"SEL$1" ("T1"."ID2"))

BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "T1"@"SEL$1")

INDEX_RS_ASC(@"SEL$C90BA1D5" "T1"@"SEL$1" ("T1"."ID1"))

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$C90BA1D5" "T1"@"SEL$1")

INDEX(@"SEL$C90BA1D5" "T2"@"SEL$2" ("T2"."ID1"))

LEADING(@"SEL$C90BA1D5" "T1"@"SEL$1" "T2"@"SEL$2")

USE_NL(@"SEL$C90BA1D5" "T2"@"SEL$2")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

4 - access("T1"."ID2"=10)

6 - filter(LNNVL("T1"."ID2"=10))

7 - access("T1"."ID1"=11)

8 - access("T2"."ID1"=11)

filter("T1"."ID1"="T2"."ID1")

--//12c下oracle選擇正確的執行計劃.可以發現id=2使用UNION-ALL,也就是oracle做了查詢轉換成union all的形式.

--//另外我曾經嘗試將ounline date的提示資訊加入到11g環境,執行計劃依舊沒有選擇OR_EXPAND.

--//通過10053事件看看.

SCOTT@test01p> @ 10053x cg5kmfhgczjfd 0

PL/SQL procedure successfully completed.

ORE: after OR Expansion:******* UNPARSED QUERY IS *******

SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "ID1","VW_ORE_BA8ECEFB"."ITEM_2" "ID2","VW_ORE_BA8ECEFB"."ITEM_3" "NAME" FROM ( (SELECT "T1"."ID1" "ITEM_1","T1"."ID2" "ITEM_2","T1"."NAME" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE "T1"."ID2"=10) UNION ALL (SELECT "T1"."ID1" "ITEM_1","T1"."ID2" "ITEM_2","T1"."NAME" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE "T1"."ID1"=ANY (SELECT /*+ UNNEST */ "T2"."ID1" "ID1" FROM "SCOTT"."T2" "T2" WHERE "T2"."ID1"=11) AND LNNVL("T1"."ID2"=10))) "VW_ORE_BA8ECEFB"

--//格式化顯示如下:

SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "ID1"

,"VW_ORE_BA8ECEFB"."ITEM_2" "ID2"

,"VW_ORE_BA8ECEFB"."ITEM_3" "NAME"

FROM ( (SELECT "T1"."ID1" "ITEM_1"

,"T1"."ID2" "ITEM_2"

,"T1"."NAME" "ITEM_3"

FROM "SCOTT"."T1" "T1"

WHERE "T1"."ID2" = 10)

UNION ALL

(SELECT "T1"."ID1" "ITEM_1"

,"T1"."ID2" "ITEM_2"

,"T1"."NAME" "ITEM_3"

FROM "SCOTT"."T1" "T1"

WHERE "T1"."ID1" = ANY (SELECT /*+ UNNEST */

"T2"."ID1" "ID1"

FROM "SCOTT"."T2" "T2"

WHERE "T2"."ID1" = 11)

AND LNNVL ("T1"."ID2" = 10))) "VW_ORE_BA8ECEFB";

--//也就是oracle查詢轉換為 UNION ALL的形式.

--//你可以看到第2個條件人為的加入LNNVL ("T1"."ID2" = 10).

--// OR_EXPAND 提示與 USE_CONCAT 提示到底有什麼不同?

5.補充使用USE_CONCAT看到的情況:

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in (select /*+unnest */ t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

SCOTT@test01p> @ 10053x 18h6hkqcqq3w2 0

PL/SQL procedure successfully completed.

--//看這些太煩,不過可以發現如下:

LORE: Or-Expansion validity checks failed on query block SEL$2 (#2) because Cost based OR expansion enabled

SYS@test01p> @ hide or_exp

old 10: and lower(a.ksppinm) like lower('%&1%')

new 10: and lower(a.ksppinm) like lower('%or_exp%')

NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

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

_no_or_expansion OR expansion during optimization disabled TRUE FALSE FALSE

_optimizer_cbqt_or_expansion enables cost based OR expansion TRUE ON ON

_optimizer_interleave_or_expansion interleave OR Expansion during CBQT TRUE TRUE TRUE

_optimizer_or_expansion control or expansion approach used TRUE DEPTH DEPTH

_optimizer_or_expansion_subheap Use subheap for optimizer or-expansion TRUE TRUE TRUE

_or_expand_nvl_predicate enable OR expanded plan for NVL/DECODE predicate TRUE TRUE TRUE

6 rows selected.

--//也就是12c預設開啟因為以上原因.不過我嘗試"_optimizer_cbqt_or_expansion"=off也無效.放棄!!

--//我也嘗試提高全表掃描的成本看看是否執行計劃會發生改變,不過依舊沒用.

SCOTT@test01p> exec dbms_stats.SET_TABLE_STATS(user,'T1',NUMBLKS=>800000000000);

PL/SQL procedure successfully completed.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2285907/，如需轉載，請註明出處，否則將追究法律責任。

[20181220]使用提示OR_EXPAND優化.txt

相關文章