gistfile1.txt

(apologies for bad formatting – I'm trying to keep it simple tho)

3-node RAC 10.2.0.4

We had a "hang" last night from 2:50 to ~3:48 and Oracle was kind enough to provide me with an automated system state dump.
(the bottom of this email is where I'm confused – where do I go from here?)

Looking at the ASH and AWR reports for the 2 hour window:

I see:
1.) An SGA Resize occurred:
Buffer Cache 30,608M increases to 30,656M
Shared Pool Size 5,136M *decreases* to 5,088M


2.) Top Events:
1.	cursor: pin S wait on X, Waits=14,995,556, Time(s)=150,485
2.	latch: row cache objects Waits=219,036, Time(s)=50,1290
3.	row cache lock Waits=629,343, Time(s)=35759
4.	library cache load lock Waits=1,811, Time(s)=5,610
5.	CPU TIME Waits=null, Time(s)=5,205

---------------------------------------------------------------
System State Dump:
Finding the sessions waiting on cursor: pin S wait on X I find:
P000 (0-2,and 4-7) waiting on GET_SHRD
P001
P002
P004
P005
P006
P007

P003 is holding the oper EXCL

Now, looking at P003 it is doing this (and I see no MUTEX(s) like GET_SHRD):

P003 is waiting:
   waiting for 'latch: row cache objects' blocking sess=0x(nil) seq=14316 wait_time=0 seconds since wait started=3
                address=a5b6bd2a0, number=c8, tries=4
    Dumping Session Wait History
     for 'latch: row cache objects' count=1 wait_time=31
                address=a5b6bd2a0, number=c8, tries=3
     for 'latch: row cache objects' count=1 wait_time=793907
                address=a5b6bd2a0, number=c8, tries=2
     for 'latch: row cache objects' count=1 wait_time=57
                address=a5b6bd2a0, number=c8, tries=1
     for 'latch: row cache objects' count=1 wait_time=575695
                address=a5b6bd2a0, number=c8, tries=0
     for 'latch: row cache objects' count=1 wait_time=73654
                address=a5b6bd2a0, number=c8, tries=1
     for 'latch: row cache objects' count=1 wait_time=562036
                address=a5b6bd2a0, number=c8, tries=0
     for 'latch: row cache objects' count=1 wait_time=248
                address=a5b6bd2a0, number=c8, tries=1
     for 'latch: row cache objects' count=1 wait_time=584430
                address=a5b6bd2a0, number=c8, tries=0
     for 'latch: row cache objects' count=1 wait_time=80267
                address=a5b6bd2a0, number=c8, tries=3
     for 'latch: row cache objects' count=1 wait_time=61
                address=a5b6bd2a0, number=c8, tries=2
    temporary object counter: 0