Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Way to much log switching!!!
I for one like my online logs sized such that in 24 hours the db generates
24 to 48 logs. This is a reasonable number of logs to work with if you ever
have to perform manual roll forward during recovery. This might be the case
where you do not have enough room to keep all the uncompressed logs since
the last backup on disk in one location. Then there is that little feature
where checkpoints are triggered on a log switch and all dirty blocks are
written to disk. I see no reason to force buffer flushing this frequently.
IMHO -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jeremiah Wilton
Sent: Wednesday, June 30, 2004 3:30 PM
To: oracle-l_at_freelists.org
Subject: Re: Way to much log switching!!!
On Wed, 30 Jun 2004, Mark Moynahan wrote:
> Currently, we have an ODS system with 3 redo log files sized at 200M each.
> Looking at v$log_history, the logs are switching every 1 to 2 minutes. If
a
> 200M log switches at 2 minutes then a 400M log should switch in 4 minutes
> and 1G log should switch in 10 minutes. I've recommended to size the redo
to
> 1G along with determining what is causing all the redo with Logminer. Is
20
> minutes a good rule of thumb to go by when seeing how often the redo logs
> switch?
If checkpoints and archiving are keeping up, then I can't think of a good reason logs shouldn't switch every 2 minutes.
You don't need logminer to find the redo culprit, just this SQL:
select module, osuser, sql_hash_value, value / (sysdate - logon_time) redo
from v$session s, v$sesstat ss, v$statname sn
where s.sid = ss.sid
and ss.statistic# = sn.statistic#
and name = 'redo size'
order by redo;
-- Jeremiah Wilton http://www.speakeasy.net/~jwilton ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jun 30 2004 - 14:46:45 CDT
![]() |
![]() |