Re: When and How to run dbms_stats.gather

Isn't this similar to performing a hot backup? During a hot backup there will be lots of I/O on your database datafiles. And if you use the ALTER TABLESPACE BEGIN BACKUP, then there will be increased I/O on your online redo logs, and same for your archive log destination. Does this mean that we should be doing hot backups when no users are signed on to the system? I know that this isn't *exactly* the same thing, but only meant to illustrate my point.

Personally, I wouldn't run a hot backup or DBMS_STATS during heavy usage periods like during office hours when most people will be hitting the database. But I would, and I do, run DBMS_STATS on a regular basis during lower periods of activity without doing anything to my connected user base. This is important for those databases that need 24x7 availability. I also take advantage of 9i's new monitoring feature and only have DBMS_STATS calc stats on new or significantly changed objects instead of re-calculating the stats every time I run DBMS_STATS.

Cheers,
Brian

> Collecting statistics means doing I/O against your tables. Perhaps lots of
> it, depending on the quality of the statistics being collected and the size
> of the tables being analyzed. So, whaddyareckon? Fancy a humungous amount
> of extra I/O going off during the working day whilst users are trying to
> get work done??
>
> *Can* statistics be collected with users logged in? Yes. Should they be??
> Probably not, in a production OLTP environment.

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"