RE: log file sync
Date: Thu, 23 Aug 2012 09:17:32 -0400
Message-ID: <304F58144267C5439E733532ABC9A3A115C5B1F5_at_USA0300MS02.na.xerox.net>
After I added LGWR to '_high_priority_processes' and repeated the same load test, the LFS wait disappeared from the top five wait events:
Top 5 Timed Foreground Events
Avg wait % DB Event Waits Time(s) (ms) timeWait Class
- ------------ ----------- ------ ------
direct path read 1,284,396 62,460 49 65.6User I/O
DB CPU 19,569 20.6 IPC send completion sync 3,817,950 4,351 1 4.6Other
db file sequential read 17,593 1,559 89 1.6 User I/O
PX Deq: reap credit 44,535,677 1,327 0 1.4 Other
Now I am working on addressing the "direct path read " event which is coming from a standard EBS statement that contains 'parallel' clause without any DOP which ends up invoking 144 parallel processes on three RAC nodes (PARALLEL_MAX_SERVERS is set to 48 on each RAC node).
From: Kevin Closson [mailto:ora_kclosson_at_yahoo.com]
Sent: Wednesday, August 22, 2012 3:38 PM
To: Hameed, Amir; Greg Rahn
Cc: oracle-l_at_freelists.org
Subject: Re: log file sync
how did this turn out?
From: "Hameed, Amir" <Amir.Hameed_at_xerox.com>
To: Greg Rahn <greg_at_structureddata.org>
Cc: oracle-l_at_freelists.org
Sent: Wednesday, August 8, 2012 6:53 AM
Subject: RE: log file sync
I had an offline discussion with Kevin Closson. The issue seems to be with the scheduling latency of LGWR. Here is information on the LFPW histogram. I have added a column showing the LFS latency corresponding to each LFPR:
% of Waits ----------------------------------------------- Total LFS(ms) Event Waits <1ms <2ms <4ms <8ms <16ms
<32ms <=1s >1s
- ------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
48(ms) log file parallel write 4270 100.0
45(ms) log file parallel write 4105 100.0
41(ms) log file parallel write 6913 100.0
41(ms) log file parallel write 3409 100.0
38(ms) log file parallel write 7335 100.0
I am going to run LGWR in RT mode and then test again.
From: Greg Rahn [mailto:greg_at_structureddata.org]
Sent: Tuesday, August 07, 2012 6:46 PM
To: Hameed, Amir
Cc: oracle-l_at_freelists.org
Subject: Re: log file sync
You may find the this of use when looking at log file sync and log file parallel write issues using the AWR report (there is usually more to the issue than the lines you provided):
http://structureddata.org/2011/12/20/visualizing-active-session-history- ash-data-with-r/
On Mon, Aug 6, 2012 at 12:55 PM, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
Hi all,
My AWR report shows 'log file sync' among the top five events. When I
look at the 'Foreground Wait Events', I see the following:
...
My question is that if the LGWR is not waiting for the 'log file
parallel write' during synchronous writes (avg. wait time=0) then why is
the average wait time of 'log file sync' event for the foreground and
background processes (DBWR, LMS etc.) is high?
-- Regards, Greg Rahn | blog <http://bit.ly/u9N0i8> | twitterReceived on Thu Aug 23 2012 - 08:17:32 CDT
<http://bit.ly/v733dJ> | linkedin <http://linkd.in/gregrahn>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l