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 - 06:48:43 CST
