FAQ
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

Search Discussions

  • Rich Jesse at Oct 25, 2012 at 2:03 pm

    1+ week ago, I wrote:

    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.
    No one else seeing this? Oracle tells me that this is normal. Yes, I saw
    the behavior in 10.1 as well, but that version didn't feel the need to wake
    me up about it...

    [sigh] I think I preferred my old unsupported bugs rather than the
    supported ones I have now.

    Rich
  • Mark W. Farnham at Oct 25, 2012 at 3:29 pm
    If you have "a slew" of them, then why not run twice as many of them at a
    time with only one thread per create?

    The advantage of parallel <pretty much anything> is to use more resources to
    get a particular job done in less elapsed time.
    When you have a lot of jobs to do, why take on the overhead of parallel?

    If we are talking about multiple schema, and you have multiple temp areas on
    independent i/o you can create additional efficiencies there as well.
    If you have multiple indexes to create on one table, you may be able to
    accelerate that as well by creating multiple indexes on the same table in
    parallel. Now you might not get sga double dipping without playing games
    with KEEP rotation and direct read might kick in anyway, but you still might
    win from device and SAN cache.

    So lose the locks and make it all faster too.

    Good luck,

    mwf

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Rich Jesse
    Sent: Thursday, October 25, 2012 10:03 AM
    To: oracle-l@freelists.org
    Subject: Re: Parallel index creates causing blocking lock alerting

    1+ week ago, I wrote:
    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.
    No one else seeing this? Oracle tells me that this is normal. Yes, I saw
    the behavior in 10.1 as well, but that version didn't feel the need to wake
    me up about it...

    [sigh] I think I preferred my old unsupported bugs rather than the
    supported ones I have now.

    Rich

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 17, '12 at 3:34p
activeOct 25, '12 at 3:29p
posts3
users2
websiteoracle.com

2 users in discussion

Rich Jesse: 2 posts Mark W. Farnham: 1 post

People

Translate

site design / logo © 2022 Grokbase