[20181225]12CR2 SQL Plan Directives.txt
[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>3x4</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>3x4</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>2x440</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后再分析自动建立扩展统计信息.