袁卓坚,张天 邓紫棋,色爱
[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))))
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
Oracle字符串函数length、upper、lower、initcap、 concat、instr、replace
我要评论
网友评论