[20181220]使用提示OR_EXPAND优化.txt
[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.