This was first published on https://blog.dbi-services.com/12c-sql-plan-directive-state-has-changed-between-12101-and-12102 (2014-11-16)
Republishing here for new followers. The content is related to the the versions available at the publication date
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:
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:
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]}
Hi Frank, we have an issue with a very simple state (one table, one predicate on a column (col =’XXXXX’). Due to a certain data skew the I have seen dynamic sampling during execution of the statement. When I search for any SQL Plan Directives for this tabel and column I am seeing two different lines: One with state ‘USABLE’ and the other with ‘SUPERSEDED’. What does that mean for the future when executing the statement: Will I have dynamic sampling or not? Related question: Can I have two different SQL Plan Directives for the same table and column?
Hi Reiner. I suppose that the USABLE one can still be used to do dynamic sampling. I suppose that the ‘SUPERSEDED’ is flagged as redundant (you can see that in the NOTES) column because the other directive can be used. Can you check the NOTES from them? I don’t reproduced yet the case where you have two directives for same table/columns but I know that it happens, this the redundant flag. Thanks, Franck.
Hi again, I can assure you that both entries have the redundant flag set to ‘NO’ (NO) Reiner
Hi, I didn’t try to reproduce yet. It seems that the two directives are telling two opposite things. Interesting. What tells the execution plan? How did you gather stats? Is SEQDEV.GEN_PROTEIN_MAPPING a virtual column?
Re-reading your post, the plan directives are not on the same expressions: EC for one ECJ for the other. Can you show the info from dba_sql_plan_dir_objects ?
The ‘J’ is for index_access_by_join_predicates so it’s for different queries.
Hi Franck thanks for clarifying this. I was already wondering about the difference between EC and ECJ. Is this documented somewhere? One question is still open: Have you seen such an amount of SQL plan directives of one table? Do you think that there may be a negative performance impact due to these directives (too much dynamic sampling) ? Best Reiner
I don’t know any documentation about those EC and ECJ. And I’m sorry I don’t know the consequence of having so many directives as I’ve not seen that yes. But it’s definitely something to investigate. So many directives means that statistics are not good. And PERMANENT status means that it cannot be fixed easily.
Additional comment: the triggering of dynamic sampling when state is ‘MISSING_STATS’ is not working on 12.1.0.1 because of Bug 16571451 ‘sql plan directives aren’t used when optimizer_dynamic_sampling is default’
Hi Frank thanks for your quick response. I must confess that I haven’t looked into SQL Plan Directives closely so far. These are my notes columns for both directives: PERMANENTNO{ECJ(SEQDEV.GEN_PROTEIN_MAPPING)[PRL_ID]} (this one is ‘USABLE’). Here is it for the ‘SUPERSEDED’ record: HAS_STATSNO{EC(SEQDEV.GEN_PROTEIN_MAPPING)[PRL_ID]}
Both are not redundant. What’s the difference then?
Another question: I have a huge amount of directives for one very central table of our application (more than 1600) – most of them ‘USABLE’. The reason is that we have queries against the table with different column combination in the where-clause. Could this have a negative impact during SQL compilation? Do you have seen such an amount of directives for one table? Thanks, Reiner