Sync redo log waits

From: Saad Khan <saad4u_at_gmail.com>
Date: Tue, 19 Feb 2013 12:06:24 -0500
Message-ID: <CACqGOPLiouE1vZG+QtJSmYEdFFopfLncdoMTCqi6cfbhhgWvkA_at_mail.gmail.com>

Hi Gurus,
I'm writing regarding our prod environment with Oracle 11g (11.2.0.2) on 64-bit AIX 6.1 with storage on IBM XIV devices.

The users have been complaining a very poor performance and all sorts of delays.

The issue I've been seeing very large waits for "Average Time to Sync a Redo Log". Its reaching as high as 600 ms. Right now we've four redo groups ( 2 members each) with a size of 600 MB each. Now when I checked the hourly logswitch frequency sometimes the count is upto 18 per hour (very alarming) while sometimes its just 1 or 2 as given below. Should I increase the size of redologs? What else can I do to curb these waits? The related parameters configured are : log_checkpoint_timeout = 1800
fast_start_mttr_target = 0
log_checkpoint_internal = 0

Appreciate earliest help. Here is the hourly logswitch count:

HOURLY LOGSWITCH FREQUENCY:

---

DAY HOUR TOTAL
--------------- ---- -----

2013-02-17      22       4
2013-02-17      23       4
2013-02-18      00      13
2013-02-18      01       5
2013-02-18      02       3
2013-02-18      03       1
2013-02-18      04       6
2013-02-18      05       5
2013-02-18      06       3
2013-02-18      07       3
2013-02-18      08       2

DAY             HOUR TOTAL


--------------- ---- -----

2013-02-18      09       1
2013-02-18      10       1
2013-02-18      11       2
2013-02-18      12       1
2013-02-18      13       3
2013-02-18      14       4
2013-02-18      15       4
2013-02-18      17       1
2013-02-18      18       2
2013-02-18      22       4
2013-02-18      23       4

DAY             HOUR TOTAL


--------------- ---- -----

2013-02-19      00      12
2013-02-19      01       1
2013-02-19      02       2
2013-02-19      03       1
2013-02-19      04       5
2013-02-19      05       3
2013-02-19      06       2
2013-02-19      07       1
2013-02-19      08       1
*2013-02-19      09      18*
*2013-02-19      10      18*

DAY             HOUR TOTAL


--------------- ---- -----

2013-02-19      11       4

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

Received on Tue Feb 19 2013 - 18:06:24 CET