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: 6 Feb 2007 10:38:03 -0800
Message-ID: <1170787083.654631.326060@h3g2000cwc.googlegroups.com>


On Feb 6, 12:21 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Ben wrote:
> > On Feb 1, 11:05 am, "Ben" <bal..._at_comcast.net> wrote:
> >> On Feb 1, 8:56 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> >> wrote:
>
> >>> select min(wadco), max(wadoco) from the table -
> >>> I think you'll find the same value you've told us
> >>> about is roughly half way between the two, slightly
> >>> nearer the max than the min
> >>> If this is the case, you need to find out if it is just one
> >>> extreme high value - and then your best bet may be
> >>> to use dbms_stats.set_column_stats to hide the extreme
> >>> values by setting the high_value to something that is
> >>> more in keeping with your expectations.
> >>> --
> >>> Regards
> >>> Jonathan Lewishttp://jonathanlewis.wordpress.com
> >>> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> >>> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >>> "Ben" <bal..._at_comcast.net> wrote in message
> >>>news:1169821650.333875.88740_at_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- Hide quoted text -
> >>> - Show quoted text -
> >> That's it! Thanks to you all for pointing me in the right direction. I
> >> selected min and max and found them to be 1 and 20926026. values >=
> >> 12000000 : 1, values >= 11723420 : 10938 and counting. Looks like now
> >> I'm researching how to set the column stats to not see that high
> >> value. It's amazing what havoc one record can do to an entire ERP
> >> system, this thing was responsible for our MRP/MPS running about 5
> >> hours over normal time that week also.- Hide quoted text -
>
> >> - Show quoted text -
>
> > I've read the manual on dbms_stats.set_column_stats and I can not
> > understand/figure out how to actually set the high_value of a column
> > to make Oracle think that the rogue record doesn't exist. I've
> > searched on asktom, johnathan's site, morgan's library and other
> > various places and I've yet to find a good example of how to do this.
> > Anyone ever actually do this? Could you help a fellow out with a
> > sample?
>
> You should find the answer in Morgan's Library atwww.psoug.org.
> Click on DBMS_STATS
> Scroll down to: "PREPARE_COLUMN_VALUES"
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Mr. Morgan,
I did see that and it looks pretty similar to what the documentation has if not exact. The problem is I just don't really understand how to use it. I need a sample showing how to say, column1 high_value = 11900000. The syntax doesn't really tell you where you are supposed to give it a value to use and how to use it in conjunction with set_column_stats. I'm sure this will appear elementary to me after I see an example but until then I don't understand the documentation. Thanks
Ben Received on Tue Feb 06 2007 - 12:38:03 CST

Original text of this message

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