DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
        b=YRk2PXBVHZKi0Wu8aXlzHflsU8IIHSeEDSz3ZFLVTJdnD0YaxCS8MRKmuZvwC7Oa/AgPa0Gp7X0UOJJjkZvLva9SNkcPlHH3P0xN4C1lT+v9Z4aM4ThOFbyNjAXsYlb8TBDTDmk94xmQjM7rK4/9zJCLTsD4cjhs7EeV46Ibqc8=
Message-ID: <2ba656800712011629v8c6a7d8sfd22bf4e8b34f6e2@mail.gmail.com>
Date: Sat, 1 Dec 2007 19:29:29 -0500
From: "Rajeev Prabhakar" <rprabha01@gmail.com>
To: sacrophyte@gmail.com
Subject: Re: Dynamic Sampling: some questions about the guts
Cc: oracle-l <oracle-l@freelists.org>
In-Reply-To: <7b8774110712011434m2c151525je3187b59b0e6162e@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_694_28502140.1196555369055"
References: <7b8774110711301245w1507d405t61c3ce3a29cc4520@mail.gmail.com>
	 <209004.44232.qm@web807.biz.mail.mud.yahoo.com>
	 <7b8774110712011434m2c151525je3187b59b0e6162e@mail.gmail.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: rprabha01@gmail.com
------=_Part_694_28502140.1196555369055
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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@gmail.com> 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

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

<div>Charles</div>
<div>&nbsp;</div>
<div>Unless you have already tried it, could you try the following&nbsp; </div>
<div>and let us know the results :</div>
<div>&nbsp;</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>&nbsp;</div>
<div>-Rajeev<br><br></div>
<div class="gmail_quote">On Dec 1, 2007 5:34 PM, Charles Schultz &lt;<a href="mailto:sacrophyte@gmail.com">sacrophyte@gmail.com</a>&gt; 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>&nbsp;</div>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><font color="#888888">Charles Schultz </font></blockquote></div><br>

------=_Part_694_28502140.1196555369055--
--
http://www.freelists.org/webpage/oracle-l