Dynamic Sampling: some questions about the guts 2007-12-01 - By Charles Schultz
Back
On Dec 1, 2007 4:05 PM, David Aldridge <david@(protected)> 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
<br><br><div class="gmail_quote">On Dec 1, 2007 4:05 PM, David Aldridge <<a
href="mailto:david@(protected)">david@(protected)</a>> wrote:
<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204,
204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>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 </div><div class="Ih2E3d"> <div>
</div> <div><font color="#333399"><font face="Courier New">"RCRAPP1
"."RCRAPP1_AIDY_CODE"=:bind1 <br style="color: rgb(51, 51,
153); font-family: courier new,monospace;"><span style="color: rgb(51, 51, 153)
; font-family: courier new,monospace;">
AND "RCRAPP1"."RCRAPP1_INFC_CODE"=:bind2 </span><
/font></font><br style="color: rgb(51, 51, 153); font-family: courier new
,monospace;"><span style="color: rgb(51, 51, 153); font-family: courier new
,monospace;">
AND "RCRAPP1"."RCRAPP1_CURR_REC_IND"=:bind3 </span><br
style="color: rgb(51, 51, 153); font-family: courier new,monospace;"><span
style="color: rgb(51, 51, 153); font-family: courier new,monospace;"> AND "
;RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4
</span><br style="color: rgb(51, 51, 153); font-family: courier new,monospace;"
></div></div>
<div>than it is to access it with:</div> <div> </div> <div><div class=
"Ih2E3d"><font color="#333399"><font face="Courier New">"RCRAPP1".
"RCRAPP1_AIDY_CODE"=:bind1<br style="color: rgb(51, 51, 153); font
-family: courier new,monospace;">
<span style="color: rgb(51, 51, 153); font-family: courier new,monospace;">AND
"RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4</span></font></font><br
style="color: rgb(51, 51, 153); font-family: courier new,monospace;">
<br></div>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.</div> <div> </div> <div>Just speculation
though.
</div></blockquote></div><br>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>Charles Schultz
|
|
