Re: analyze problems

In our last gripping episode "Rocr" <rolland.cright_at_pwgsc.gc.ca> wrote:
> 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/
>
>

Statistics cannot be run once and then forgotten. Data changes, data topology changes and soon the statistics that were current are no longer viable for the optimiser. I usually run statistics once weekly to ensure that they are current and that the CBO will make the proper decisions. It would appear that your database is frequently undergoing inserts, updates and deletes thus invalidating computed statistics after a week. Schedule your statistics computations on a regular basis. The CBO can't function properly with stale stats.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/