Typically you will see a small spike in cursor: pin S wait on X or library cache lock waits.This can happen more often in OLTP envoriment. shared pool and buffer cache is in demand.
Problem will happen randomly and intermittently.

Cause

Due to frequent resize of the shared pool and buffer cache You can see cursor: pin S wait on X,library cache lock waits.

Query V$SGA_RESIZE_OPS

set linesize 90
set pagesize 60
column component format a25
column Final format 99,999,999,999
column STARTED format A25

SELECT COMPONENT ,OPER_TYPE,FINAL_SIZE Final,to_char(start_time,'dd-mon hh24:mi:ss') STARTED FROM V$SGA_RESIZE_OPS;

COMPONENT OPER_TYPE Final STARTED
------------------------- ------------- ---------------- --------------------
DEFAULT buffer cache SHRINK 17,548,967,936 10/06/2008 07:56:28

COMPONENT OPER_TYPE Final STARTED
------------------------- ------------- ---------------- --------------------
shared pool GROW 2,197,815,296 10/06/2008 07:56:28
DEFAULT buffer cache GROW 17,649,631,232 10/06/2008 07:54:25
shared pool SHRINK 2,097,152,000 10/06/2008 07:54:25

Note:-
-----
The shared pool shrinked at 7:54:25 and within 2 minutes it grew at 7:56:28
The default buffer cache grew at 7:54:25 and again shrunk at 7:56:28 . You can see resize operations every 30 seconds also.

ITPub博客

cursor: pin S wait on X or library cache lock waits

Cause

最新文章