Redo Log Files Too Big? Hidden gotchas?
Date: Mon, 9 Dec 2013 11:36:08 -0600
Message-ID: <CAP79kiRSXEBURcvkOfcY1YhH+-soAfeZJSUCafJJp7O0n8ZOjA_at_mail.gmail.com>
My client has a production database that is hitting 150-160 redo log switches PER HOUR during the hours of 1-3 am. This is database that is loaded thru an nightly ETL process where tables are truncated and reloaded from the source. Average redolog switches during nightly processing is 50+ but during the day we see reasonable switches of 0,2,4 or 6 per hour.
Performance is a concern and as expected we are seeing logfile switch completion in the top 5 wait events during those periods.
Current RedoLog sizes are 300MB. If I take Oracle's recommended 4 per hour then we are at approximately 38.75x above the recommended value. I'd like to get the redo log switches down to 4-6 per hour but that means resizing my redologs to about 10GB per log member.
I'm going to set archive_lag_target to a 15 minute interval, but I'm concerned that 10GB might be "too big". I can't think of a technical reason why but I've got a nagging feeling that I might be overlooking something.
I was thinking there might be a negative impact to backups, but logically the amount of archive log data being backed up for the same period should be similar (whether its many ~300 MB archived logs or few ~10GB archived logs).
The filesystems in question reside on a NetApp appliance and the filesystems for database files and the backup location are NFS mounted.
Is there any obvious thing I'm missing here? I'm going to reduce the count of redo log files while increasing the size (that's the plan anyway). I plan to have a couple of additional groups to take into account any slow archiving so that I should have a "spare" redolog group in case Oracle tries to wrap around to a group that is being archived.
Thoughts?
Chris Taylor
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 09 2013 - 18:36:08 CET