cursor: pin S

declare
v_sql varchar2(200);
begin
loop
v_sql :='select seq1.nextval from dual';
execute immediate v_sql;
end loop;
end;


SQL> select * from (select SAMPLE_TIME,
       SESSION_ID,  
       NAME,  
       P1,  
       P2,  
       P3  
  from v$active_session_history ash, v$event_name enm  
 where ash.event# = enm.event#)
 where rownum<10;  2    3    4    5    6    7    8    9  

SAMPLE_TIME								    SESSION_ID NAME				      P1	 P2	    P3
--------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ----------
15-APR-14 07.24.15.366 PM							    22 cursor: pin S		      3155007310   75235328	196608
15-APR-14 07.24.13.356 PM							    17 cursor: pin S		      3155007310	  2	196608
15-APR-14 07.24.09.356 PM							    22 cursor: pin S		      3155007310   75235329	589824
15-APR-14 07.24.07.346 PM							    17 cursor: pin S		      3155007310   75235328	196608
15-APR-14 07.24.04.346 PM							    17 cursor: pin S		      3155007310	  2	589824
15-APR-14 07.24.03.336 PM							    22 cursor: pin S		      3155007310   75235328	196608
15-APR-14 07.24.01.336 PM							    22 cursor: pin S		      3155007310   75235328	196608
15-APR-14 07.24.00.336 PM							    22 cursor: pin S		      3155007310	  1	589824
15-APR-14 07.23.56.326 PM							    17 cursor: pin S		      3155007310   75235330	589824

9 rows selected.

P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR’d with Mutex Sleeps

SELECT a.*, s.sql_text
  FROM v$sql s,
       (SELECT sid,
               event,
               wait_class,
               p1 cursor_hash_value,
               p2raw Mutex_value,
               TO_NUMBER (SUBSTR (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid
          FROM v$session_wait
         WHERE event LIKE 'cursor%') a
 WHERE s.HASH_VALUE = a.p1

SQL> SELECT a.*, s.sql_text
  FROM v$sql s,
       (SELECT sid,
               event,
               wait_class,
               p1 cursor_hash_value,
               p2raw Mutex_value,
               TO_NUMBER (SUBSTR (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid
          FROM v$session_wait
         WHERE event LIKE 'cursor%') a
 WHERE s.HASH_VALUE = 3155007310;  2    3    4    5    6    7    8    9   10   11  

       SID EVENT			  WAIT_CLASS							   CURSOR_HASH_VALUE MUTEX_VALUE      

HOLD_MUTEX_X_SID SQL_TEXT
---------- ------------------------------ ---------------------------------------------------------------- ----------------- ---------------- --------------

-- --------------------
      1148 cursor: pin S		  Concurrency								  3155007310 0000000000000002		     

0 select seq1.nextval from dual
当看到系统有很多session等待cursor: pin S事件的时候，要么是CPU不够快，要么是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。如果是Capacity的问题，则可以升级硬件。如果是因为SQL的并行太多，则要么想办法降低该SQL执行次数，要么将该SQL复制成N个其它的SQL
cursor: pin S A session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should rarely be seen because a shared mutex pin update is very fast.(Wait Time: Microseconds)
Parameter Description

P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR’d with Mutex Sleep