Re: analyze problems

Fair enough.. check out a couple of the other pointers I gave you:

...historgrams for wildly skewed data (calculated by analyzing for particular columns)
...multi-block read count is set to a number that your OS can actually handle.

Regards
HJR Rocr <rolland.cright_at_pwgsc.gc.ca> wrote in message news:956dmm$4ji5_at_shark.ncr.pwgsc.gc.ca...
> Thanks Howard,
>
> Unfortunately this application was written without any idea of either
> optimization mode. The developers wrote, compiled, then forgot about it.
> There were no performance guidelines in place. Essentially it is a mess.
> Yes, moving to cost based optimization is a 'gutsy move' but one I think I
> have to take. Performance sucks. I'll give you an example: it took over
 40
> minutes to OPEN a window that was populated with data from the database.
 I
> tuned the query that fetched the data and with optimization and stats
> reduced the time to 40 millisecs. Go figure. Anyhow I think the cost
 based
> method is here to stay I think I have to go through every query and
 report.
>
> Cheers,
> rocr
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:3a75cbc9_at_news.iprimus.com.au...
> > As a general rule, if an application has been written with rule-based
> > optimisation in mind, it's extremely brave to suddenly move all the
> > goalposts and start asking it to respond well under cost-based
 optimisation
> > (though if you're 'tuning a query', it sounds like this is not
 applicable
 in
> > your case).
> >
> > Developers knew the 15 rules involved, and tweaked their code
 accordingly.
> > With cost-based, only Oracle knows what the rules are at any one point
 in
> > time!
> >
> > All I can say is that it sounds like your application works fine under
> > rule-based, and perhaps it's best left that way. You're going to have
 an
> > interesting time upgrading to newer versions of Oracle, however! (I
 seem
 to
> > recall Oracle as being on record somewhere that the future is
 cost-based,
> > and rule-based will die in a future version).
> >
> > There are one or two things you could tweak to see whether you can get
 any
> > better performance out of cost-based: is multi-block read set correctly,
 for
> > example (if set to high, the optimizer thinks full tablescans are
 really,
> > really cheap, and merrily scans every thing rather than opt for index
> > access)? Is your data skewed? And if so, did you calculate statistics
 for
> > individual columns, and thus generate histograms? "Select * from emp
 where
> > gender='F'" might sound like a job for a full-table scan -except that
 this
> > particular EMP table belongs to the Army's database, and accordingly,
 there
> > are 97% 'M', and only 3% 'F', so an index scan would have been more
> > efficient -that sort of skew needs addressing with histograms.
> >
> > Just some thoughts
> > HJR
> >
> > Rocr <rolland.cright_at_pwgsc.gc.ca> wrote in message
> > news:953oqq$4ji1_at_shark.ncr.pwgsc.gc.ca...
> > > Thanks Mark,
> > >
> > > The optimizer is set to CHOOSE(if there are stats it will use the
 cost
> > > based mode otherwise it will use the rules based) I initially ran
 stats
 on
> > > just 2 tables(the ones that were involved in a query that I was
 tuning).
> > > Once performance problems began to show themselves (after 1 week of
 the
> > > initial stats run on the two tables) I removed the stats and the
 performance
> > > returned to the old levels.
> > >
> > > I then ran stats on the entire schema COMPUTE and I found the same
 problems.
> > >
> > > Any ideas?
> > >
> > > "Mark D Powell" <markp7832_at_my-deja.com> wrote in message
> > > news:94s7jd$ta6$1_at_nnrp1.deja.com...
> > > > In article <94rsuf$kg1_at_shark.ncr.pwgsc.gc.ca>,
> > > > "Rocr" <rolland.cright_at_pwgsc.gc.ca> wrote:
> > > > > oracle 7.3.4
> > > > > HP-UX B.10.20
> > > > > I ran the analyze table command on a few tables(3 of 300). Over
 time
 (1
> > > > > week) the system degrade to unacceptable levels(slow response
 times
 to
> > > > > database access).
> > > > > I removed the stats and the application ran well(response times
 acceptable).
> > > > > Anyone know why?
> > > > >
> > > > > rocr
> > > > >
> > > > By removing the stats you probably told the rdbms to run the queries
> > > > using the rule based optimizer instead of the cost based optimizer,
> > > > CBO. The CBO was making bad choices. Your analyze may have used
 too
> > > > small a sample size to generate good statistics, 2- caught the
 tables
> > > > in question at a bad point in time (since a week had to go by before
> > > > things got bad) or 3- involved too few tables. If the tables you
> > > > analyzed are involved in joins and the other tables had no
 statistics
> > > > then the CBO would just make a lot of assumptions about the data.
 The
> > > > assumptions could easilty be wrong.
> > > > --
> > > > Mark D. Powell -- The only advice that counts is the advice that
> > > > you follow so follow your own advice --
> > > >
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > >
> > >
> >
> >
>
>
Received on Tue Jan 30 2001 - 19:10:35 CST