Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Dynamic Sampling: some questions about the guts

Re: Dynamic Sampling: some questions about the guts

From: Alberto Dell'Era <>
Date: Sun, 2 Dec 2007 02:18:13 +0100
Message-ID: <>

> 2) How does the Optimizer use these sample counts to influence the costs and
> subsequent access/join methods?

If you try this scenario, and collect a 10053 trace:

exec dbms_random.seed(0);

create table t (x int, y int, z int);

create index t_x_idx on t(x);
create index t_y_idx on t(y);
create index t_x_y_idx on t(x,y);

insert into t (x,y,z)
select mod(rownum-1,10) x, mod(rownum-1,10) y, mod(rownum-1,10) z from dual connect by level <= 10000
order by dbms_random.random;

alter session set optimizer_dynamic_sampling=5; -- just to have pretty round numbers:
alter session set "_optimizer_cost_model"=io; alter session set events '10053 trace name context forever, level 1'; set autotrace traceonly explain
select /*+ dynamic_sampling */ * from t where x = 1 and y = 1 and z = 1; set autotrace off
alter session set events '10053 trace name context off';

The sampling query (reformatted for clarity) is, in


1                                                                  AS C1,
CASE WHEN "T"."X"=1 AND "T"."Y"=1 AND "T"."Z"=1  THEN 1 ELSE 0 END AS C2,
CASE WHEN "T"."Y"=1                              THEN 1 ELSE 0 END AS C3,
CASE WHEN "T"."Y"=1 AND "T"."X"=1                THEN 1 ELSE 0 END AS C4,
CASE WHEN "T"."X"=1                              THEN 1 ELSE 0 END AS C5

C1 estimates num_rows, C2 the query filtered cardinality, and the others how much index keys are going to be visited if the index is chosen, and hence, the index selectivity - for each index eligible to be used.

Of course the numbers returned by the query are in general corrected by the sampling factor - if you estimated by 1%, they are multiplied by 100. Here the sampling was 100%.

The numbers are then plugged into the usual formulae; for example, the estimated cost of the index access for T_X_Y_IDX is

  Access Path: index (AllEqRange)
    Index: T_X_Y_IDX
    resc_io: 85.00 resc_cpu: 0
    ix_sel: 0.1 ix_sel_with_filters: 0.1     Cost: 85.00 Resp: 85.00 Degree: 1

From Jonathan's "Cost Based Oracle" or Wolfgang's famous paper, we get that the usual formula is
blevel + ix_sel * leaf_blocks + ix_sel_with_filters * clustering_factor

that is "blevel blocks are visited going from the root to the leaves, ix_sel * leaf_blocks are the leaf block got (visited, range scan), and ix_sel_with_filters * clustering_factor are the table blocks got".

For this scenario, ix_sel = ix_sel_with_filters, since all index columns are used for access, none for (pre)filtering before following the rowid.

blevel and clustering_factor are taken from the index stats (here clustering_factor is defaulted to 800):

  Index: T_X_Y_IDX Col#: 1 2 (NOT ANALYZED)     LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00 The leaf_blocks is AFAIK taken from the index segment header and set equal to the number of index blocks (in an index, almost all blocks are leaves):

so 1 + 0.1 * 43 + 0.1 * 800 = 85.3

Notice that the clustering factor is not dynamically estimated, and since it is very frequently the major contributor, it is well worth to have the index stats in place.

Caveat: this is how I understand it, I haven't made any exhaustive investigation. In fact most of this test case comes from my fuzzy recalls of a discussion I had with Wolfgang eons ago. It should be enough to answer much of your question anyway ...


Alberto Dell'Era
"the more you know, the faster you go"
Received on Sat Dec 01 2007 - 19:18:13 CST

Original text of this message