Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Unique index not being used, up to date stats

Re: Unique index not being used, up to date stats

From: Ben <balvey_at_comcast.net>
Date: 26 Jan 2007 10:36:55 -0800
Message-ID: <1169836615.735803.41080@s48g2000cws.googlegroups.com>

On Jan 26, 1:15 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> On Jan 26, 9:27 am, "Ben" <bal..._at_comcast.net> wrote:
>
>
>
>
>
> > 9.2.0.5 Ent Ed, AIX5L
>
> --snip--
> > Here's the sql that gets ran when the user clicks find, this results in
> > a full scan even though wadoco is the primary key.
>
> > SELECT wadcto, wadoco, wasfxo, warcto, warorn, walnid, wapars, waprts,
> > wadl01,
> > wammcu, walocn, wasrst, waan8, waansa, waanpa, watrdj, wastrt,
> > wadrqj,
> > wawr01, wawr02, wawr03, wavr01, waitm, waaitm, walitm, wauorg,
> > wasocn,
> > wasoqs, wauom, walotn, warkco, waurdt
> > FROM proddta.f4801
> > WHERE wadoco >= :key1
> > ORDER BY wadoco ASC
>
> > When I run an explain plan on that sql this is what I get, and is what
> > it should be doing.
>
> > SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> > ---------------------------------------------------------------------------
> > | Id | Operation | Name | Rows | Bytes |
> > Cost |
> > ---------------------------------------------------------------------------
> > | 0 | SELECT STATEMENT | | 24652 | 6813K|
> > 672 |
> > | 1 | TABLE ACCESS BY INDEX ROWID| F4801 | 24652 | 6813K|
> > 672 |
> > | 2 | INDEX RANGE SCAN | F4801_0 | 4437 | |
> > 18 |
> > ---------------------------------------------------------------------------
>
> > Note: cpu costing is off, PLAN_TABLE' is old version
>
> > 10 rows selected.
>
> > Now if I take the bind variable out and run explain plan, it reverts
> > back to fts. That particular value is toward the upper end of values so
> > the resulting data set should be less than 10% of the data.
> --snip--
> > It is saying that the cost of using that index is much more than it
> > actually is. were talking almost instant as opposed to 5 mins.
> > I don't think it is a data skew problem, as it's a unique column. I
> > don't think it's a casting issue either ( I could be totally wrong ) as
> > when I enter the number instead of the bind variable is when i'm
> > getting the problem.
> > It's a little confusing why it's doing the FTS with the bind variable,
> > even though when I run explain plan on it, it shows the index being
> > used. But I'm pretty sure I read where CBO 9.2 and > does ( bind
> > variable peeking? ) where it knows the value of the variable before it
> > decides what execution path to take. I know some of you genusis on here
> > can point me to the right direction as you have in the past.
> > I've search asktom and I'm sure this has been addressed but I guess I'm
> > not entering the right keywords to find anything on Tom's site. I
> > haven't found anything on here either that addresses this, but then
> > again I'm probably not using the correct keywords.
> > ThanksBen,
>
> You seem to have pretty much figured this out yourself..
> I think you are assuming that the bind variable peeking
> happens *everytime*. If that were true then the performance
> implications of this would have been bad.
> In reality bind variable peeking happens ONCE, the first
> time you parse/execute that statement.
> It so happened that the first time that sql was called using
> bind variable, it might have been using a value which
> caused a FTS. Subsequently, the later SQL's are now using the
> same FTS in the execution plan.
>
> Explain plan however does *NOT* consider bind variable peeking.
> So when you run an explain plan, its showing you an index scan
> because its assuming a particular selectivity.
>
> Also, when you use literals, the index scan or FTS happens based on
> where
> the value lies as compared to the LOW HIGH values that were
> calculated for the table column when the table was analyzed.
>
> Anurag- Hide quoted text -- Show quoted text -

Anurag,
That's good to know, I was assuming that it *peeked* everytime. But that still doesn't explain why, when I use a literal that is in the upper 10% to 5% of the data set it still performs the FTS. I would accept that if I didn't try it with a literal. In one of the messages above I gave the numbers of records that were above the value that I'm using. Since the analyze this weekend, there have only been 5000 records added to the 493000, that's not enough to throw a wrench in the plans already, is it?
If the wadoco column didn't have a value as high as the value that I'm using when it was analyzed, how would that be treated? Received on Fri Jan 26 2007 - 12:36:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US