Re: Redo Log File Sizes

On Jan 23, 6:21 pm, Jeremiah Wilton <jerem..._at_ora-600.net> wrote:
> Dereck L. Dietz wrote:
>
> > When we have two or more processing performing updates, deletes and/or
> > inserts at the same time our system slows to a crawl.
>
> > I was under the impression that Oracle suggests that Redo Log Files be sized
> > so they switch only every 20 minutes or so. Is the frequency of the log
> > file switching in the excerpt below normal?
>
> The 20 minute rule is not based in any type of science, and can be filed
> up there with "BCHR must be 99.9 or greater."

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.

The science comes in when you define the uptime requirements or service level agreement.

(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. Now it has attained mythic status, but still is not unreasonable as a ballpark setting before more specific tuning).

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

The sequential read and write characteristics of copying redo can have quite an effect on a RAID-5 fighting with random reads and writes to data and undo, performance goes to hell when any of the various device buffers are saturated.

Of course, the waits will tell if it is I/O or block contention in the SGA or whatall. The alert log shown doesn't seem to have checkpoint messages, so that's good. But we haven't seen that there aren't any times when there are such messages.

>
> Regards,
>
> Jeremiah Wilton
> ORA-600 Consultinghttp://www.ora-600.net

jg

--
@home.com is bogus.
Nobody knows what's going on, so reinstall everything:
http://www.linux.com/feature/125548