[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.