Re: analyze problems

In our last gripping episode "Rocr" <rolland.cright_at_pwgsc.gc.ca> wrote:
> I read that somewhere... Good call I'll give it a shot. Have you
 ever
> generated and used histograms, buckets, etc.
>
> I understand the theoretical concepts behind them but my practical
> experience with them is nill.
>
> "David Fitzjarrell" <oratune_at_aol.com> wrote in message
> news:956lb8$49q$1_at_nnrp1.deja.com...
> > In our last gripping episode "Rocr" <rolland.cright_at_pwgsc.gc.ca>
 wrote:
> > > 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/
> > >
> > >
> >
> > Statistics are estimated? It is quite possible that your
 statistics do
> > not accurately assess the key distribution and, as such, your
 results
> > are skewed to provide a "normal" picture that may not exist.
> >
> > Try generating histograms. They may be the key to your performance
> > problems.
> >
> > --
> > David Fitzjarrell
> > Oracle Certified DBA
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

You should first check the distribution of your keys before computing histograms. Select the key columns and a count, grouped by the key columns, to determine if histograms will be helpful. If you find histograms will be beneficial use the DBMS_STATS package to create the histograms; you may also use the ANALYZE command to accomplish this.

I would check the documentation for more information.

--
David Fitzjarrell
Oracle Certified DBA


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