RE: Truncating tables in RAC environment

There are a lot of effects to consider - most (if not all) of them covered by other readers by now.

When you truncate a table, any dirty blocks have to be dumped to disc (unless it's a GTT) - which requires cross-instance communication to deal with past images (PI blocks).

Any clean blocks in the buffer also have to be found and the associated buffers marked as free and put on the LRU-AUX list - whether or not its a GTT. And this action has to be propagate across the instances. In 10gR2, there is a new 'object queue' mechanism running through the buffer headers that allows this to take minimal resources - before that, the instance had to wade through the entire set of cache buffers chains for each block below the HWM. (There was supposed to be a special mechanism for small tables, but I don't think I ever noticed it).

All this work has to be repeated for any indexes on the table.

Then truncate is DDL, so cursors in the library cache referencing the object have to be invalidated, and that activity has to be propagated across instances. And if you are using GTTs, you invalidate all the cursors for anyone using that GTT - until 10.2, where you invalidate only the cursors referencing your private copy.

I think the DFS lock handles you saw may have been related to this last step - whereby local latch activity ends up as global lock activity, using locks of type Qx and Lx., (x going from A to P, or thereabouts).

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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