Re: Oracle statistics

Hi Niall, Maria et All,

I think Niall has pretty well said it all.

The only things I would add are:

1. I asked why on earth you would drop and recreate objects all the time and you haven't really provided an answer. I would investigate this as a matter of urgency. For some reason, Niall failed to mention this concern ;)
2. Look at using DBMS_STATS rather than analyze, pick an appropriate sample size (you haven't mentioned DB version but note 9i has some nice options with this) and consider running in parallel, which is one of the advantages of this package.

Cheers

Richard
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3d7f5d4d$0$1291$cc9e4d1f_at_news.dial.pipex.com...
> "Maria" <evoradba_at_yahoo.ca> wrote in message
> news:351fd9d6.0209110532.28ad59f5_at_posting.google.com...
> > Hi Richard
> > Thanks for the info
> > We have to drop and create tables indexes etc in order to clean the
> > database, etc, at least that is what I have been told, I'm new at this
> > place, and they have this for 1 year, where then run a ctas every day,
>
> We have a saying where I work. "its all completely barking". The process
 you
> have outlined above qualifies. I just can't begin to imagine what benefit
> this is supposed to bring. If you could find out what problem this is
> solving I'm sure a far less drastic and taxing solution could be found.
>
> > is like a export import of the database, I just don't understand what
> > update STATITISCS does, why to do we have to do this all the time,
> > where does it store this infomartion, the statistics takes a long time
> > to run which is not acceptable (7 hours at times)
> > Do you have any other suggestions
>
> Well don't do the drop and recreate and you'll need to collect stats far
> less frequently for a start. If you can't change the procedure (and you
> should) then consider collecting stats on a sample of rows (using the
> estimate clause). it takes less time (but collects less accurate stats
> increasing the CBO's chance of producing a poor execution plan). The trade
> off is less collection time vs possibly impaired performance
>
>
> Did I mention you shouldn't do the drop and recreate at all? I don't
 think
> the stats are your problem your procedure is.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
>
Received on Thu Sep 12 2002 - 02:04:50 CDT