Re: Sync redo log waits
Date: Tue, 19 Feb 2013 13:38:58 -0500
Message-ID: <CACqGOPJBXmFgL1E_7Qr79FffXXWyNveMtKOMPRFbcxyz4LbgGg_at_mail.gmail.com>
Hi,
I understand that this is a huge logswitch count but if you notice most often its restricted to a very less number even 1-2. Do you still think we should have increased size? I've approached application guyz to push them to spread the jobs across but as always, in the end it comes to DBA to spell the magic of all their problems.
I'm attaching the statspack report here, if anybody can review and suggest changes. Thanks.
On Tue, Feb 19, 2013 at 12:48 PM, 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-lReceived on Tue Feb 19 2013 - 19:38:58 CET