Oracle SQL计划指令

团队，
阅读这篇文章 https://blogs.oracle.com/oraclemagazine/on-learning-from-mistakes 并在我的本地数据库中设置这样的演示。
在下面的示例中，假装所有者为 “状态”，而OBJECT_TYPE为 “城市” (类似于上面演示的示例，以演示数据的偏斜分布)。

create table t 
as 
 select *
 from all_objects;
select to_char( sysdate, 'dd-mon-yyyy hh24:mi') now, table_name,num_rows,
       to_char( last_analyzed, 'dd-mon-yyyy hh24:mi') last_analyzed
from user_tab_statistics
where table_name ='T';
select column_name,
    num_distinct,
 trunc(72337/num_distinct) as the_guess,
 num_nulls,
    histogram,notes
from user_tab_col_statistics
where table_name ='T'
and column_name in ('OWNER','OBJECT_TYPE')
order by column_name ;

set serveroutput off
select /*+ gather_plan_statistics */ count(*)
from t 
where owner ='SYS';
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
select /*+ gather_plan_statistics */ count(*)
from t 
where owner ='WMSYS';
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',no_invalidate=>false);
PL/SQL procedure successfully completed.

demo@ORA12C> select column_name,
  2      num_distinct,
  3     trunc(72340/decode(num_distinct,0,null,num_distinct)) as the_guess,
  4     num_nulls,
  5      histogram,notes
  6  from user_tab_col_statistics
  7  where table_name ='T'
  8  and column_name in ('OWNER','OBJECT_TYPE')
  9  order by column_name ;
COLUMN_NAME          NUM_DISTINCT  THE_GUESS  NUM_NULLS HISTOGRAM       NOTES
-------------------- ------------ ---------- ---------- --------------- -------------------------------
OBJECT_TYPE                    38       1903          0 NONE
OWNER                          30       2411          0 FREQUENCY

demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where owner ='SYS';
  COUNT(*)
----------
     47713

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  4yavh9z8v9cwr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where owner ='SYS'
Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.03 |    1436 |      4 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |    1436 |      4 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  47713 |  47713 |00:00:00.04 |    1436 |      4 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"='SYS')

19 rows selected.
demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where owner ='WMSYS';
  COUNT(*)
----------
       374

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  6qtmz04h2z2t0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where owner ='WMSYS'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1436 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1436 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    374 |    374 |00:00:00.01 |    1436 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"='WMSYS')

20 rows selected.

select /*+ gather_plan_statistics */ count(*)
from t 
where object_type ='SYNONYM';
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
select /*+ gather_plan_statistics */ count(*)
from t 
where object_type ='WINDOW';
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',no_invalidate=>false);

PL/SQL procedure successfully completed.

demo@ORA12C> select column_name,
  2      num_distinct,
  3     trunc(72337/decode(num_distinct,0,null,num_distinct)) as the_guess,
  4     num_nulls,
  5      histogram,notes
  6  from user_tab_col_statistics
  7  where table_name ='T'
  8  and column_name in ('OWNER','OBJECT_TYPE')
  9  order by column_name ;

COLUMN_NAME          NUM_DISTINCT  THE_GUESS  NUM_NULLS HISTOGRAM       NOTES
-------------------- ------------ ---------- ---------- --------------- --------------------------------
OBJECT_TYPE                    38       1903          0 FREQUENCY
OWNER                          30       2411          0 FREQUENCY
demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where object_type ='SYNONYM';
  COUNT(*)
----------
     12513
  
demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  ddtjv20hx8vyd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where object_type='SYNONYM'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1436 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1436 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  12513 |  12513 |00:00:00.01 |    1436 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_TYPE"='SYNONYM')

demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where object_type ='WINDOW';
  COUNT(*)
----------
         9
demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  bhvtspf7sa1xy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where object_type='WINDOW'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1436 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1436 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      9 |      9 |00:00:00.01 |    1436 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_TYPE"='WINDOW')

能够跟进-在所有者上发布直方图，并且OBJECT_TYPE估计值与实际值匹配。

对于涉及两个列的复杂谓词，优化器首先会犯错，然后使用 “统计反馈 (aka-基数反馈)” 重新优化它们。

demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where object_type ='TABLE SUBPARTITION'
  4  and owner ='DEMO';

  COUNT(*)
----------
        18
demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  1a9sbz16k7shh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where object_type='TABLE SUBPARTITION' and owner ='DEMO'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1436 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1436 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      1 |     18 |00:00:00.01 |    1436 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("OBJECT_TYPE"='TABLE SUBPARTITION' AND "OWNER"='DEMO'))

demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where object_type ='TABLE SUBPARTITION'
  4  and owner ='DEMO';

  COUNT(*)
----------
        18
demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  1a9sbz16k7shh, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where object_type='TABLE SUBPARTITION' and owner ='DEMO'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1436 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1436 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |     18 |     18 |00:00:00.01 |    1436 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("OBJECT_TYPE"='TABLE SUBPARTITION' AND "OWNER"='DEMO'))
Note
-----
   - statistics feedback used for this statement

法拉盛SPD得到了这个

demo@ORA12C> exec dbms_spd.flush_sql_plan_directive;
PL/SQL procedure successfully completed.
demo@ORA12C> select d2.owner,d2.object_name,
  2        d2.subobject_name,
  3        d2.object_type,
  4        d1.type,
  5        d1.state,
  6        d1.reason
  7  from dba_sql_plan_directives d1,
  8       dba_sql_plan_dir_objects d2
  9  where d1.directive_id = d2.directive_id
 10  and d2.owner = user
 11  and d2.object_name ='T';
OWNER      OBJECT_NAM SUBOBJECT_NA OBJECT_TYP TYPE                    STATE      REASON
---------- ---------- ------------ ---------- ----------------------- ---------- ------------------------------------
DEMO       T          OWNER        COLUMN     DYNAMIC_SAMPLING        USABLE     SINGLE TABLE CARDINALITY MISESTIMATE
DEMO       T          OBJECT_TYPE  COLUMN     DYNAMIC_SAMPLING        USABLE     SINGLE TABLE CARDINALITY MISESTIMATE
DEMO       T                       TABLE      DYNAMIC_SAMPLING        USABLE     SINGLE TABLE CARDINALITY MISESTIMATE

demo@ORA12C>

Question#1 - next execution of the query with different values for (owner,object_type) combination - doesn't influence SPD with Dynamic sampling, instead still uses "Statistics feedback" - why?

demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where object_type ='TRIGGER'
  4  and owner ='APEX_050100';
  COUNT(*)
----------
       501
demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  6dgj5tnkrndcj, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where object_type ='TRIGGER' and owner ='APEX_050100'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1436 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1436 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |     28 |    501 |00:00:00.01 |    1436 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("OBJECT_TYPE"='TRIGGER' AND "OWNER"='APEX_050100'))
demo@ORA12C> select /*+ gather_plan_statistics */ count(*)
  2  from t
  3  where object_type ='TRIGGER'
  4  and owner ='APEX_050100';
  COUNT(*)
----------
       501
demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  6dgj5tnkrndcj, child number 2
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where object_type ='TRIGGER' and owner ='APEX_050100'
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1436 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1436 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    501 |    501 |00:00:00.01 |    1436 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("OBJECT_TYPE"='TRIGGER' AND "OWNER"='APEX_050100'))
Note
-----
   - statistics feedback used for this statement
24 rows selected.
demo@ORA12C>

Question#2 - post the SPD creation, gathering stats on the table doesn't create extended stats - please help us to understand what are we missing here?

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',no_invalidate=>false);

PL/SQL procedure successfully completed.

demo@ORA12C> select * from user_stat_extensions
  2  where table_name ='T';

no rows selected

demo@ORA12C> select column_name,
  2      num_distinct,
  3     trunc(72337/decode(num_distinct,0,null,num_distinct)) as the_guess,
  4     num_nulls,
  5      histogram,notes
  6  from user_tab_col_statistics
  7  where table_name ='T'
  8  and ( column_name in ('OWNER','OBJECT_TYPE')
  9    or column_name like 'SYS%' )
 10  order by column_name ;

COLUMN_NAME          NUM_DISTINCT  THE_GUESS  NUM_NULLS HISTOGRAM       NOTES
-------------------- ------------ ---------- ---------- --------------- -------------------
OBJECT_TYPE                    38       1903          0 FREQUENCY
OWNER                          30       2411          0 FREQUENCY

demo@ORA12C>

抱歉这么大的帖子。

demo@ORA12C> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
optimizer_adaptive_plans             boolean     TRUE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_adaptive_statistics        boolean     FALSE
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      12.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_inmemory_aware             boolean     TRUE
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
demo@ORA12C>

很高兴看到我们的用户自助!这个答案是原始提交者发回给我们的!

Got answer’s to both my question’s after reading this blog post from Optimizer team DBI services Team.

https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2

https://blog.dbi-services.com/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1/


Question#1 - next execution of the query with different values for (owner,object_type) combination - doesn't influence SPD with Dynamic sampling, instead still uses "Statistics feedback" - why?

Answer: Having Optimizer_adaptive_statistics = False (by default) – doesn’t disable the creation of SPD, instead it prevents them from being used in conjunction with Dynamic statistics to influence new execution plans – Hence SPD got created but doesn’t involve in further optimization – subsequent execution of sql with similar combination of predicates produced cardinality mismatch –statistics feedback kickin again.

Question#2 - post the SPD creation, gathering stats on the table doesn't create extended stats - please help us to understand what are we missing here?

Answer: Starting with 12.2 automatic column group creation is controlled by DBMS_STATS preference AUTO_STAT_EXTENSIONS – which is OFF by default, setting it to ON and re-running my demo, created the extended stats automatically.