Re: oracle very slow after computing stats

Jason,

Once statistics are computed for a schema they must be kept current or performance will suffer. At some point during the day statistics should be computed again, usually during a period of low activity. I have run into this problem before and it was due to initially computing statistics on a schema and then never re-computing them. You mention nightly data loads; this type of activity can quickly invalidate computed statistics and create slow response from the database on inserts, updates and queries. Create a script to compute statistics on your tables and schedule it to run early in the morning. This will improve performance considerably.

David Fitzjarrell
Oracle DBA

In article <8hj6vs$ivf$1_at_flood.xnet.com>,   "Jason Kratz" <jkratz_at_rctanalytics.com> wrote:
> We have an Oracle instance that is running considerably slower after
> computing stats. Heres the situation: I estimated stats at 25% on
 all
> tables in a schema (not SYS) and computed stats on all of the indexes
 in
> that schema (primary keys, etc are all seperated out from the data
 into
> seperate tablespaces). At first our nightly loads ran fine. After a
 day or
> so of the database up and running with stats the dataloads are running
> several times slower. We had this problem before but I had only
 computed
> stats on a few tables. When I dropped them before everything worked
 fine
> again. I was under the impression that most dbs should be running
 with
> computed stats. Shouldnt stats help increase db performance? Another
 note:
> this db is highly normalized resulting in rather large joins necessary
 when
> joins are done. Help!!!!
>
> Jason
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jun 06 2000 - 00:00:00 CDT