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.
- ‘USABLE’ that covers the ‘NEW’, ‘MISSING_STATS’ and ‘PERMANENT’ which means that a directive is there to be evaluated by the optimizer, but the issue is not solved yet.
- ‘SUPERSEDED’ when it has been solved (the ‘HAS_STATS’) or it is redundant with another directive, which means that the issue is solved somewhere else.
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]}