Parallel index creates causing blocking lock alerting

From: Rich Jesse <rjoralist2_at_society.servebeer.com>
Date: Wed, 17 Oct 2012 10:10:35 -0500 (CDT)
Message-ID: <23114cadff16d706bb33eb3c7a5cb752.squirrel_at_society.servebeer.com>



Hey all,

After upgrading to 11.2.0.3 (64-bit AIX 5.3), during index creates with DOP 2 on our dev DB, EM12c goes nuts in alerting me to blocking locks. It looks like the issue is in the DB:

There are several messages in DBA_ALERT_HISTORY with a REASON of "Session nnn is blocking 0 other sessions". If it's blocking zero sessions, then why the message? The only thing of consequence happening at the time of these messages on a Friday night in this dev DB is a slew of CREATE INDEX...PARALLEL 2 statements running sequentially (one at a time). ASH and AWR reports seem to confirm this.

The reports also show that at least one of the sessions listed in the blocking entries of DBA_ALERT_HISTORY is a PQ process from an index create. My working theory is that the PQ is conflicting with the PX coordinator or another PQ -- by design -- and that the alert is errantly being generated.

I've been working with Oracle Support on this, but as I'm having some difficulty communicating the nature of the issue, I'm hoping for ideas or at least someone else that has seen this on their 11gR2 DB.

One of the tech's suggestions is to run successive systemstate 266 dumps at a session level, but I'm not sure how that will help with debugging issues while running parallel DML.

Thoughts?

Rich

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 17 2012 - 17:10:35 CEST

Original text of this message