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 -> Unique index not being used, up to date stats

Unique index not being used, up to date stats

From: Ben <balvey_at_comcast.net>
Date: 26 Jan 2007 06:27:30 -0800
Message-ID: <1169821650.333875.88740@v33g2000cwv.googlegroups.com>


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 Received on Fri Jan 26 2007 - 08:27:30 CST

Original text of this message

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