Dynamic Sampling: some questions about the guts 2007-12-01 - By Rajeev Prabhakar
Back
One more thing, can you try deleting histograms and give it a try.
On Dec 1, 2007 7:29 PM, Rajeev Prabhakar <rprabha01@(protected)> wrote:
> 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>One more thing, can you try deleting histograms and give it a try.</div>
<div><br> </div>
<div class="gmail_quote">On Dec 1, 2007 7:29 PM, Rajeev Prabhakar <<a href=
"mailto:rprabha01@(protected)">rprabha01@(protected)</a>> wrote:<br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<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><font
color="#888888">
<div> </div>
<div>-Rajeev<br><br></div></font>
<div>
<div></div>
<div class="Wj3C7c">
<div class="gmail_quote">On Dec 1, 2007 5:34 PM, Charles Schultz <<a href=
"mailto:sacrophyte@(protected)" target="_blank">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><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></div></div></blockquote></div>
<br>
|
|
