RE: LGWR blocking sessions
Date: Fri, 12 Jun 2009 09:17:25 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA02E4A9EA_at_JAXMSG01.crowley.com>
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;
EVENT TIME_WAITED
------------------------------ -----------
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
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MacGregor, Ian A.
Sent: Thursday, June 11, 2009 6:52 PM
To: oracle-l_at_freelists.org
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?
Ian MacGregor
SLAC National Accelerator Center
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Joel.Patterson_at_crowley.com
Sent: Thursday, June 11, 2009 12:41 PM
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 12 2009 - 08:17:25 CDT