Re: oracle very slow after computing stats

Agreed. I have yet to read that computing stats on tables is necessary. Every article I have read says to estimate. They say only to compute on indexes. At any rate I found out that it wasnt the stats causing the problem. It was the data loading app. Turned out though that the bug only showed up after I ran the stats ;) Just a timing thing. As it stands I've scheduled to run stats every 3 days or so and see how that works out. For our biggest table (currently 90 million rows) even the estimate takes several hours so I dont want to be doing that every day. On the good news front running the stats improved the performance of another process by cutting running time down by as much as an hour in some cases.

Jason

"John Jones" <john.jones_at_duke.edu> wrote in message news:8hlov5$m3g$1_at_news.duke.edu...
> Not always true. Compute takes much longer if you have really large
 tables.
> My database is over 20G and if we compute, it takes hours. We do an
> estimate at 40% and it takes about 1/2 hour. Everything runs fine with
 40%,
> but it must be done frequently as a previous message suggested. I have a
> cron job that runs every morning at 6:00 am.
>
>
> John Jones
> Senior Oracle DBA
> Duke University OIT
> john.jones_at_duke.edu
> <kal121_at_my-deja.com> wrote in message news:8hk3fe$slt$1_at_nnrp1.deja.com...
> > You're probably better off doing a full compute, not an estimate of
> > your statistics as well.
> >
> > In article <8hjmru$rd0$1_at_flood.xnet.com>,
> > "Jason Kratz" <jkratz_at_rctanalytics.com> wrote:
> > > Thanks Dave. I'll give that a shot tonight and see what happens.
> > >
> > > Jason
> > >
> > > <ddf_dba_at_my-deja.com> wrote in message news:8hje3h$bn7
 $1_at_nnrp1.deja.com...
> > > > 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.
> > >
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Received on Wed Jun 07 2000 - 00:00:00 CDT