Dynamic Sampling Myths Dispelled

There are couple of myths associated with Dynamic Sampling, I thought of eliminating them with the help of this post. Two myths associated with DS are:

i) Default number of blocks sampled are 32 in Oracle 10g and ii) Tables having less than 32 blocks are not considered by the optimizer for dynamic sampling.

Myth 1: Default number of blocks sampled are 32 in Oracle 10g

The default number of dynamic sampling blocks are 32. This is set using a hidden parameter “_optimizer_dyn_smp_blks” (ofcourse one should not change it). Here’s the query to see the default settings:

SQL> column "Parameter" format a30
SQL> column "Session Value" format a20
SQL> column "Instance Value" format a20
SQL>
SQL> select a.ksppinm  "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
  2    from x$ksppi a, x$ksppcv b, x$ksppsv c
  3   where a.indx = b.indx and a.indx = c.indx
  4     and ksppinm= '_optimizer_dyn_smp_blks';

Parameter                      Session Value        Instance Value
------------------------------ -------------------- --------------------
_optimizer_dyn_smp_blks        32                   32

Starting with Oracle 10g, by default “optimizer_dynamic_sampling” initialization parameter is set to “2”, meaning optimizer will dynamically sample all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks. So at this level, Optimizer will sample 64 blocks.

To verify how many blocks are sampled, we need to dig into the 10053 trace output. For the purpose of this test, I will create a test table and dump some data into it. Later enable 10053 trace, execute the query and disable the trace.

SQL> drop table test purge;

Table dropped.

SQL> create table test as select * from all_objects where 1=2;

Table created.

SQL> create index t_object_id on test(object_id);

Index created.

SQL> insert into test select * from all_objects;

35023 rows created.

SQL> commit;

Commit complete.

SQL> alter session set tracefile_identifier = 'blksize';

Session altered.

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> select * from test where object_id = 123;

no rows selected

SQL> alter session set events '10053 trace name context off';

Session altered.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Digging into the trace, we see the following:

** Executed dynamic sampling query:
    level : 2
    sample pct. : 12.701613
    actual sample size : 4097
    filtered sample card. : 0
    orig. card. : 82
    block cnt. table stat. : 496
    block cnt. for sampling: 496
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.01000000

Well, it’s clear for the trace that at level 2, the maximum number of blocks sampled (max. sample block cnt) are 64 and the actual number of blocks sampled (sample block cnt) are 63. (For some reason, Oracle always samples one block less)

So, this myth has been uncovered and we know how many blocks are sampled.

Myth 2: Tables having less than 32 blocks are not considered by the optimizer for dynamic sampling

It is widely understood that, tables having less than 32 blocks are ignored by the optimizer for dynamic sampling.

Let’s continue using the same table as in case 1. However, it contains data, so we will truncate it. After truncating, verify number of blocks allocated by querying USER_SEGMENTS view.

SQL> truncate table test;

Table truncated.

SQL> select blocks from user_segments where segment_name = 'TEST';

    BLOCKS
----------
         8

This table has only 8 blocks allocated. Let’s see whether optimizer considers dynamic sampling on this table by executing a simple query.

SQL> set autotrace traceonly exp
SQL> select * from test where object_id = 123;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   128 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |   128 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=123)

Note
-----
   - dynamic sampling used for this statement

SQL> set autotrace off
SQL>

Yes, Oracle optimizer did consider dynamic sampling although the number of blocks were less than 32.

So, in this post we were able to uncover myths associated with dynamic sampling through very simple and reproducible examples.