RE: Way to much log switching!!!
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Jun 2004 17:11:47 -0400
Yes online redo logs can affect performance even in 10g. See Oracle note
REDO LOGS SIZING ADVISORY 274264.1 of 10-JUN-2004. This article actually
includes SQL to find a recommended log size if FSMT is in use.
If my understanding and memory is correct, redo logging does not freeze
during a log switch unless it is for the duration of the last write to the
current log. Oracle flushes the buffer for the last log and then just
starts writing to the new one. But if you keep switching and come back
around to an online log which is still not archived then all update
processes pretty much lock up till a log becomes available.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
Rajesh.Rao@xxxxxxxxxxxx
Sent: Wednesday, June 30, 2004 4:47 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Way to much log switching!!!
I dont think recovery is the issue. The issue could be performance, caused,
if not by frequent checkpointing, then by log switches. Does redo logging
not freeze during a log switch, since log buffer space wont be allocated
until the log switch is complete. So, in an hour, is it not better to have
3 log switches than 30?
Regards
Raj
Jeremiah Wilton
<jwilton@speakeasy. To:
"'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
net> cc:
Sent by: Subject: RE: Way to much
log switching!!!
oracle-l-bounce@fre
elists.org
06/30/2004 04:10 PM
Please respond to
oracle-l
On Wed, 30 Jun 2004, Powell, Mark D wrote:
> 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.
In modern Oracle, checkpoints happen constantly as part of the fast
start mechanism. In 10g this is the default. Even in the days of
log_checkpoint_interval, the only stuff written to disk was what was
on the LRUW list, not the whole buffer cache!
So I don't buy that frequent checkpoints adversely affect the cache or
performance. Checkpoints only use a small portion of the DBW0's write
batch anyway.
As for recovery, why is it more difficult to recover with 100 32M
logfiles than with 10 320M logfiles? I use wildcards to copy stuff
and automatic log apply to roll forward. I could even argue that in
the case of a restore, the first 32M logfile is going to be restored
sooner than the first 320M log, allowing me to commence roll forward
sooner, and shorten overall time to recover.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jeremiah Wilton
>
> 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
