| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Unique index not being used, up to date stats
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);
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
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
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);
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
 |
 |