[20181225]12cr2 sql plan directives.txt
–//12c引入sql plan directives.12cr1版本会造成大量的动态取样,影响性能.许多人把optimizer_adaptive_features设置为false.
–//这也是为什么我不主张将xx.1版本使用在生产系统.12cr2做了一些改进,废除了optimizer_adaptive_features参数.使用2个新的
–//参数optimizer_adaptive_plans,optimizer_adaptive_statistics,缺省前者true,后者为false.
–//通过测试说明问题.
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
scott@test01p> show parameter optimizer_adaptive
name type value
——————————— ——- ——
optimizer_adaptive_plans boolean true
optimizer_adaptive_reporting_only boolean false
optimizer_adaptive_statistics boolean false
–//注:没有optimizer_adaptive_features参数,optimizer_adaptive_plans=true,optimizer_adaptive_statistics=false.
2.建立测试环境:
create table t
as
select rownum id
,lpad (‘x’, 20, ‘x’) name
,mod (rownum, 3) flag1
,mod (rownum, 3) flag2
,mod (rownum, 3) flag3
from dual
connect by level <= 1e5;
–//说明:flags1,flags2,flags3分别存在3个取值,按照道理存在27种选择.因为存在相关性,仅仅存在3种选择.
3.测试:
scott@test01p> alter session set statistics_level=all;
session altered.
scott@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1;
count(distinctname)
——————-
1
scott@test01p> @ dpc ” ”
plan_table_output
————————————-
sql_id 872fdta99gdk8, child number 0
————————————-
select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
plan hash value: 2359337548
—————————————————————————————————————————————————————
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers | reads | omem | 1mem | used-mem |
—————————————————————————————————————————————————————
| 0 | select statement | | 1 | | | 155 (100)| | 1 |00:00:00.06 | 556 | 540 | | | |
| 1 | sort aggregate | | 1 | 1 | 12 | | | 1 |00:00:00.06 | 556 | 540 | | | |
| 2 | view | vw_dag_0 | 1 | 1 | 12 | 155 (2)| 00:00:01 | 1 |00:00:00.06 | 556 | 540 | | | |
| 3 | hash group by | | 1 | 1 | 30 | 155 (2)| 00:00:01 | 1 |00:00:00.06 | 556 | 540 | 1345k| 1345k| 504k (0)|
|* 4 | table access full| t | 1 | 3704 | 108k| 154 (1)| 00:00:01 | 33334 |00:00:00.06 | 556 | 540 | | | |
—————————————————————————————————————————————————————
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$c33c846d
2 – sel$5771d262 / vw_dag_0@sel$c33c846d
3 – sel$5771d262
4 – sel$5771d262 / t@sel$1
predicate information (identified by operation id):
—————————————————
4 – filter((“flag1″=1 and “flag2″=1 and “flag3″=1))
–//注意看id=4, e-rows=3704,估算按照100000/27 = 3703.7,而a-rows=33334(10000/3 = 3333.3),存在很大偏差.
scott@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id =’872fdta99gdk8′;
sql_id child_number i
————- ———— –
872fdta99gdk8 0 y
–//is_reoptimizable=’y’
scott@test01p> exec dbms_spd.flush_sql_plan_directive;
pl/sql procedure successfully completed.
set numw 20
column notes format a50
select directive_id
,type
,enabled
,state
,notes
,reason
from dba_sql_plan_directives
where directive_id in (select directive_id
from dba_sql_plan_dir_objects
where owner = user and object_name = ‘t’);
directive_id type ena state notes reason
——————– ——————– — ——————– ————————————————– ————————————
17342821566768621333 dynamic_sampling yes usable <spd_note><internal_state>new</internal_state><red single table cardinality misestimate
undant>no</redundant><spd_text>{ec(scott.t)[flag1,
flag2, flag3]}</spd_text></spd_note>
–//指导建议字段flag1,flag2,flag3联合查询时存在偏差,建议动态取样.
–//补充说明:{ec(scott.t)[flag1,flag2, flag3]}
–//这里的e和c，以及可能出现其他的字符，解释如下：
e – equality_predicates_only
c – simple_column_predicates_only
j – index_access_by_join_predicates
f – filter_on_joining_object
–//再次执行:
scott@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1;
count(distinctname)
——————-
1
scott@test01p> @ dpc ” ”
plan_table_output
————————————-
sql_id 872fdta99gdk8, child number 1
————————————-
select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
plan hash value: 2359337548
——————————————————————————————————————————————————
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers | omem | 1mem | used-mem |
——————————————————————————————————————————————————
| 0 | select statement | | 1 | | | 156 (100)| | 1 |00:00:00.01 | 556 | | | |
| 1 | sort aggregate | | 1 | 1 | 12 | | | 1 |00:00:00.01 | 556 | | | |
| 2 | view | vw_dag_0 | 1 | 1 | 12 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | | | |
| 3 | hash group by | | 1 | 1 | 30 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | 1345k| 1345k| 505k (0)|
|* 4 | table access full| t | 1 | 33334 | 976k| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 | | | |
——————————————————————————————————————————————————
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$c33c846d
2 – sel$5771d262 / vw_dag_0@sel$c33c846d
3 – sel$5771d262
4 – sel$5771d262 / t@sel$1
predicate information (identified by operation id):
—————————————————
4 – filter((“flag1″=1 and “flag2″=1 and “flag3″=1))
note
—–
– statistics feedback used for this statement
–//注意note,指示statistics feedback used for this statement.
scott@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id =’872fdta99gdk8′;
sql_id child_number i
————- ——————– –
872fdta99gdk8 0 y
872fdta99gdk8 1 n
scott@test01p> @ share 872fdta99gdk8
sql_text = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
sql_id = 872fdta99gdk8
address = 000007ff1393f830
child_address = 000007ff13d9c198
child_number = 0
use_feedback_stats = y
reason = <childnode><childnumber>0</childnumber><id>48</id><reason>auto reoptimization mismatch(1)</reason><size>3×4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>0</dnum_kksfcxe></childnode>
————————————————–
sql_text = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
sql_id = 872fdta99gdk8
address = 000007ff1393f830
child_address = 000007ff115a7e58
child_number = 1
reason =
————————————————–
pl/sql procedure successfully completed.
select directive_id
,type
,enabled
,state
,notes
,reason
from dba_sql_plan_directives
where directive_id in (select directive_id
from dba_sql_plan_dir_objects
where owner = user and object_name = ‘t’);
directive_id type ena state notes reason
——————– ——————– — ——————– ————————————————– ————————————
17342821566768621333 dynamic_sampling yes usable <spd_note><internal_state>new</internal_state><red single table cardinality misestimate
undant>no</redundant><spd_text>{ec(scott.t)[flag1,
flag2, flag3]}</spd_text></spd_note>
4.继续测试:
–//设置optimizer_adaptive_statistics=true看看.
scott@test01p> alter session set optimizer_adaptive_statistics=true ;
session altered.
scott@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1;
count(distinctname)
——————-
1
scott@test01p> @ dpc ” ”
plan_table_output
————————————-
sql_id 872fdta99gdk8, child number 2
————————————-
select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
plan hash value: 2359337548
——————————————————————————————————————————————————
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers | omem | 1mem | used-mem |
——————————————————————————————————————————————————
| 0 | select statement | | 1 | | | 157 (100)| | 1 |00:00:00.01 | 556 | | | |
| 1 | sort aggregate | | 1 | 1 | 12 | | | 1 |00:00:00.01 | 556 | | | |
| 2 | view | vw_dag_0 | 1 | 1 | 12 | 157 (3)| 00:00:01 | 1 |00:00:00.01 | 556 | | | |
| 3 | hash group by | | 1 | 1 | 30 | 157 (3)| 00:00:01 | 1 |00:00:00.01 | 556 | 1345k| 1345k| 496k (0)|
|* 4 | table access full| t | 1 | 48497 | 1420k| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 | | | |
——————————————————————————————————————————————————
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$c33c846d
2 – sel$5771d262 / vw_dag_0@sel$c33c846d
3 – sel$5771d262
4 – sel$5771d262 / t@sel$1
predicate information (identified by operation id):
—————————————————
4 – filter((“flag1″=1 and “flag2″=1 and “flag3″=1))
note
—–
– dynamic statistics used: dynamic sampling (level=2)
– 1 sql plan directive used for this statement
–//设置optimizer_adaptive_statistics=true的情况下,做了动态取样(level=2).产生新的子光标.
scott@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id =’872fdta99gdk8′;
sql_id child_number i
————- ——————– –
872fdta99gdk8 0 y
872fdta99gdk8 1 n
872fdta99gdk8 2 n
scott@test01p> @ share 872fdta99gdk8
old 15: and q.sql_id like ”&1”’,
new 15: and q.sql_id like ”872fdta99gdk8”’,
sql_text = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
sql_id = 872fdta99gdk8
address = 000007ff1393f830
child_address = 000007ff13d9c198
child_number = 0
use_feedback_stats = y
reason = <childnode><childnumber>0</childnumber><id>48</id><reason>auto reoptimization mismatch(1)</reason><size>3×4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>0</dnum_kksfcxe></childnode>
————————————————–
sql_text = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
sql_id = 872fdta99gdk8
address = 000007ff1393f830
child_address = 000007ff115a7e58
child_number = 1
reason = <childnode><childnumber>1</childnumber><id>3</id><reason>optimizer mismatch(12)</reason><size>2×440</size><_optimizer_dsdir_usage_control> 0 126 </_optimizer_dsdir_usage_control><optimizer_adaptive_stat
istics> false
true </optimizer_adaptive_statistics><_optimizer_use_feedback_for_join> false true </_optimizer_use_feedback_for_join><_optimizer_ads_for_pq> false true </_optimizer_ads_for_pq></childnode>
————————————————–
sql_text = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
sql_id = 872fdta99gdk8
address = 000007ff1393f830
child_address = 000007ff0fdbe618
child_number = 2
optimizer_mismatch = y
reason =
————————————————–
scott@test01p> exec dbms_spd.flush_sql_plan_directive;
pl/sql procedure successfully completed.
select directive_id
,type
,enabled
,state
,notes
,reason
from dba_sql_plan_directives
where directive_id in (select directive_id
from dba_sql_plan_dir_objects
where owner = user and object_name = ‘t’);
directive_id type ena state notes reason
——————– ——————– — —— ————————————————– ————————————
14350253949522184195 dynamic_sampling_res yes usable <spd_note><internal_state>new</internal_state><red verify cardinality estimate
ult undant>no</redundant><spd_text>{(scott.t, num_rows
=100000) – (sql_id:4k5yrxfcvd5qb, t.card=48497[-2
-2])}</spd_text></spd_note>
17342821566768621333 dynamic_sampling yes usable <spd_note><internal_state>missing_stats</internal_ single table cardinality misestimate
state><redundant>no</redundant><spd_text>{ec(scott
.t)[flag1, flag2, flag3]}</spd_text></spd_note>
–//多了一行,动态取样分析后估计t.card=48497,虽然与实际a-rows=33334还是存在很大偏差.指导提示是missing_stats.
–//补充说明sql_id:4k5yrxfcvd5qb,我没有查询到对于sql语句,有点奇怪!!
scott@test01p> exec dbms_stats.gather_table_stats(user,’t’,options=>’gather auto’,no_invalidate=>false);
pl/sql procedure successfully completed.
scott@test01p> select column_name,num_buckets,histogram from user_tab_col_statistics where table_name =’t’;
column_name num_buckets histogram
———– ———– —————
id 1 none
name 1 none
flag1 3 frequency
flag2 3 frequency
flag3 3 frequency
–//并没有指导建议生成相关列的统计信息.
–//实际上12cr2引入新参数auto_stat_extensions控制extended stats的收集,缺省设置off.(没有打开).设置auto_stat_extensions=on可以打开.
scott@test01p> select dbms_stats.get_prefs(‘auto_stat_extensions’) c10 from dual;
c10
———-
off
scott@test01p> exec dbms_stats.set_global_prefs(‘auto_stat_extensions’,’on’) ;
pl/sql procedure successfully completed.
scott@test01p> select dbms_stats.get_prefs(‘auto_stat_extensions’) c10 from dual;
c10
———-
on
scott@test01p> exec dbms_stats.gather_table_stats(user,’t’,options=>’gather auto’,no_invalidate=>false);
pl/sql procedure successfully completed.
scott@test01p> column column_name format a30
scott@test01p> select column_name,num_buckets,histogram from user_tab_col_statistics where table_name =’t’;
column_name num_buckets histogram
—————————— ———– —————
id 1 none
name 1 none
flag1 3 frequency
flag2 3 frequency
flag3 3 frequency
sys_sts0sr$hpc$e#kvdpen#0r2jou 3 frequency
6 rows selected.
scott@test01p> column extension_name format a30
scott@test01p> select * from user_stat_extensions where table_name =’t’;
table_name extension_name extension creator dro
———- —————————— ————————- ——- —
t sys_sts0sr$hpc$e#kvdpen#0r2jou (“flag1″,”flag2″,”flag3”) system yes
–//可以发现现在收集了相关列(“flag1″,”flag2″,”flag3”)的统计,并且建立了直方图.
scott@test01p> alter session set optimizer_adaptive_statistics=false ;
session altered.
scott@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1;
count(distinctname)
——————-
1
scott@test01p> @ dpc ” ”
plan_table_output
————————————-
sql_id 872fdta99gdk8, child number 1
————————————-
select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1
plan hash value: 2359337548
——————————————————————————————————————————————————
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers | omem | 1mem | used-mem |
——————————————————————————————————————————————————
| 0 | select statement | | 1 | | | 156 (100)| | 1 |00:00:00.01 | 556 | | | |
| 1 | sort aggregate | | 1 | 1 | 12 | | | 1 |00:00:00.01 | 556 | | | |
| 2 | view | vw_dag_0 | 1 | 1 | 12 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | | | |
| 3 | hash group by | | 1 | 1 | 30 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | 1345k| 1345k| 507k (0)|
|* 4 | table access full| t | 1 | 33334 | 976k| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 | | | |
——————————————————————————————————————————————————
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$c33c846d
2 – sel$5771d262 / vw_dag_0@sel$c33c846d
3 – sel$5771d262
4 – sel$5771d262 / t@sel$1
predicate information (identified by operation id):
—————————————————
4 – filter((“flag1″=1 and “flag2″=1 and “flag3″=1))
–//可以发现e-rows已经正确修正.
scott@test01p> exec dbms_spd.flush_sql_plan_directive;
pl/sql procedure successfully completed.
directive_id type ena state notes reason
——————– ——————– — ——————– ————————————————– ————————————
14350253949522184195 dynamic_sampling_res yes usable <spd_note><internal_state>new</internal_state><red verify cardinality estimate
ult undant>no</redundant><spd_text>{(scott.t, num_rows
=100000) – (sql_id:4k5yrxfcvd5qb, t.card=48497[-2
-2])}</spd_text></spd_note>
17342821566768621333 dynamic_sampling yes superseded <spd_note><internal_state>has_stats</internal_stat single table cardinality misestimate
e><redundant>no</redundant><spd_text>{ec(scott.t)[
flag1, flag2, flag3]}</spd_text></spd_note>
–//注意看现在不是missing_stats而是提示has_stats. superseded 表示 取代，接替.
–//有了相关列统计其它涉及相关列的查询就不会在动态取样,而是估计行数与实际行数接近.而且执行其它类似语句也不会出现is_reoptimizable=’y’的情况.
scott@test01p> select max(id) from t where flag1=1 and flag2=1 and flag3=1;
max(id)
———-
100000
scott@test01p> @ dpc ” ”
plan_table_output
————————————-
sql_id 6stmvx0gcybbg, child number 0
————————————-
select max(id) from t where flag1=1 and flag2=1 and flag3=1
plan hash value: 2966233522
———————————————————————————————————————
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | a-rows | a-time | buffers |
———————————————————————————————————————
| 0 | select statement | | 1 | | | 154 (100)| | 1 |00:00:00.01 | 556 |
| 1 | sort aggregate | | 1 | 1 | 14 | | | 1 |00:00:00.01 | 556 |
|* 2 | table access full| t | 1 | 33334 | 455k| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 |
———————————————————————————————————————
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$1
2 – sel$1 / t@sel$1
predicate information (identified by operation id):
—————————————————
2 – filter((“flag1″=1 and “flag2″=1 and “flag3″=1))
scott@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id =’6stmvx0gcybbg’;
sql_id child_number i
————- ———— –
6stmvx0gcybbg 0 n
–//is_reoptimizable = ‘n’.
总结:
–//12cr2做了一些改进,optimizer_adaptive_statistics=false,避免大量的动态取样对性能的影响.另外即使设置optimizer_adaptive_statistics=true.
–//oracle也保存了动态取样的结果.
–//dbms_stats引入新的参数auto_stat_extensions,缺省是off.设置on后再分析自动建立扩展统计信息.