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

On Jan 26, 2:11 pm, "Ben" <bal..._at_comcast.net> wrote:
> 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.
> > ThanksI'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?- Hide quoted text -- Show quoted text -

I checked the datablocks/key, leafblocks/key, and clustering factor. None of these look out of line. The table is 63,591 blocks, 495000 rows, with 6647 blocks on the free list. The db/key = 1, lb/key =1, and cf = 71999. Could the free list blocks be causing a problem? This table is archived monthly, and I doubt that it was ever resized after we started archiving it, hence the free list blocks. I'm at a loss here, and don't really know what to try next. Received on Mon Jan 29 2007 - 14:28:05 CST