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: Anurag Varma <avoracle_at_gmail.com>
Date: 26 Jan 2007 11:08:36 -0800
Message-ID: <1169838516.859922.144030@s48g2000cws.googlegroups.com>

On Jan 26, 1:36 pm, "Ben" <bal..._at_comcast.net> wrote:
> 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?

There are a number of factors that would influence why oracle would revert
to FTS rather than index scan.
Read this as a starter:
http://www.dbazine.com/oracle/or-articles/jlewis12 I'm pretty sure your question is answered in detail in Jonathan Lewis's

latest book on CBO.

See the following example:

    MIN(A) MAX(A)
---------- ----------

         1 2000

LOW_VALUE HIGH_VALUE

-------------------- --------------------
C102                 C215

ORA92> var v number
ORA92> exec dbms_stats.convert_raw_value('C102',:v)

PL/SQL procedure successfully completed.

ORA92> print v

         V


         1

ORA92> exec dbms_stats.convert_raw_value('C215',:v)

PL/SQL procedure successfully completed.

ORA92> print v

         V


      2000

.. as you can see, oracle has it recorded that low/high values are 1 and 2000.

Now some tests:
ORA92> set autotrace traceonly exp
ORA92> select * from test98 where a >= 2005;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=165)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST98' (Cost=3 Card=1 Bytes=165)

   2 1 INDEX (RANGE SCAN) OF 'TEST98_PK' (UNIQUE) (Cost=2 Card=1)

... uses PK. Since literal > the high_value, oracle calculates that it will return only 1 row
at max.

ORA92> select * from test98 where a >= 1900;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=101 Bytes=16665)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST98' (Cost=5 Card=101 Bytes=16665)

   2 1 INDEX (RANGE SCAN) OF 'TEST98_PK' (UNIQUE) (Cost=2 Card=101)

... Similarly here, it calculates that ~101 rows will be returned (card=101).

ORA92> select * from test98 where a >= 1700;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=301 Bytes=49665)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST98' (Cost=10 Card=301 Bytes=49665)

   2 1 INDEX (RANGE SCAN) OF 'TEST98_PK' (UNIQUE) (Cost=2 Card=301)

... still doing Index range scan...

ORA92> select * from test98 where a >= 1600;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=401 Bytes=66165)

   1 0 TABLE ACCESS (FULL) OF 'TEST98' (Cost=11 Card=401 Bytes=66165)

... reverted to FTS.

For above example, no system stats were gathered and optimizer values set to default (oic=0, oica=100) and mbrc=8

Anurag Received on Fri Jan 26 2007 - 13:08:36 CST

Original text of this message

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