Re: analyze problems

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 Mon Jan 29 2001 - 14:01:03 CST