Dynamic Sampling

If you read the manual pages about “dynamic sampling” it’s easy to get just a little lost in the detail; so this is a brief overview of the variations in the strategies used.

You can set the parameter optimizer_dynamic_sampling to a level between 0 (no sample) and 10 (“sample” whole table).

You can use the dynamic_sampling() hint in two different ways in an SQL statement.

• If you use the hint in the “cursor-level” form: /*+ dynamic_sampling ({level}) */ e.g. /*+ dynamic_sampling(4) */, this is equivalent to setting the parameter optimizer_dynamic_sampling to that level for the duration of that query.
• If you use the hint in the “table-level” form /*+ dynamic_sampling({alias} {level}) */ e.g. /*+ dynamic_sampling(ord 4) */ you are directing Oracle to sample a specific table unconditionally. In this form, the sample size for levels 1 to 9 is 2^level-1 * “basic sample size” (which is set to 32 blocks by default by parameter _optimizer_dyn_smp_blks.)  You can have multiple hints of this form in a single query, one for each table you want sampled.

If you’ve used the parameter setting or the “cursor-level” setting, then there are rules associated with levels 1 to 4 that the optimizer applies to each table in the query to decide whether or not to sample that table. Level 3 attempts to address the problems of “guesses” such as the effects of functions applied to columns, and level 4 attempts to deal with the “dependent predicates” problem; the sample size for these early levels will be 32 or 64 blocks (depending on the parameter _optimizer_dyn_smp_blks and the presence, or absence, of existing stats on the table being tested). From level 4 onwards, the sample size tends to multiply by a factor of 4 for each level, until the sample sizes for the cursor-level and table-level versions of the hints are the same.

Follow-up:

The morning after I wrote this note a related question appeared on the OTN database forum asking why Oracle hadn’t done dynamic sampling in a particular case.  I’ve modelled the example with the code below, running on 10.2.0.3, locally managed tablespace, 1MB uniform extent size, 8KB block size:

rem
rem     Script: dynamic_sampling_blog.sql
rem     Author: J P.Lewis
rem     Dated:  Feb 2010
rem

drop table t1;

create table t1
as
select	*
from	all_objects
where   rownum  comment to avoid WordPress format mess
;

begin
        dbms_stats.gather_table_stats(
                user, 't1',method_opt => 'for all columns size 1'
        );
end;
/

select
	blocks, num_rows, sample_size
from
	user_tables
where
	table_name = 'T1'
;

delete from t1 where mod(object_id,10) != 0;
commit;

set autotrace on explain

select count(*) from t1;

select /*+ dynamic_sampling(t1 2) */ count(*) from t1;

set autotrace off

It’s a very simple query, on a very simple structure, and it uses autotrace in an attempt to make the point about dynamic sampling. Here’s a cut-n-paste of the output (starting just after the stats collection):

    BLOCKS   NUM_ROWS SAMPLE_SIZE
---------- ---------- -----------
       128      10000       10000

1 row selected.

9002 rows deleted.

Commit complete.

  COUNT(*)
----------
       998

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    36   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    36   (0)| 00:00:01 |
-------------------------------------------------------------------

  COUNT(*)
----------
       998

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    36   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    36   (0)| 00:00:01 |
-------------------------------------------------------------------

We can see that we have statistics.
We can see that we delete 9002 rows
We can see that we have 998 rows left
We can see that the plan (and especially the cardinality of the full tablescan) doesn’t change even though we included a table-level hint to do dynamic sampling.
Moreoever – we can’t see the usual note that you get when the plan is dependent on a dynamic sample (” – dynamic sampling used for this statement”).

It looks as if dynamic sampling hasn’t happened.

However, I “know” that dynamic sampling is supposed to happen unconditionally when you use a table-level hint – so I’m not going to stop at this point. There are cases where you just have to move on from explain plan (or autotrace) and look at the 10053 trace. This is what I found (with a few line-wraps added) when I enabled the 10053 trace for the hinted query:

SINGLE TABLE ACCESS PATH
*** 2010-02-24 10:14:04.671
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
*** 2010-02-24 10:14:04.671
** Generated dynamic sampling query:
    query text :
SELECT /* OPT_DYN_SAMP */
/*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (
SELECT /*+ NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */ 1 AS C1, 1 AS C2
FROM "T1" SAMPLE BLOCK (49.218750 , 1) SEED (1) "T1") SAMPLESUB
*** 2010-02-24 10:14:04.671
** Executed dynamic sampling query:
    level : 2
    sample pct. : 49.218750
    actual sample size : 547
    filtered sample card. : 547
    orig. card. : 10000
    block cnt. table stat. : 128
    block cnt. for sampling: 128
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : -1.00000000
** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : Current SQL statement for this session:
select /*+ dynamic_sampling(t1 2) */ count(*) from t1

So the optimizer did do dynamic sampling, but then decided that it wasn’t going to use the results for this query.

Footnote: I pointed out that for the lower levels of sampling Oracle will sample 32 or 64 blocks. For reasons relating (I think) to rounding errors and the “sample block (percentage)” strategy, the actual sample often seems to fall short by one block.

Update:

The most recent versions of Oracle allow you to specify level 11 for dynamic sampling – a level which allows the optimizer to decide for itself how much sampling to do. The manuals for the most recent versions also seem to introduce a few errors relating to the effect of the hints and the number of blocks sampled.