By Franck Pachot

.
If you have read Matching SQL Plan Directives and queries using it then you know how to use the ‘+metrics’ format of dbms_xplan.

21:49:54 SQL> explain plan for select distinct * from DEMO_TABLE
where a=0 and b=0 and c=0 and d=0;
Explained.
21:50:01 SQL> select *
from table(dbms_xplan.display(format=>'basic +rows +note +metrics'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3823449216

-------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT UNIQUE NOSORT| | 1 |
| 2 | TABLE ACCESS FULL| DEMO_TABLE | 500 |
-------------------------------------------------

Sql Plan Directive information:
-------------------------------
Used directive ids:
2183573658076085153
Note
-----

– dynamic statistics used: dynamic sampling (level=2)
– 1 Sql Plan Directive used for this statement
So, when I run this query with predicates on columns A,B,C,D I’m using the directive id 2183573658076085153. ‘Using’ means doing dynamic sampling in order to get good estimations, because the directive tells us that there is a misestimate when using only the static statistics. Then we can look at that directive:

21:50:11 SQL> select directive_id,state,created,last_modified,last_used
from dba_sql_plan_directives where directive_id=2183573658076085153;

DIRECTIVE_ID STATE CREATED LAST_MODIFIED LAST_USED
----------------------- ---------- -------- ------------- -------------
2183573658076085153 USABLE 21:41:50 21:41:55.0000 21:41:55.0000

Look at the timestamps. I’m at 21:50 and the directive which has been created 9 minutes ago at 21:41:50 has been used 5 seconds later at 21:45:55 and it’s the last usage.
Let’s run the query now. I know that it will use the directive:

21:50:40 SQL> select distinct * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0;

A B C D
---------- ---------- ---------- ----------
0 0 0 0

It I check to be sure that the directive has been used:

21:50:55 SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select distinct * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0

Plan hash value: 3823449216

-------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT UNIQUE NOSORT| | 1 |
| 2 | TABLE ACCESS FULL| DEMO_TABLE | 500 |
-------------------------------------------------

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

Yes: SPD used, we have dynamic sampling and accurate cardinality estimation.
The SPD has been used by the query that I’ve run at 21:50:40
So I’ll will check the LAST_USED timestamp, after being sure that what has been modified in memory is written to dictionary:

21:50:59 SQL> exec dbms_spd.flush_sql_plan_directive;
PL/SQL procedure successfully completed.

21:51:07 SQL> select directive_id,state,created,last_modified,last_used
from dba_sql_plan_directives where directive_id=2183573658076085153;

DIRECTIVE_ID STATE CREATED LAST_MODIFIED LAST_USED
----------------------- ---------- -------- ------------- -------------
2183573658076085153 USABLE 21:41:50 21:41:55.0000 21:41:55.0000
The LAST_USED has not been updated.

LAST_USED

This is not a bug. Updating the dictionary for all directives used during the previous 15 minutes (the automatic flush frequency) would be too expensive, so it is not done at each flush. The LAST_USED goal is to manage SPD retention – drop those that have not been used for a while (53 weeks by default) – and the retention is defined as weeks. So the LAST_USED is updated only if it is one week higher than the previous LAST_USED. Not exactly one week but 6.5 days.

6.5 days

In fact, when LAST_USED is updated, it adds 6.5 days to the current timestamp. We can see that in the underlying table:

SQL> alter session set nls_date_format='dd-mon-yyyy hh24_mi:ss';
 Session altered.
SQL> select type,state,created,last_modified,last_used from sys.opt_directive$ where dir_id=2183573658076085153;
 TYPE STATE CREATE LAST_MODIFI LAST_USED
 ---------- ---------- ------- ----------- --------------------
 1 3 19-may- 19-may-2015 26-may-2015 09:41:26

Last used as displayed by the DBA_SQL_PLAN_DIRECTIVES is 19-may-2015 at 21:41:26 but the internal table OPT_DIRECTIVE$ stores 26-may-2015 09:41:26 which is 6.5 days later. The view subtracts 6.5 days to it in order to expose it to us. But we must keep in mind that this timestamp will not be updated until 26-may-2015.
Conclusion: We cannot rely on LAST_USED when we want to know if a directive has been used recently. We must use explain plan for that. Note that this implementation is not new: it’s exactly the same as the LAST_EXECUTED column in DBA_SQL_PLAN_BASELINES. I’ts an optimization because tracking each usage would be too expensive.