Re: analyze problems

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 - 06:57:18 CST