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

来源：这里教程网时间：2026-03-03 12:46:53 作者：

[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 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------- | 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 ); --//执行计划如下: ------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------- | 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 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 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.

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推