RE: Rollback waiting on 'cache buffer chains' latch
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Brandon.Allen@xxxxxxxxxxx>, <rstauffer@xxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Jan 2007 08:51:17 -0500
Did you ever get a solution?
Now of course the rollback really needs to finish eventually, and in this
case it is (was) holding up other work. But even if you abort the database
(and I'm not suggesting that yet) Oracle has to complete the rollback to
guarantee that the written contents in your database files is correct. When
you kill a session that is rolling back, Oracle will start another session
rolling back that transaction. At this moment I cannot remember the order in
which it completes the rollback and cleans up old entries in v$session and
v$process for the session that was killed. My notion is that it might keep
those entries around until the rollback is complete, and I don't have
confidence that looking at the killed session details represent up to date
wait information on the rollback. If you identify the session/process
completing the rollback, that should lead you to what is really going on.
The next question is whether the wait on 'cache buffer chains' represents a
condition that will cure itself given time or some variety of bug. I'm going
to interpret your statement that the rollback is waiting on 'cbc latch' as
meaning that is where it is spending a lot of time as opposed to that is the
most frequent wait that pops up when you snap a query on session waits. If
that is not right, then the rest of this is worth little...
Things you can do to maximize the chances that Oracle's software will
resolve the situation and finish the rollback sooner rather than later
should address minimizing the wait. In this case that means making as few
other requests for buffers as possible. Keep in mind it is the latch rather
than buffer space.
If you can block new session logon attempts and the start of new queries,
that might help.
If you can kill the sessions that are spinning waiting for the blocks this
rollback is holding that should reduce competition for the latch.
But all that has more to do with the session you killed rather than what is
going on with the rollback.
Before sniping something that is listed as already being in rollback I would
want a really good reason to think that might do something useful.
Let us know how you make out.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Allen, Brandon
Sent: Tuesday, January 09, 2007 4:53 PM
To: rstauffer@xxxxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Rollback waiting on 'cache buffer chains' latch
Thanks Bob. I wish it were that easy but we already killed it, and
Oracle is still in mourning:
SQL> select b.spid from v$session a, v$process b where a.paddr=b.addr
and a.sid=664;
SPID
------------
9859284
SQL> host ps -p 9859284
PID TTY TIME CMD
<no processes returned>
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stauffer, Robert G
Killing it the OS level would probably take care of it in a hurry.
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions
and other information in this message that do not relate to the official
business of this company shall be understood as neither given nor endorsed
by it.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
