欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20181225]12CR2 SQL Plan Directives.txt

程序员文章站 2022-07-06 12:10:33
[20181225]12CR2 SQL Plan Directives.txt--//12C引入SQL PLAN Directives.12cR1版本会造成大量的动态取样,影响性能.许多人把OPTIMIZER_ADAPTIVE_FEATURES设置为false.--//这也是为什么我不主张将XX.1 ......

[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后再分析自动建立扩展统计信息.