RE: Sync redo log waits

From: Shanmugam, Satheesh B <satheesh.b.shanmugam_at_jpmchase.com>
Date: Wed, 20 Feb 2013 05:03:10 +0000
Message-ID: <2BCCB1F834945545A57388A869B75CA429F2EDAD_at_SPTCMX003.exchad.jpmchase.net>

ARCHIVE_LAG_TARGET is not hard limit. _log_switch_timeout would be more accurate.

Refer to https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=6920172&productFamily=Oracle

---

ARCHIVE_LAG_TARGET=900 is not as simple as causing a log switch   every 15 minutes. The code tries to be more intelligent than   that - it looks at how long a time period the current   log spans and how long it expects to take to archive the log   using an in memory archival rate and kicks off a log switch if   it thinks that these two added together will exceed the   ARCHIVE_LAG_TARGET. ie: The aim is the get the log archived   so that when archival completes the archived log contains no   redo which is more than archive_lag_target seconds old.   This is intended to be of help for standby databases where   remote archival is being used.

---

Regards,
Satheesh Shanmugam
US# 614-213-5790

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Saad Khan Sent: Wednesday, February 20, 2013 5:37 AM To: Andrew Kerber
Cc: Paul.Houghton_at_admin.cam.ac.uk; oracle-l_at_freelists.org Subject: Re: Sync redo log waits

I just set ARCHIVE_LAG_TARGET to 15 min and this has what I got in last few hours:

DAY        HOUR LOGSWITCH COUNT
----------      ----      ----------------------
2013-02-19 16         17
2013-02-19 17         20
2013-02-19 18         22

On Tue, Feb 19, 2013 at 1:50 PM, Andrew Kerber <andrew.kerber_at_gmail.com>wrote:

> Oracle has a setting, archive_lag_target, that will force a log switch
> if one has not been done in the time frame specified by archive_lag_target.
> So when the redo activity is uneven, I usually set that parameter and
> set the redo logs to a very large size.
>
> On Tue, Feb 19, 2013 at 11:48 AM, Paul Houghton <
> Paul.Houghton_at_admin.cam.ac.uk> wrote:
>
>> Hi
>>
>> According to a number of oracle support notes, e.g. 147468.1
>> performance will be impacted if log switches more often than about
>> once every 15 minutes. That note specifically says that performance
>> will be impacted by switching once every 3 minutes, which is around what you are doing.
>>
>> So yes, you should increase the size of the redo logs to 4.5 - 6
>> times their current size unless you are sure you can reduce the
>> number of switches by reducing the workload.
>>
>> We have a cron job which switches the log file every 15 minutes to
>> ensure the standby is up to date, though I am not sure if this is
>> needed any more (11.2).
>>
>> You may be able to talk with the application people to get them to
>> spread the load. Sometimes you get a trendy time to start a batch
>> process (say
>> 2am) and everyone starts their process at this time, though I can't
>> see a pattern in the data you posted. Does the application keep some
>> kind of log or audit of what it was doing which you could consult to
>> find out what was being run, or use AWR reports if you have tuning
>> pack, or even statspack and tune that area of the application or move it to a quieter time?
>>
>> Hope this helps
>>
>> PaulH
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l


This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 20 2013 - 06:03:10 CET