Dynamic Sampling: some questions about the guts 2007-12-01 - By Rajeev Prabhakar
Back
Charles
Unless you have already tried it, could you try the following
and let us know the results :
a) gathering stats with compute OR high sample (not AUTO).
b) use dbms_stats.set_column_stats for the relevant column.
-Rajeev
On Dec 1, 2007 5:34 PM, Charles Schultz <sacrophyte@(protected)> wrote:
>
>
> 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
<div>Charles</div>
<div> </div>
<div>Unless you have already tried it, could you try the following </div>
<div>and let us know the results :</div>
<div> </div>
<div>a) gathering stats with compute OR high sample (not AUTO).</div>
<div>b) use dbms_stats.set_column_stats for the relevant column.</div>
<div> </div>
<div>-Rajeev<br><br></div>
<div class="gmail_quote">On Dec 1, 2007 5:34 PM, Charles Schultz <<a href=
"mailto:sacrophyte@(protected)">sacrophyte@(protected)</a>> wrote:<br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<div class="Ih2E3d"><br> </div>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.
<br><br><br>Rajeev, to answer your questions:<br>1) Have tried with bucket 1,
default and 254.<br>2) exact<br>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.
<br><br>Not sure how those questions/answers help, but there you go.<br><br><br
>Thanks all,<br><br clear="all"><br>-- <br><font color="#888888">Charles Schultz
</font></blockquote></div><br>
|
|
