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 :

Capture 01

With ADS :

The join cardinality is now very accurate !

Capture 02

The ADS result is stored as SPD :

Capture 13

Capture 03

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):

Capture 04

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

capture 05

Let’s Modify one more row :

Capture 06

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

Capture 07

Let’s re-execute our query :

Capture 08

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

Capture 09

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

Capture 10

Capture 11

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.

Capture 12

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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s