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 07:02:07 -0800
Message-ID: <1169823727.443383.229810@a75g2000cwd.googlegroups.com>

On Jan 26, 9:39 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 26.01.2007 15:27, Ben 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.
> > ThanksHm... Did you also verify that stats of all indexes on that table are
> current? Did you create stats with DBMS_STATS or with ANALYZE? If you
> used ANALYZE I suggest to redo with DBMS_STATS.GATHER_TABLE_STATS with
> cascade => TRUE in order to update index statistics.
>
> Others will probably have much better suggestions...
>
> Kind regards
>
> robert- Hide quoted text -- Show quoted text -

Yeah, I use
dbms_stats.gather_schema_stats(
ownname => 'PRODDTA',
cascade => true,
degree => 6,
options => 'GATHER STALE');

This runs every weekend, and the table in question & indexes were analyzed this past weekend. Received on Fri Jan 26 2007 - 09:02:07 CST

Original text of this message

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