Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013
    Posts
    20

    Question Unanswered: Cursor: Pin S Wait on X

    Hi all,

    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

  2. #2
    Join Date
    Sep 2013
    Posts
    20
    Couldn't wait, so asked also on other discussion boards, this is the result:

    select r.* from
    (
    select distinct to_char(j2.sleep_timestamp, 'DD.MM.YYYY HH24:MIS') AS date_and_time, j1.user_io_wait_time, j1.sql_id, j1.sql_text
    from v$sql j1,
    (
    select t1.sql_id, t2.sleep_timestamp
    from V$ACTIVE_SESSION_HISTORY t1,
    (
    select mutex_identifier, sleep_timestamp
    from V$MUTEX_SLEEP_HISTORY where trunc(sleep_timestamp) = trunc(sysdate)
    ) t2
    where t1.p1 = t2.mutex_identifier
    ) j2,
    (
    select sql_id, count(*) as cnt
    from v$active_session_history
    where event = 'cursor: pin S wait on X'
    group by sql_id
    ) j3
    where j1.sql_id = j2.sql_id
    and j1.sql_id = j3.sql_id
    order by j1.user_io_wait_time desc
    ) r
    where rownum <= 10

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •