Session With oper EXCL is also waiting - where to now? (systemstate dump)
From: <Christopher.Taylor2_at_parallon.net>
Date: Wed, 19 Sep 2012 15:01:39 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515AA361D_at_NADCWPMSGCMS10.hca.corpad.net>
(apologies for bad formatting - I'm trying to keep it simple tho)
(pastebin is blocked, but used gist https://gist.github.com/3751878)
3-node RAC 10.2.0.4
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
Date: Wed, 19 Sep 2012 15:01:39 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515AA361D_at_NADCWPMSGCMS10.hca.corpad.net>
(apologies for bad formatting - I'm trying to keep it simple tho)
(pastebin is blocked, but used gist https://gist.github.com/3751878)
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,995,556, Time(s)0,485 2. latch: row cache objects Waits!9,036, Time(s)P,1290 3. row cache lock Waitsb9,343, Time(s)5759 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) seq316 wait_time=0 seconds since wait started=3
address¥b6bd2a0, numberÈ, tries=4 Dumping Session Wait History for 'latch: row cache objects' count=1 wait_time1 address¥b6bd2a0, numberÈ, tries=3 for 'latch: row cache objects' count=1 wait_timey3907 address¥b6bd2a0, numberÈ, tries=2 for 'latch: row cache objects' count=1 wait_timeW address¥b6bd2a0, numberÈ, tries=1 for 'latch: row cache objects' count=1 wait_timeW5695 address¥b6bd2a0, numberÈ, tries=0 for 'latch: row cache objects' count=1 wait_times654 address¥b6bd2a0, numberÈ, tries=1 for 'latch: row cache objects' count=1 wait_timeV2036 address¥b6bd2a0, numberÈ, tries=0 for 'latch: row cache objects' count=1 wait_time$8 address¥b6bd2a0, numberÈ, tries=1 for 'latch: row cache objects' count=1 wait_timeX4430 address¥b6bd2a0, numberÈ, tries=0 for 'latch: row cache objects' count=1 wait_time€267 address¥b6bd2a0, numberÈ, tries=3 for 'latch: row cache objects' count=1 wait_timea address¥b6bd2a0, numberÈ, tries=2temporary object counter: 0
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 19 2012 - 15:01:39 CDT