Session With oper EXCL is also waiting - where to now? (systemstate dump)
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Sep 2012 15:01:39 -0500
(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=2
temporary object counter: 0
--
//www.freelists.org/webpage/oracle-l
Other related posts:
