Re: Redo Log File Sizes
Date: Fri, 25 Jan 2008 18:32:39 -0800
Message-ID: <SvSdncPvJq5RBgfanZ2dnUVZ_jqdnZ2d@speakeasy.net>
joel garry wrote:
> On Jan 23, 6:21 pm, Jeremiah Wilton <jerem..._at_ora-600.net> wrote:
> >> Dereck L. Dietz wrote: >> >>> I was under the impression that Oracle suggests that Redo Log Files be sized >>> so they switch only every 20 minutes or so. >> >> The 20 minute rule is not based in any type of science...
>
> It's somewhat correllated with the granularity of restoration. If you
> are getting your arcs and backups away from the computer (or the disk
> drive with your data files on it) and it gets nuked, this controls how
> much data you've lost. You don't have to lose any data at all if you
> can protect the unarchived redo.
With modern versions of Oracle, log shipping can be done asynchronously for the current online redolog, so we no longer have to wait for log switch to be assured of the presence of contemporary redo at the remote archive site. Hopefully for most people, forcing frequent log switches is not an issue thanks to this feature.
Assuming someone were not using LGWR ASYNC for some reason, wouldn't the original poster's frequent log switching provide *more* recent log data at a hypothetical remote log shipping site?
> (I speculate the rule came from before there was a checkpoint process
> and a typical configuration was VMS with a few disks, so you didn't > want a long running checkpoint to interfere with a long running > archive copy.
Oddly, the CKPT process doesn't perform checkpointing. I think this is quite funny. CKPT performs datafile header and controlfile writes upon checkpoint completion. Before CKPT, this was LGWR's job, so all commits stopped while header/controlfile SCNs were written. DBWn perform checkpoint I/O as a proportion of the DBWR write batch as they always have. This proportion and the currency of the checkpoint is now demand-based when using FAST_START_MTTR_TARGET.
> Now it has attained mythic status, but still is not
> unreasonable as a ballpark setting before more specific tuning).
I suppose, but the whole idea that log swicthes have anything to do with the OP's performance problem is complete and total conjecture. Why are we even talking about log switches when we don't know what the predominant waits are? Many I/O subsystems will provide excellent performance despite log switches every couple minutes. It just depends too much on the underlying storage performance to continue proliferating blind rules of thumb on any Oracle metric.
>> Based on your post, there does not seem to be any evidence or reason to >> believe that the performance problems are caused by logfile size or >> switch frequency. You need to look at the wait events on the database >> at the time if the slowdown. If you are on 10g or above, a time-series >> history of wait data is available in dba_hist_active_sess_history. Any >> conjecture without looking at the waits is just that.
>
> True, but there can be an impact if the archiving I/O interferes with
> normal db I/O, which can happen on many configurations - certainly a
> possibility if you don't have a lot of controllers, and likely if you
> have just one disk device apparent to Oracle on a Windows box.
Yes, I/O contention might be a problem... but there is absolutely no evidence to lead anyone to believe that is the case. It is just as possible that there is enqueue contention for a single row of data, but that's just conjecture too. It could be anything. I don't see what we get out of propping up this theory without data.
Regards,
Jeremiah Wilton
ORA-600 Consulting
http://www.ora-600.net
Received on Fri Jan 25 2008 - 20:32:39 CST