On Feb 6, 1:38 pm, "Ben" <bal..._at_comcast.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
>From lack of response, I'm going to assume not many people have done
this.
I have found a way that I believe I can accomplish tricking the CBO
into not seeing the high value of that rogue record, without using
dbms_stats.set_column_stats.
If I analyze the table and then export the statistics to the stattab,
I can go in and find that column in the table and change the value of
the C7 column to be a new lower value, and then import those
statistics back over the dictionary stats.
Is there any reason why this wouldn't work?
Received on Wed Feb 07 2007 - 14:44:30 CST
