SQL Plan Directives and extended statistics

Sometimes you will find in the notes of an Explainplan this:

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - 1 Sql Plan Directive used for this statement

This means oracle is doing a little magic - explained in detail in the link below.

To check where Plan Directives occur:

  select TO_CHAR (d.directive_id) dir_id
       , o.owner
       , o.object_name
       , o.subobject_name col_name
       , o.object_type
       , d.TYPE
       , d.state
       , d.reason
       , D.CREATED
       , D.ENABLED
       , D.LAST_MODIFIED
       , D.LAST_USED
    --, D.NOTES, D.CREATED, D.ENABLED, D.LAST_MODIFIED, D.LAST_USED
    from dba_sql_plan_directives d, dba_sql_plan_dir_objects o
   where d.directive_id = o.directive_id and o.owner = 'mySchema'
order by o.owner
       , o.object_name
       , o.subobject_name
       , o.object_type
       , d.TYPE
;
 

 

To show extended stats:

select USE.TABLE_NAME
     , USE.EXTENSION_NAME
     , trim(DBMS_LOB.SUBSTR(USE.EXTENSION, 100)) EXTENSION
     , USE.CREATOR
     , USE.DROPPABLE
  from user_stat_extensions use
;
 

 

 

Quelle: https://oracle-base.com/articles/12c/sql-plan-directives-12cr1