FreeLists / oracle-l / Re: Dynamic Sampling: some questions about the guts

["Charles Schultz" <sacrophyte@xxxxxxxxx>]

On Dec 1, 2007 4:05 PM, David Aldridge <david@xxxxxxxxxxxxxxxxxx> wrote:

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

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 Schultz