12c SQL Plan Directive: state has changed between 12.1.0.1 and 12.1.0.2

By Franck Pachot

.
SQL plan Directives have been introduced in 12c. If you have scripts that check their states (and I have that as I prefer to monitor closely the features that are new) you probably have seen a difference when going from 12.1.0.1 – the first release of 12c and the only one avilable yet in Standard Edition – and 12.1.0.2 – the first patchest. I’ll explain here what are the SQL Plan Directive states and how they changed.

When a SQL Plan Directive is created, it’s state is NEW. It means that a misestimate has been encountered but the reason has not been yet determined. Remember that the directive is created only at execution time, so very limited information is available, except the fact that A-Rows is different that E-Rows.

On a subsequent optimization (same or different statement that uses the same table and same column predicates) the optimizer sees the SQL Plan Directive and can update it with more information. If the reason of misestimation is that some statistics are missing then the state of the directive is changed from NEW to MISSING_STATS.

MISSING_STATS directives lead to short term and long term solutions:

• each new query will solve missing stats by gathering more statistics with Dynamic Sampling
• the next dbms_stats gathering will gather extended statistics to definitly fix the issue

Once the latter is done, we can expect to have good estimations without Dynamic Sampling anymore, thanks to the extended statistics. But that has to be checked. So the next query optimization will check it and update the SQL plan Directive accordingly:

• HAS_STATS to show that statistics (extended statistics here) are now sufficient to get correct estimations
• PERMANENT to show that the misestimation is still there and extended statistics have to be ignored because they were calculated for an issue that they do not solve.

SQL> select directive_id,type,state,reason,notes from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO' );

        DIRECTIVE_ID TYPE              STATE         REASON
-------------------- ---------------- ---------- ------------------------------------
NOTES
--------------------------------------------------------------------------------
 1350980939868665098 DYNAMIC_SAMPLING USABLE         SINGLE TABLE CARDINALITY MISESTIMATE

  NEW
  NO
  {EC(DEMO.DEMO_TABLE)[A, B, C, D]}

        DIRECTIVE_ID TYPE              STATE         REASON
-------------------- ---------------- ---------- ------------------------------------
NOTES
--------------------------------------------------------------------------------
 1350980939868665098 DYNAMIC_SAMPLING USABLE         SINGLE TABLE CARDINALITY MISESTIMATE

  MISSING_STATS
  NO
  {EC(DEMO.DEMO_TABLE)[A, B, C, D]}

        DIRECTIVE_ID TYPE              STATE         REASON
-------------------- ---------------- ---------- ------------------------------------
NOTES
--------------------------------------------------------------------------------
 1350980939868665098 DYNAMIC_SAMPLING SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE

  HAS_STATS
  NO
  {EC(DEMO.DEMO_TABLE)[A, B, C, D]}