RE: LGWR blocking sessions
Date: Fri, 12 Jun 2009 09:17:25 -0400
Hmmm. Certainly busy seems to be it, (not because of backups). The CPU averaged around 89% for around the 12 hours I did the ADDM... This is a peoplesoft database... and the normal resource monster is SQL statements. I am suspecting CPU. The CPU could cause log file sync events, but I would like to confirm that these wait events do indeed trigger a 'blocking session' warning with LGWR as the blocker.
Since I suspect bad SQL generated by Peoplesoft or ad hoc, I am mostly looking for confirmation that one leads to another which leads to the LGWR blocking sessions message.
So Having said that, here's some more info on the side: There is a peoplesoft DBA... but he is 'busy', and 3000 miles away, and off today. I have been trying to get him, (or me) to investigate Embarcadero, and ignite for a month or so...
The locks don't seem to last long because I havn't seen them yet. There can be up to 4 sessions queued at the time of the lock, (part of the message).
User logon time warning showed up just about midnight at 20000ms, and an average in the last 24 hours of 6818.81ms.
select event, time_waited from v$session_event where sid=221 order by 3;
direct path read temp 0 direct path read 0 latch: redo writing 1 direct path write 30 os thread startup 32 log file sequential read 1047 control file sequential read 7471 log file single write 7496 control file parallel write 24913 events in waitclass Other 31736 log file parallel write 22712903 rdbms ipc message 764622536
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MacGregor, Ian A. Sent: Thursday, June 11, 2009 6:52 PM
Subject: RE: LGWR blocking sessions
OEM should provide more information, are they log buffer waits, log file synch, etc. Where are the online redologs located? Are these waits actually causing any problems? How long does the blocking last? How many sessions are queued?
SLAC National Accelerator Center
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com
Sent: Thursday, June 11, 2009 12:41 PM
Subject: LGWR blocking sessions
My log writer is triggering an alert from OEM that it is blocking sessions. (SID ### is blocking -- which is LGWR).
I have googled, and searched metalink, and come across log file sync's, and even Kevin Closson's interesting blog about io and cpu (again :)).
Even though I can find people (several) with the same issue and question, I did not come up with any solutions that where directed specifically.
10.2.0.4, peoplesoft, SAN. Sidenote. Archiver is copying logfiles to a Quantum dxi7500, (with compression and deduping software bundled).
Any ideas where to go from here? (I'll be back early tomorrow)
Alert log snippet during time receiving two such alerts which looks normal. No trace files. Some db's have been know to warn of the 500ms write threshold was exceeded, but not here now.
Thu Jun 11 11:04:57 2009
Beginning log switch checkpoint up to RBA [0x1bc1.2.10], SCN: 2847388841 Thu Jun 11 11:04:57 2009
Thread 1 advanced to log sequence 7105 (LGWR switch) Current log# 1 seq# 7105 mem# 0: /u02/oradata/HRPROD/log01a.log Current log# 1 seq# 7105 mem# 1: /u03/oradata/HRPROD/log01b.log Thu Jun 11 11:10:02 2009
Completed checkpoint up to RBA [0x1bc1.2.10], SCN: 2847388841 Thu Jun 11 12:11:53 2009
Beginning log switch checkpoint up to RBA [0x1bc2.2.10], SCN: 2847490018 Thu Jun 11 12:11:53 2009
Thread 1 advanced to log sequence 7106 (LGWR switch) Current log# 2 seq# 7106 mem# 0: /u02/oradata/HRPROD/log02a.log Current log# 2 seq# 7106 mem# 1: /u03/oradata/HRPROD/l0g02b.log Thu Jun 11 12:17:11 2009
Completed checkpoint up to RBA [0x1bc2.2.10], SCN: 2847490018 Thu Jun 11 13:07:20 2009
Beginning log switch checkpoint up to RBA [0x1bc3.2.10], SCN: 2847597436 Thu Jun 11 13:07:20 2009
Thread 1 advanced to log sequence 7107 (LGWR switch) Current log# 3 seq# 7107 mem# 0: /u02/oradata/HRPROD/log03a.log Current log# 3 seq# 7107 mem# 1: /u03/oradata/HRPROD/log03b.log Thu Jun 11 13:12:36 2009
Completed checkpoint up to RBA [0x1bc3.2.10], SCN: 2847597436
http://www.freelists.org/webpage/oracle-l Received on Fri Jun 12 2009 - 08:17:25 CDT