1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cursor: Pin S Wait on X

Discussion in 'General' started by ecivgamer, Nov 13, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    How do I get list of queries sql_id that cause "Cursor: Pin S Wait on X" on database?

    Found a way to get all mutex identifiers for today:

    select mutex_identifier
    from V$MUTEX_SLEEP_HISTORY where trunc(sleep_timestamp) = trunc(sysdate)

    How do I get sql_id now?

    Anybody help?
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Please find below how do I get list of queries that caused "Cursor: Pin S Wait on X" on database.

    Now how do I get list of only the top wait for these queries?

    select distinct j1.sql_id, j1.sql_text from v$sql j1,

    (

    select distinct t1.sql_id

    from V$ACTIVE_SESSION_HISTORY t1,

    (

    select distinct mutex_identifier

    from V$MUTEX_SLEEP_HISTORY where trunc(sleep_timestamp) = trunc(sysdate)

    ) t2

    where t1.p1 = t2.mutex_identifier

    ) j2

    where j1.sql_id = j2.sql_id
     
  3. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Couldn't wait, so asked also on other discussion boards, this is the result:

     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Oracle version ?