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 Fri Jan 26 2001 - 10:11:34 CST
