Dynamic sampling in Oracle 12c – it goes up to 11. Really!

For the first time in many years, Oracle introduced a new option for dynamic sampling, and a major one for that – http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#PFGRF95292  .

The new dynamic sampling level 11 is the all-encompassing “automatic” switch, which leaves all decisions to Oracle.
The big question now is when would the new auto dynamic sampling kick-in? Maria Colgan (http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf)  indicates that the auto option would fire more frequently that the default.
To begin filling in the blanks, I started with a really simple test – retrieval by primary key.

Here are the create scripts:

create table tab3 as
with v1 as 
     (select rownum n from dual connect by level <= 10000)
select
     rownum id , 
     dbms_random.string('u',dbms_random.value*29 + 1) str
from
     v1, v1
where
     rownum < 1000000;

alter table tab3 add primary key (id)

execute dbms_stats.gather_table_stats('JORDAN','TAB3')

Now let see when happens when set the optimizer_dynamic_sampling =11 and issue the search by primary key:

SQL> alter session set optimizer_dynamic_sampling =11 ;

Session altered.

SQL> set autotrace on
SQL> select * from tab3 where id = 123 ;

…output truncated…

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
468 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

We see that that Oracle decided to do dynamic sampling. There is no guesswork in those types of queries. The cardinality of the statement is one, so dynamic sampling was not necessary.
But that’s not the worst part. The statement required 14 consistent gets and 15 recursive calls for a simple scan. Without dynamic sampling, the same statement requires 4 consistent gets and 1 recursive call.

SQL> select * from tab3 where id = 345 ;

…output truncated…

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
468 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The idea behind automatic dynamic sampling is excellent. Starting dynamic sampling only when the Oracle CBO cannot get reliable cardinalities would be a giant step towards query self-tuning.

The implementation of the feature in Oracle version 12.1.0.1, however, needs some more work. Hopefully, the logic would get more precise in the newer versions….

Advertisements

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

%d bloggers like this: