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: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 26 Jan 2007 15:39:39 +0100
Message-ID: <51ui4vF1macebU1@mid.individual.net>


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.
> Thanks

Hm... 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 Received on Fri Jan 26 2007 - 08:39:39 CST

Original text of this message

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