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?
Received on Tue Feb 06 2007 - 11:07:47 CST
