Re: LGWR blocking sessions

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 12 Jun 2009 09:31:57 -0500
Message-ID: <203315c10906120731t4b963169gdd5ccb18a7b8b50c_at_mail.gmail.com>



Hello Joel

    You say LGWR is blocking other sessions and I am assuming that it is blocking for some sort of locks or enqueues.

     Were you able to capture what lock does LGWR holds while blocking other sessions? I am assuming CF enqueue but can you please confirm that?

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com

On Fri, Jun 12, 2009 at 8:17 AM, <Joel.Patterson_at_crowley.com> wrote:

> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 12 2009 - 09:31:57 CDT

Original text of this message