RE: Truncating tables in RAC environment
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Mar 2006 10:48:37 -0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
