DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references;
        b=SkcUkGRJUH2LoGEfkwtr9NR0mHhYUkMHZPxNNC/vPr0vxfDAiBcgVYUaiebyNLk5BEeOuzwq51G0CVWHyZArUTj0f7JmxJMLXPVDTWoJ+A4aCEbn/ktuV4fr5Wikpyis5eOULFKQJoL+PQG3IGDoXzsdym0SZ4apiYYVqcg+Eyw=
Message-ID: <7b8774110712011434m2c151525je3187b59b0e6162e@mail.gmail.com>
Date: Sat, 1 Dec 2007 16:34:19 -0600
From: "Charles Schultz" <sacrophyte@gmail.com>
To: "David Aldridge" <david@david-aldridge.com>
Subject: Re: Dynamic Sampling: some questions about the guts
Cc: oracle-l <oracle-l@freelists.org>
In-Reply-To: <209004.44232.qm@web807.biz.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_2983_15725975.1196548459596"
References: <7b8774110711301245w1507d405t61c3ce3a29cc4520@mail.gmail.com>
	 <209004.44232.qm@web807.biz.mail.mud.yahoo.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: sacrophyte@gmail.com
------=_Part_2983_15725975.1196548459596
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Dec 1, 2007 4:05 PM, David Aldridge <david@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

------=_Part_2983_15725975.1196548459596
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br><br><div class="gmail_quote">On Dec 1, 2007 4:05 PM, David Aldridge &lt;<a href="mailto:david@david-aldridge.com">david@david-aldridge.com</a>&gt; 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>
&nbsp;</div>  <div><font color="#333399"><font face="Courier New">&quot;RCRAPP1&quot;.&quot;RCRAPP1_AIDY_CODE&quot;=:bind1&nbsp;<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 &quot;RCRAPP1&quot;.&quot;RCRAPP1_INFC_CODE&quot;=:bind2&nbsp;</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 &quot;RCRAPP1&quot;.&quot;RCRAPP1_CURR_REC_IND&quot;=:bind3&nbsp;</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 &quot;RCRAPP1&quot;.&quot;RCRAPP1_VPDI_CODE&quot;=: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>&nbsp;</div>  <div><div class="Ih2E3d"><font color="#333399"><font face="Courier New">&quot;RCRAPP1&quot;.&quot;RCRAPP1_AIDY_CODE&quot;=: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 &quot;RCRAPP1&quot;.&quot;RCRAPP1_VPDI_CODE&quot;=: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>&nbsp;</div>  <div>Just speculation though.
</div></blockquote></div><br>The interesting part is that if I calc stats (estimate_percent =&gt; 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

------=_Part_2983_15725975.1196548459596--
--
http://www.freelists.org/webpage/oracle-l