Re: Dynamic Sampling: some questions about the guts

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Sat, 1 Dec 2007 16:34:19 -0600
Message-ID: <7b8774110712011434m2c151525je3187b59b0e6162e@mail.gmail.com>

On Dec 1, 2007 4:05 PM, David Aldridge <david_at_david-aldridge.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Dec 01 2007 - 16:34:19 CST