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 11:11:45 -0800
Message-ID: <1169838705.499247.155730@s48g2000cws.googlegroups.com>

On Jan 26, 9:27 am, "Ben" <bal..._at_comcast.net> wrote:
> 9.2.0.5 Ent Ed, AIX5L
>
> Got a call about a screen in our ERP started taking 5 mins when it used
> to take 3 secs to load. I check the stats and it was analyzed this past
> weekend, count the rows and they are only off by 5000 compared to
> dba_tables.numrows out of 493000. So the stats are close enough but
> when I execute the screen and check out what is happening. I see that
> it is doing a FTS and should be using the primary key.
>
> 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.
>
> SQL> explain plan for
> 2 SELECT wadcto, wadoco, wasfxo, warcto, warorn, walnid, wapars,
> waprts, wadl01,
> 3 wammcu, walocn, wasrst, waan8, waansa, waanpa, watrdj,
> wastrt, wadrqj,
> 4 wawr01, wawr02, wawr03, wavr01, waitm, waaitm, walitm,
> wauorg, wasocn,
> 5 wasoqs, wauom, walotn, warkco, waurdt
> 6 FROM proddta.f4801
> 7 where wadoco >= 11723420
> 8 order by wadoco asc;
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> ---------------------------------------------------------------------------­-
> | Id | Operation | Name | Rows | Bytes |TempSpc|
> Cost |
> ---------------------------------------------------------------------------­-
> | 0 | SELECT STATEMENT | | 216K| 58M| |
> 29309 |
> | 1 | SORT ORDER BY | | 216K| 58M| 147M|
> 29309 |
> | 2 | TABLE ACCESS FULL | F4801 | 216K| 58M| |
> 9648 |
> ---------------------------------------------------------------------------­-
>
> Note: cpu costing is off, PLAN_TABLE' is old version
>
> 10 rows selected.
>
> If I hint it to use the index without the bind variable this is what I
> get.
>
> SQL> explain plan for
> 2 select /*+ INDEX(F4801 F4801_0) */
> 3 wadcto, wadoco, wasfxo, warcto, warorn, walnid, wapars, waprts,
> wadl01,
> 4 wammcu, walocn, wasrst, waan8, waansa, waanpa, watrdj, wastrt,
> wadrqj,
> 5 wawr01, wawr02, wawr03, wavr01, waitm, waaitm, walitm, wauorg,
> wasocn,
> 6 wasoqs, wauom, walotn, warkco, waurdt
> 7 FROM proddta.f4801
> 8 where wadoco >= 11723420;
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 216K| 58M|
> 32699 |
> | 1 | TABLE ACCESS BY INDEX ROWID| F4801 | 216K| 58M|
> 32699 |
> | 2 | INDEX RANGE SCAN | F4801_0 | 216K| |
> 763 |
> ---------------------------------------------------------------------------
>
> 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.
> Thanks

I'm just aggrevated now. I imported the stats that were in place last week and that fixed the problem. The new stats were fairly accurate ( only 5000 records off, out of 493000 ) and the statistics weren't gathered in a different way than what the old stats were gathered.

What now? I want to find out why this happened, I also don't want to run into this everytime stats are gathered, I haven't used outlines before so it would take some time for me to learn how to use them. How are outlines handled as your tables grow? What if one becomes worse than the path the CBO prefers now? Received on Fri Jan 26 2007 - 13:11:45 CST

Original text of this message

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