Adaptive Dynamic Sampling : DYNAMIC_SAMPLING_RESULT & STALE_PERCENT

Since oracle 12.2.0.1 Adaptive Dynamic Sampling result are stored inside SPD as a special directive type “DYNAMIC_SAMPLING_RESULT”. This will allow the result of ADS to be persisted on disk,thus it will survive memory flush and database restart.For more detailed info please check Mauro Pagano blog Post Something new about SQL Plan Directives and 12.2

We may ask at this moment is there some sanity check that will trigger the refresh of this ADS result stored as sql plan directive when the table data is marked stale (STALE_PERCENT) ?

Let’s do a quick test : Oracle 12.2.0.1/OEL6/UEK4

I will use the same example as in this blog post dynamic-sampling-just-got-better


alter session set optimizer_dynamic_sampling = 11;
alter session set events 'trace[RDBMS.SQL_DS] disk=high';
ALTER SESSION SET EVENTS  '10053 trace name context forever, level 1';
select       count(*)   from t1,t2 where t1.n1 = t2.n1 and t1.n2 > t2.n2;
ALTER SESSION SET EVENTS  '10053 trace name context off';
alter session set events 'trace[RDBMS.SQL_DS] off';

Without ADS :

With ADS :

The join cardinality is now very accurate !

The ADS result is stored as SPD :

Let’s now modify 10% of table T2 data and check if the ADS stored result will be reused (the query is modified a little bit to be hard parsed):

It’s seem that the same ADS result was used let’s verify :

Let’s Modify one more row :

The table stats is now stale (more than 10% / Default STALE_PERCENT)

Let’s re-execute our query :

It’s seem that ADS kicked in again (The join cardinality is now very accurate) let’s verify :

We can see that the Dynamic sampling result directive is marked stale and it has been updated :

See the “LAST_MODIFIED” column !

This was a quick example to demonstrate that tables with stale stats (more than STALE_PERCENT) will trigger the refresh of the ADS result stored as sql plan directive !

The same test done in Oracle 12.1.0.2.6 demonstrated that the ADS result stored in the result cache will not be invalidated as in this example.So basically we will have to wait for 1h (snapshot=3600) or until the result is flushed from memory.

That’s it 😀

Note : The screens capture have been taken at different moment of testing so you may found different directive_id on them (sorry i’am feeling lazy :p)