By Franck Pachot

.
This is another blog post I’m writing while reviewing the presentation I’m doing next week forSOUG and next month for DOAG. You have SQL Plan Directives used by several queries that have same kind of predicates. And queries that can use several SPD. Can you match them?

When a query uses a SPD (meaning that the SPD in usable state – NEW, MISSING_STATS or PERMANENT internal state) the execution plan show it as:

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

but you don’t have information about which directive(s).

Unfortunately that information is not stored in V$SQL_PLAN information. There are two ways to get information:

  • Parse it and trace it with set events ‘trace [SQL_Plan_Directive.*]’ but that’s for another post.
  • Do an EXPLAIN PLAN and info is in PLAN_TABLE.OTHER_XML

example

Here are the SQL Plan Directives I have:

SQL> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_d
irectives where directive_id in( select directive_id from dba_sql_plan_dir_objects where owner='DEMO
' ) order by created;

           DIRECTIVE_ID TYPE             STATE      REASON
----------------------- ---------------- ---------- ------------------------------------
NOTES                                                                                      CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
   11092019653200552215 DYNAMIC_SAMPLING SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE
.spd_note.                                                                                 21:21:58
  .internal_state.HAS_STATS./internal_state.
  .redundant.NO./redundant.
  .spd_text.{EC(DEMO.DEMO_TABLE)[A, B, C, D]}./spd_text.
./spd_note.
21:30:09 21:30:09

    9695481911885124390 DYNAMIC_SAMPLING USABLE     SINGLE TABLE CARDINALITY MISESTIMATE
.spd_note.                                                                                 21:35:45
  .internal_state.NEW./internal_state.
  .redundant.NO./redundant.
  .spd_text.{E(DEMO.DEMO_TABLE)[A, B, C, D]}./spd_text.
./spd_note.

(I changed the xml tag because our current blog platform is a bit creative with them… fortunately we are migrating soon to wordpress)

+metrics

So in order to have more information, you have to re-parse the statement with EXPLAIN PLAN FOR… and show it with DBMS_XPLAN.DISPLAY witht he format ‘+METRICS’

SQL> explain plan for select * from DEMO_TABLE where a+b=c+d;

Explained.

This query will use the {E(DEMO.DEMO_TABLE)[A, B, C, D]} directive but not the {EC(DEMO.DEMO_TABLE)[A, B, C, D]} one because it’s not simple columns predicates.
Let’s get the execution plan from PLAN_TABLE with the +METRICS format:

SQL> select * from table(dbms_xplan.display(null,null,'+metrics'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4063024151

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1000 | 12000 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DEMO_TABLE |  1000 | 12000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"+"B"="C"+"D")

Sql Plan Directive information:
-------------------------------

  Used directive ids:
    9695481911885124390

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

As you can see, in addition to the number of SPD used you have the DIRECTIVE ID.

Conclusion

It’s not easy to match all queries that can use a SQL Plan Directive, but you can do it on the other way: do an explain plan for each query you suspect and check the notes. If you are ready to parse a lot of queries, you can also do it automatically.