Re: Inserts waiting on gc buffer busy on Node2. Waiting for Undo block of Node1

From: Dan Norris <dannorris_at_dannorris.com>
Date: Mon, 09 Jun 2008 20:40:14 -0600
Message-ID: <484DE98E.5080906@dannorris.com>

Ajay,

Locking is row-level, but ITLs are block-level. So, in order to delete row 3 from a block where row 1 may have been updated, Oracle would need to create a read-consistent image of that block (which may require reading UNDO from wherever it was generated) in order to complete the delete you're requesting. Until the block is "rolled back" to create the read-consistent image, Oracle wouldn't know if the change affected the row you're trying to delete or not.

Someone correct me if I'm wrong please.

Dan

Ajay Thotangare wrote:

I am deleting from a table "TABLEA" from node1. At the same time also inserting records in same table from Node2. Session on Node2 are waiting on "gc buffer busy" wait. P1 value points to Undo of Node1. TABLEA is a huge table and delete takes long time. Its not a partitioned table but has about 28 million records. delete operation will delete about 200K records.I am committing at the end of delete operation. Any idea why my inserts are waiting on Undo block of Node1 ? moreover all sessions waits on same block. All sessions points to same undo file and same undo block. what can be done in this case to resolve the problem? Any help appreciated

-- http://www.freelists.org/webpage/oracle-l Received on Mon Jun 09 2008 - 21:40:14 CDT