Re: When and How to run dbms_stats.gather

NB there is a new option on gather_table_stats = no_invalidate

There is (in later versions) much more scope for defining exactly what you want to gather

if you want us to help we need much more detail than this

But you should know that a full gather of column and table statistics for a table is not a small task especially if you add histograms and there is a corresponding large load placed on the server io and cpu - this is the main reason for doing it off-hours.

I have seen existing statistics crash a system; I have not seen the gather of statistics crash a system.

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:1Uh0b.100125$9x4.41927_at_news02.roc.ny...
>
> "Andras Kovacs" <andkovacs_at_yahoo.com> wrote in message
news:412ebb69.0308181818.15b71440_at_posting.google.com...
> > Okay the question wasn't clear.
> > You have too much imagination.
> >ex
> > Should users be logged off when running dbms_stats ?
> > Or can we run dbms_stat during normal cycle of operations ?
> >
> > Ask the same from Oracle you will get as many answer as they have
> > customer analysts ....
>
> You would normally run it off peak hours.
> You can run it during the normal cycle of operations, however there are
downsides to that:
> * One being that if analyzing causes any problems, you'd rather be fixing
it off peak hours.
> * Another being that analyzing invalidates any reference to the objects
being analyzed, causing them
> to be re-parsed. Thus you might not want to analyze while your site/db
is showing peak activity.
> * Analyze itself uses resources depending on how much data is being
analyzed (estimate factor etc),
> so just like a batch operation, its preferable to do it off-peak hours.
>
> Now about your original problem: Oracle crashing when you do analyze...
maybe you did not explain that
> either. Or maybe you want us to use our imagination there also.
> By crashing you mean the database just goes belly up? No errors/trace
files/alert messages?
> I'd admit I've never really seen that happening ...
> You also did not state what exactly are you analyzing (db/schema/bunch of
tables),
> nor have you stated your Oracle version, error messages ...
>
>
> Anurag
>
>
Received on Tue Aug 19 2003 - 04:55:54 CDT