Re: Oracle statistics

"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
******************************************