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 at www.psoug.org.
Click on DBMS_STATS
Scroll down to: "PREPARE_COLUMN_VALUES"
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Feb 06 2007 - 11:21:46 CST
