RE: Rollback waiting on 'cache buffer chains' latch

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 10 Jan 2007 08:51:17 -0500
Message-ID: <FBEIIHEAOIFCBBNIIFOGAENOCJAA.mwf@rsiz.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Allen, Brandon
Sent: Tuesday, January 09, 2007 4:53 PM
To: rstauffer_at_decommunications.com; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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.

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 10 2007 - 07:51:17 CST