After upgrading to 18.104.22.168 (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.