"Cursor: Pin S Wait On X" Contention Mutex Sleep Reason Primarily ' kkslce [KKSCHLPIN2]'
(Doc ID 1268724.1)
Last updated on AUGUST 04, 2018
Oracle Database - Enterprise Edition - Version 10.2.0.4 and later Information in this document applies to any platform.
Sessions waiting on 'latch:library cache' and 'cursor pin S wait on X' mutexes
Application changes were made prior to the issue. Application changes involve changes to NLS_LENGTH_SEMANTICS although this is unlikely to be apparent initially, for example, whilst the length of PLSQL datatypes is fixed at compile time, anonymous PLSQL blocks have their variables defined at run time .
Database is not hung just contending as more and more sessions start waiting for the "cursor: pin S wait on X" event.
main mutex sleeps by volume were :
kkslce [KKSCHLPIN2] and kksfbc [KKSPRTLOC1]
select * from X$MUTEX_SLEEP ;
Mutex Type Location Sleeps Wait Time (ms) ------------- ------------------- -------- -------------- Cursor Pin kkslce [KKSCHLPIN2] 14384475 2132356683 ....
The cursor with all the versions is a pseudo cursor:
select sql_id, count(*) from v$sql_shared_cursor group by sql_id having count(*) > 10 order by 2;