Home » RDBMS Server » Performance Tuning » Cursor: Pin S Wait on X (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
icon5.gif  Cursor: Pin S Wait on X [message #627641] Thu, 13 November 2014 10:17 Go to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
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?
Re: Cursor: Pin S Wait on X [message #627642 is a reply to message #627641] Thu, 13 November 2014 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/search?q="Cursor%3A+Pin+S+Wait+on+X"+on+database
Re: Cursor: Pin S Wait on X [message #627644 is a reply to message #627642] Thu, 13 November 2014 10:31 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
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 

[Updated on: Thu, 13 November 2014 10:32]

Report message to a moderator

Re: Cursor: Pin S Wait on X [message #627649 is a reply to message #627644] Thu, 13 November 2014 12:12 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
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:MI:SS') 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 


Thanks for attention.
Re: Cursor: Pin S Wait on X [message #627677 is a reply to message #627649] Fri, 14 November 2014 03:44 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

https://community.oracle.com/message/12722439

Previous Topic: PGA_AGREGATE_TARGET parameter
Next Topic: Logging operation takes longer but no wait events
Goto Forum:
  


Current Time: Thu Mar 28 10:35:02 CDT 2024