Re: When and How to run dbms_stats.gather

On Tue, 19 Aug 2003 14:43:27 GMT, Brian Peasland <dba_at_remove_spam.peasland.com> wrote:

> 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.

It's a reasonable point. Why, though, do you think RMAN allows you to limit the number of files in a backup set, or to throttle back the I/O rate of its various channels? Precisely because yes, hot backups do cause excessive I/O. RMAN of course licks the excessive redo generation problem.

So, taking all that lot into account, the answer must surely be that the two situations are not, ultimately, comparable at all, because the excessive I/O of hot backups can be dealt with, whereas that generated by statistics calculation must be lived with.

Besides, you can probably live without fresh statistics for another 8 hours or so. But running for 8 hours without a backup that business rules dictate should be taken is another matter entirely.

Plus, running a hot backup doesn't invalidate most of the contents of your library cache. Calculating fresh statistics is likely to.

But I notice I was lax in my original post. I shouldn't have suggested "no users logged on", just "few", and then I think we agree entirely.

Regards
HJR
>
> 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.
>
>
>

-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/