RE: Rollback waiting on 'cache buffer chains' latch
- To: <Andrey.Kriushin@xxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Jan 2007 16:44:07 -0700
Here is what I found in the hanganalyze output - please let me know if
it gives you any indication of how to resolve the problem. We are
planning to bounce the instance in about 30 minutes and hopefully that
will resolve the problem, but I'm afraid this rollback might still be
hung even after the restart:
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/664/62086/0x35a03d8/9859284/No Wait>
-- <0/683/26271/0x35e5b58/1249520/enqueue>
-- <0/905/18570/0x35e60b0/10633242/enqueue>
gbtbaan01.baan4 /baan4/admin/udump ->grep 9859284 hanganalyze
<0/664/62086/0x35a03d8/9859284/No Wait>
<0/264/52152/0x35a03d8/9859284/No Wait>
[263]/0/264/52152/0x3844980/9859284/SINGLE_NODE_NW/435/436//none
[663]/0/664/62086/0x394ca80/9859284/LEAF_NW/1141/1142//682
<0/664/62086/0x35a03d8/9859284/No Wait>
<0/264/52152/0x35a03d8/9859284/No Wait>
[263]/0/264/52152/0x3844980/9859284/SINGLE_NODE_NW/435/436//none
[663]/0/664/62086/0x394ca80/9859284/LEAF_NW/1143/1144//682
O/S info: user: bajernig, term: UNKNOWN, ospid: 9859284
OSD pid info: Unix process pid: 9859284, image: oracle@gbtbaan01
(TNS V1-V3)
O/S info: user: bajernig, term: UNKNOWN, ospid: 9859284
OSD pid info: Unix process pid: 9859284, image: oracle@gbtbaan01
(TNS V1-V3)
O/S info: user: bajernig, term: UNKNOWN, ospid: 9859284
OSD pid info: Unix process pid: 9859284, image: oracle@gbtbaan01
(TNS V1-V3)
-----Original Message-----
From: Allen, Brandon
Sent: Tuesday, January 09, 2007 4:29 PM
To: 'Andrey.Kriushin@xxxxxxxx'; oracle-l
Subject: RE: Rollback waiting on 'cache buffer chains' latch
Hi Andrey, yes we've checked v$session_wait and it seems kind of odd to
me that wait_time is always 1 and seconds_in_wait is constantly
increasing:
15:11:12 SQL> select event, p1raw, p2, state, wait_time, seconds_in_wait
from v$session_wait where sid=664;
EVENT P1RAW P2 STATE
WTime SECONDS_IN_WAIT
------------------------------ ---------------- -----------
------------------- ------ ---------------
latch free 0700000104AA4980 98 WAITED KNOWN
TIME 1 16676
15:11:43 SQL> /
EVENT P1RAW P2 STATE
WTime SECONDS_IN_WAIT
------------------------------ ---------------- -----------
------------------- ------ ---------------
latch free 0700000104AA4980 98 WAITED KNOWN
TIME 1 16850
There haven't been any complaints from PMON in the alert log or any
trace files in bdump, but it is eating up CPU like crazy, probably
spinning trying to get the 'cache buffers chains' latch.
There don't appear to be any other transactions rolling back:
15:27:23 SQL> select s.sid, t.addr, t.status, t.used_ublk,
decode(bitand(t.flag,128),0,'NO','YES') rolling_back from v$session s,
v$transaction t where s.taddr=t.addr;
SID ADDR STATUS USED_UBLK ROL
----- ---------------- ---------------- ---------- ---
648 0700000105DFA1B0 ACTIVE 24 NO
1112 0700000105E57900 ACTIVE 1 NO
1055 0700000105E81250 ACTIVE 1 NO
879 0700000105E96380 ACTIVE 1 NO
737 0700000105ECABD8 ACTIVE 1 NO
1001 0700000105F52FF8 ACTIVE 1 NO
396 0700000105F90D80 ACTIVE 17 NO
664 07000001061D98D0 ACTIVE 69 YES
815 070000010620E7B0 ACTIVE 1 NO
683 0700000106267130 ACTIVE 3 NO
905 0700000106268B20 ACTIVE 1 NO
11 rows selected.
We are currently looking through the hanganalyze dump.
Thanks!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
