Re: analyze problems

From: Rocr <rolland.cright_at_pwgsc.gc.ca>
Date: Tue, 30 Jan 2001 07:58:48 -0500
Message-ID: <956dpg$4v910@shark.ncr.pwgsc.gc.ca>

Thanks David,

I run estimate 30% on all tables every night and Compute stats on all tables on the weekend. No stale stats here.

Cheers,
Rocr

"David Fitzjarrell" <oratune_at_aol.com> wrote in message news:954ra3$l9l$1_at_nnrp1.deja.com...
> 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/
Received on Tue Jan 30 2001 - 06:58:48 CST