Re: Redo Log improvement - how to analyze and attack?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Nov 2012 08:26:33 -0000
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@xxxxxxxxxxxx>
To: <lcarapinha@xxxxxxxxx>; <Mark.Bobak@xxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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$$.
|
--
//www.freelists.org/webpage/oracle-l
Other related posts:
