Redo Log improvement (Follow Up)

From: <Christopher.Taylor2_at_parallon.net>
Date: Tue, 27 Nov 2012 14:21:45 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885653AB300_at_NADCWPMSGCMS10.hca.corpad.net>



Regarding the discussion from 2 weeks ago (or so), when we talk about redo log switches in a RAC environment, should you target an "x" number of redo log switches per hour TOTAL over (in this case) 3 nodes, or a redo log switches PER thread?

For example, the cluster overall averages (now) 12 per hour. But it's averaging 4 per hour per node. (Also I have archive_lag_target set to get a redo log switch at a minimum of every 15 minutes)

Thoughts?

Chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, November 16, 2012 2:27 AM
To: oracle-l_at_freelists.org
Subject: Re: Redo Log improvement - how to analyze and attack?

Chris,

It's at this point you have to remind yourself that the AWR report is a broad-brush indicator of resource usage which sometimes pinpoints specific things that could be worth addressing because the side effects of their individual impact could affect every who is using the system.

Once you've dealt with the overview, it's still possible for individual tasks to be in trouble even though the headline figures don't change much (especially over a 5 hour aggregate).

Typically, excessive log file switch can result in increased rates of data block writes, and the combination of the (necessary) lgwr writes with the extra dbwr writes can result in a general slow-down of I/O (in particular reads) on the system. Over the interval your average write time for a redo write was about 2.5 ms for a 59KB write - which is pretty adequate (although that might have been 1ms for 4 hours and 8.5 ms for one hour, of course). When the average seems reasonable, you need to look at sessions - maybe most of the batch was unchanged, but perhaps there's one session that waited on 10,000 log writes (technically the log file sync) that is now running much faster. The same applies to reads - maybe your I/O subsystem was never overloaded - what was the average single block read time, what is it after the change in log file size - has some critical SQL statement started to complete much more quickly because it spends less time waiting on a very large number of reads, while every other statement only shows a small improvement because they don't (individually) do many reads ?

Another point for everyone to bear in mind is that Oracle has made several changes to write activity on log file switches - the "log switch checkpoint" can now be delayed for a long time, so that smaller log files don't necessarily cause sudden surges in dbwr writes shortly after log switches - if you had 10 small log files you might, for example, see a surge in DBWR writes somewhere around the 8th or 9th switch in the more recent versions of Oracle.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

  • Original Message ----- From: <Christopher.Taylor2_at_parallon.net> To: <lcarapinha_at_gmail.com>; <Mark.Bobak_at_proquest.com> Cc: <oracle-l_at_freelists.org> Sent: Thursday, November 15, 2012 9:29 PM Subject: RE: Redo Log improvement - how to analyze and attack?

| Luis,
| Any idea how much its slowing down my system? How can we (as DBAs)
quantify the "how much" is "too much"?
|
| This is kind of turned into a thought exercise I guess..I'm not trying to
be an a$$.
|

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


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 27 2012 - 21:21:45 CET

Original text of this message