[20190524]使用use_concat or_expand提示优化.txt
–//上午看了链接https://connor-mcdonald.com/2019/05/22/being-generous-to-the-optimizer，突然想起我们生产系统类似语句。
–//现在想想觉得开发的想象力太丰富，写这些语句是否考虑长期运行导致的结果。对方例子相对简单，我优化的例子简直就是变态。
–//链接:[20150814]使用use_concat提示.txt => http://blog.itpub.net/267265/viewspace-1771727/
–//实际上看了马上想到使用use_concat or_expand提示优化sql语句.同时看了链接
–//https://jonathanlewis.wordpress.com/2019/05/22/danger-hints/，一起测试看看。
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 address ( street int, suburb int, post_code int, data char(100));
insert into address select mod(rownum,1e4), mod(rownum,10), mod(rownum,1e2), rownum from dual connect by level <= 1e5;
commit;
exec dbms_stats.gather_table_stats(”,’address’)
create index i_address_stress on address ( street );
create index i_address_suburb on address ( suburb );
create index i_address_post_code on address ( post_code );
2.测试:
variable val number = 6
variable choice number = 1
alter session set statistics_level = all;
scott@test01p> select data from address where ( :choice = 1 and street = :val ) or ( :choice = 2 and suburb = :val );
data
—–
6
10006
20006
30006
40006
50006
60006
70006
80006
90006
10 rows selected.
plan hash value: 3645838471
———————————————————————————————————————–
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers |
———————————————————————————————————————–
| 0 | select statement | | 1 | | | 445 (100)| | 10 |00:00:00.02 | 1636 |
|* 1 | table access full| address | 1 | 100 | 10800 | 445 (1)| 00:00:01 | 10 |00:00:00.02 | 1636 |
———————————————————————————————————————–
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$1 / address@sel$1
peeked binds (identified by position):
————————————–
2 – :2 (number): 6
4 – :2 (number, primary=2)
predicate information (identified by operation id):
—————————————————
1 – filter(((:choice=2 and “suburb”=:val) or (“street”=:val and :choice=1)))
–//选择全表扫描.加入提示:/*+ or_expand(@sel$1) */
select /*+ or_expand(@sel$1) */ data from address where ( :choice = 1 and street = :val ) or ( :choice = 2 and suburb = :val );
scott@test01p> @ dpc ” outline
plan hash value: 1427591975
————————————————————————————————————————————————————–
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers | reads |
————————————————————————————————————————————————————–
| 0 | select statement | | 1 | | | 456 (100)| | 10 |00:00:00.06 | 13 | 4 |
| 1 | view | vw_ore_b7380f92 | 1 | 10010 | 997k| 456 (1)| 00:00:01 | 10 |00:00:00.06 | 13 | 4 |
| 2 | union-all | | 1 | | | | | 10 |00:00:00.06 | 13 | 4 |
|* 3 | filter | | 1 | | | | | 10 |00:00:00.06 | 13 | 4 |
| 4 | table access by index rowid batched| address | 1 | 10 | 1050 | 11 (0)| 00:00:01 | 10 |00:00:00.06 | 13 | 4 |
|* 5 | index range scan | i_address_stress | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.06 | 3 | 4 |
|* 6 | filter | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 7 | table access full | address | 0 | 10000 | 1054k| 445 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
————————————————————————————————————————————————————–
query block name / object alias (identified by operation id):
————————————————————-
1 – set$9162bf3c / vw_ore_b7380f92@sel$b7380f92
2 – set$9162bf3c
3 – set$9162bf3c_1
4 – set$9162bf3c_1 / address@sel$1
5 – set$9162bf3c_1 / address@sel$1
6 – set$9162bf3c_2
7 – set$9162bf3c_2 / address@sel$1
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(@”set$9162bf3c_2″)
outline_leaf(@”set$9162bf3c_1″)
outline_leaf(@”set$9162bf3c”)
or_expand(@”sel$1″ (1) (2))
outline_leaf(@”sel$b7380f92″)
outline(@”set$9162bf3c”)
or_expand(@”sel$1″ (1) (2))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
outline(@”sel$1″)
no_access(@”sel$b7380f92″ “vw_ore_b7380f92″@”sel$b7380f92”)
index_rs_asc(@”set$9162bf3c_1″ “address”@”sel$1” (“address”.”street”))
batch_table_access_by_rowid(@”set$9162bf3c_1″ “address”@”sel$1”)
full(@”set$9162bf3c_2″ “address”@”sel$1”)
end_outline_data
*/
peeked binds (identified by position):
————————————–
2 – :2 (number): 6
4 – :2 (number, primary=2)
predicate information (identified by operation id):
—————————————————
3 – filter(:choice=1)
5 – access(“street”=:val)
6 – filter(:choice=2)
7 – filter((“suburb”=:val and (lnnvl(:choice=1) or lnnvl(“street”=:val))))
–//注意看下划线.
–//但是使用下划线提示,改动代码的情况下如何呢?
select /*+ or_expand(@sel$1 (1) (2) ) */ data
from address
where ( :choice = 1 and street = :val )
or ( :choice = 2 and suburb = :val )
or ( :choice = 3 and post_code = :val);
scott@test01p> @ dpc ” outline
…
plan hash value: 1427591975
—————————————————————————————————————————————————–
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers |
—————————————————————————————————————————————————–
| 0 | select statement | | 1 | | | 456 (100)| | 10 |00:00:00.01 | 13 |
| 1 | view | vw_ore_b7380f92 | 1 | 10010 | 997k| 456 (1)| 00:00:01 | 10 |00:00:00.01 | 13 |
| 2 | union-all | | 1 | | | | | 10 |00:00:00.01 | 13 |
|* 3 | filter | | 1 | | | | | 10 |00:00:00.01 | 13 |
| 4 | table access by index rowid batched| address | 1 | 10 | 1050 | 11 (0)| 00:00:01 | 10 |00:00:00.01 | 13 |
|* 5 | index range scan | i_address_stress | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 3 |
|* 6 | filter | | 1 | | | | | 0 |00:00:00.01 | 0 |
|* 7 | table access full | address | 0 | 10000 | 1054k| 445 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
—————————————————————————————————————————————————–
query block name / object alias (identified by operation id):
————————————————————-
1 – set$9162bf3c / vw_ore_b7380f92@sel$b7380f92
2 – set$9162bf3c
3 – set$9162bf3c_1
4 – set$9162bf3c_1 / address@sel$1
5 – set$9162bf3c_1 / address@sel$1
6 – set$9162bf3c_2
7 – set$9162bf3c_2 / address@sel$1
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(@”set$9162bf3c_2″)
outline_leaf(@”set$9162bf3c_1″)
outline_leaf(@”set$9162bf3c”)
or_expand(@”sel$1″ (1) (2))
outline_leaf(@”sel$b7380f92″)
outline(@”set$9162bf3c”)
or_expand(@”sel$1″ (1) (2))
outline(@”sel$1″)
no_access(@”sel$b7380f92″ “vw_ore_b7380f92″@”sel$b7380f92”)
index_rs_asc(@”set$9162bf3c_1″ “address”@”sel$1” (“address”.”street”))
batch_table_access_by_rowid(@”set$9162bf3c_1″ “address”@”sel$1”)
full(@”set$9162bf3c_2″ “address”@”sel$1”)
end_outline_data
*/
peeked binds (identified by position):
————————————–
2 – :2 (number): 6
4 – :2 (number, primary=2)
predicate information (identified by operation id):
—————————————————
3 – filter(:choice=1)
5 – access(“street”=:val)
6 – filter(:choice=2)
7 – filter((“suburb”=:val and (lnnvl(:choice=1) or lnnvl(“street”=:val))))
–//正像链接讲的那样如果增加1个或条件,导致执行计划变得不合理.实际上跟严重的是查询发生了错误.
–//如果仔细看predicate information 就很容易发现没有:choice=1的filter.如果查询:
scott@test01p> variable choice number = 3
select /*+ or_expand(@sel$1 (1) (2) ) */ data
from address
where ( :choice = 1 and street = :val )
or ( :choice = 2 and suburb = :val )
or ( :choice = 3 and post_code = :val);
no rows selected.
–//取消提示:
select data from address where ( :choice = 1 and street = :val ) or ( :choice = 2 and suburb = :val ) or ( :choice = 3 and post_code = :val);
…
1000 rows selected.
–//两者的结果集不一样.明显这个是一个bug.
修改如下:
select /*+ or_expand(@sel$1 (1) (2) (3) ) */ data
from address
where ( :choice = 1 and street = :val )
or ( :choice = 2 and suburb = :val )
or ( :choice = 3 and post_code = :val);
…
–//注使用提示/*+ or_expand(@sel$1 ) */结果是正确的.
scott@test01p> @ dpc ” outline
plan hash value: 3525475520
—————————————————————————————————————————————————–
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers |
—————————————————————————————————————————————————–
| 0 | select statement | | 1 | | | 900 (100)| | 1000 |00:00:00.02 | 1640 |
| 1 | view | vw_ore_b7380f92 | 1 | 11009 | 1096k| 900 (1)| 00:00:01 | 1000 |00:00:00.02 | 1640 |
| 2 | union-all | | 1 | | | | | 1000 |00:00:00.02 | 1640 |
|* 3 | filter | | 1 | | | | | 0 |00:00:00.01 | 0 |
| 4 | table access by index rowid batched| address | 0 | 10 | 1050 | 11 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | index range scan | i_address_stress | 0 | 10 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 6 | filter | | 1 | | | | | 0 |00:00:00.01 | 0 |
|* 7 | table access full | address | 0 | 10000 | 1054k| 445 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 8 | filter | | 1 | | | | | 1000 |00:00:00.02 | 1640 |
|* 9 | table access full | address | 1 | 999 | 108k| 445 (1)| 00:00:01 | 1000 |00:00:00.02 | 1640 |
—————————————————————————————————————————————————–
query block name / object alias (identified by operation id):
————————————————————-
1 – set$49e1c21b / vw_ore_b7380f92@sel$b7380f92
2 – set$49e1c21b
3 – set$49e1c21b_1
4 – set$49e1c21b_1 / address@sel$1
5 – set$49e1c21b_1 / address@sel$1
6 – set$49e1c21b_2
7 – set$49e1c21b_2 / address@sel$1
8 – set$49e1c21b_3
9 – set$49e1c21b_3 / address@sel$1
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(@”set$49e1c21b_3″)
outline_leaf(@”set$49e1c21b_2″)
outline_leaf(@”set$49e1c21b_1″)
outline_leaf(@”set$49e1c21b”)
or_expand(@”sel$1″ (1) (2) (3))
outline_leaf(@”sel$b7380f92″)
outline(@”set$49e1c21b”)
or_expand(@”sel$1″ (1) (2) (3))
outline(@”sel$1″)
no_access(@”sel$b7380f92″ “vw_ore_b7380f92″@”sel$b7380f92”)
index_rs_asc(@”set$49e1c21b_1″ “address”@”sel$1” (“address”.”street”))
batch_table_access_by_rowid(@”set$49e1c21b_1″ “address”@”sel$1”)
full(@”set$49e1c21b_2″ “address”@”sel$1”)
full(@”set$49e1c21b_3″ “address”@”sel$1”)
end_outline_data
*/
peeked binds (identified by position):
————————————–
2 – :2 (number): 6
4 – :2 (number, primary=2)
6 – :2 (number, primary=2)
predicate information (identified by operation id):
—————————————————
3 – filter(:choice=1)
5 – access(“street”=:val)
6 – filter(:choice=2)
7 – filter((“suburb”=:val and (lnnvl(:choice=1) or lnnvl(“street”=:val))))
8 – filter(:choice=3)
9 – filter((“post_code”=:val and (lnnvl(:choice=1) or lnnvl(“street”=:val)) and (lnnvl(:choice=2) or lnnvl(“suburb”=:val))))
3.测试使用use_concate看看:
set linesize 100
select /*+ use_concat */ data
from address
where ( :choice = 1 and street = :val )
or ( :choice = 2 and suburb = :val )
or ( :choice = 3 and post_code = :val);
scott@test01p> @ dpc ” outline
…
plan hash value: 2048882018
—————————————————————————————————————————————————-
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers |
—————————————————————————————————————————————————-
| 0 | select statement | | 1 | | | 900 (100)| | 1000 |00:00:00.01 | 1640 |
| 1 | concatenation | | 1 | | | | | 1000 |00:00:00.01 | 1640 |
|* 2 | filter | | 1 | | | | | 0 |00:00:00.01 | 0 |
| 3 | table access by index rowid batched| address | 0 | 10 | 1110 | 11 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 4 | index range scan | i_address_stress | 0 | 10 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | filter | | 1 | | | | | 1000 |00:00:00.01 | 1640 |
|* 6 | table access full | address | 1 | 1000 | 108k| 445 (1)| 00:00:01 | 1000 |00:00:00.01 | 1640 |
|* 7 | filter | | 1 | | | | | 0 |00:00:00.01 | 0 |
|* 8 | table access full | address | 0 | 9999 | 1083k| 445 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
—————————————————————————————————————————————————-
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$1
3 – sel$1_1 / address@sel$1
4 – sel$1_1 / address@sel$1
6 – sel$1_2 / address@sel$1_2
8 – sel$1_3 / address@sel$1_3
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$1″)
outline_leaf(@”sel$1_1″)
use_concat(@”sel$1″ 8 or_predicates(1) predicate_reorders((5 2) (6 3) (7 4) (8 5) (9 6) (10 7) (2 8) (4 9) (3 10)))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
–//始终不明白里面那一串表示什么?
outline_leaf(@”sel$1_2″)
outline_leaf(@”sel$1_3″)
outline(@”sel$1″)
index_rs_asc(@”sel$1_1″ “address”@”sel$1” (“address”.”street”))
batch_table_access_by_rowid(@”sel$1_1″ “address”@”sel$1”)
full(@”sel$1_2″ “address”@”sel$1_2”)
full(@”sel$1_3″ “address”@”sel$1_3”)
end_outline_data
*/
peeked binds (identified by position):
————————————–
2 – :2 (number): 6
4 – :2 (number, primary=2)
6 – :2 (number, primary=2)
predicate information (identified by operation id):
—————————————————
2 – filter(:choice=1)
4 – access(“street”=:val)
5 – filter(:choice=3)
6 – filter((“post_code”=:val and (lnnvl(“street”=:val) or lnnvl(:choice=1))))
7 – filter(:choice=2)
8 – filter((“suburb”=:val and (lnnvl(:choice=3) or lnnvl(“post_code”=:val)) and (lnnvl(“street”=:val) or lnnvl(:choice=1))))