Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle very slow after computing stats

Re: oracle very slow after computing stats

From: John Jones <john.jones_at_duke.edu>
Date: 2000/06/07
Message-ID: <8hlov5$m3g$1@news.duke.edu>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US