[Oracle-l] Dynamic Sampling: some questions about the guts

I wouold guess that this might be to do with the optimizer looking at different access paths to the table. For example, it might be not be much more selective to access the table via


"RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
AND "RCRAPP1"."RCRAPP1_INFC_CODE"=:bind2
AND "RCRAPP1"."RCRAPP1_CURR_REC_IND"=:bind3
AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4

than it is to access it with:


"RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4

If there is one index on all four columns and one index on just the two columns then accessing via the two column index might be more efficient than using the four-column one.


Just speculation though.

In one of our reporting environments (OSEE 10.2.0.2) we have optimizer_dynamic_sampling cranked up to 5. We have a case where dynamic sampling is pushing the CBO to choose a poor plan. Our situation highlights Level 4, specifically "tables that have single-table predicates that reference 2 or more columns". (from the documentation)

Looking at a 10046 trace, I see a OPT_DYN_SAMP query that counts a sample of rows that match variations on the predicate. I have found that a 10046 can always be good grounds for finding more questions than it answers. *grin*

To avoid the bandwidth, I will show just the relevant parts of the sampling query:
SELECT /* OPT_DYN_SAMP */

...
FROM

(SELECT /* ... */

1 AS C1,
CASE

WHEN "RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
AND "RCRAPP1"."RCRAPP1_INFC_CODE"=:bind2
AND "RCRAPP1"."RCRAPP1_CURR_REC_IND"=:bind3
AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4

THEN 1

ELSE 0

END AS C2,

CASE

WHEN "RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4

THEN 1

ELSE 0

END AS C3,

CASE

WHEN "RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4

THEN 1

ELSE 0

END AS C4

FROM ...

SAMPLE BLOCK (.037704, 1) SEED (1) "RCRAPP1"

) SAMPLESUB

/

First question: Why the predicate variations? I am assuming that sometimes C4 is different than C3, so I am going to ignore C4 for now.
2) How does the Optimizer use these sample counts to influence the costs and subsequent access/join methods?

I believe what is happening in our case is that sampling is "just unlucky" and it happens to come up with a inaccurate picture of how many rows match (an order of magnitude too many). I can prove this by using this query directly with different sample sizes.

I am hoping that by understanding dynamic sampling a little better, I can understand how to resolve this issue. I realize that one can add the dynamic_sampling query hint, and perhaps that is the "best" solution for this. Aside from adjusting dynamic sampling (whether it be at the system level or via a hint), is there any other way to address this situation?

TIA,

The interesting part is that if I calc stats (estimate_percent => null), the
CBO chooses an index for C3. We do not have an index for all 4 columns, but
under certain circumstances, the CBO will choose that other index for C2,
using three of the four columns in an access path, and CURR_REC_IND as a
filter. Those of you with astute eyes will notice the VPD column and
correctly assume we are using FGAC as well. That ends up being the most
optimal path, and I trying to understand why. Obviously, there is a whole
lot more information I could share, but first I wanted to understand how
dynamic sampling was playing a part in this.

Rajeev, to answer your questions:

1) Have tried with bucket 1, default and 254.
2) exact
3) Yes, all the time, every day. =) The parameters depend on what I am

trying to do. I like to calc stats in these cases to give the CBO as much
info as possible.

Not sure how those questions/answers help, but there you go.

Thanks all,

Charles

Unless you have already tried it, could you try the following
and let us know the results :

gathering stats with compute OR high sample (not AUTO).
use dbms_stats.set_column_stats for the relevant column.

-Rajeev

One more thing, can you try deleting histograms and give it a try.

Charles

Do you have histograms collected for relevant column(s) ?
What is the value of cursor_sharing parameter ?
Do you use dbms_stats ? If yes, what parameters/options
do you use ?

-Rajeev

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

SELECT /* OPT_DYN_SAMP */ ...

FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T")

NO_PARALLEL_INDEX("T") */

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.

Executed dynamic sampling query:
level : 5
sample pct. : 100.000000
actual sample size : 10000
filtered sample card. : 1000 -- Alberto: from C2
filtered sample card. (index T_Y_IDX): 1000 -- Alberto: from C3
filtered sample card. (index T_X_Y_IDX): 1000 -- Alberto: from C4
filtered sample card. (index T_X_IDX): 1000 -- Alberto: from C5
orig. card. : 82
block cnt. table stat. : 20
block cnt. for sampling: 20
max. sample block cnt. : 64
sample block cnt. : 20
min. sel. est. : 0.00000100
index T_Y_IDX selectivity est.: 0.10000000
index T_X_Y_IDX selectivity est.: 0.10000000
index T_X_IDX selectivity est.: 0.10000000

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

Dynamic sampling updated index stats.: T_X_Y_IDX, blocks=43

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 ...

HTH

Alberto