Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: TEMPORARY TABLE

Re: TEMPORARY TABLE

From: K Gopalakrishnan <kaygopal_at_gmail.com>
Date: Thu, 28 Oct 2004 01:38:42 +0530
Message-ID: <3b0f44a1041027130859ed920b@mail.gmail.com>


Mladen:

> I must confess that I didn't react last week because I already knew that
> stuff.
> Basically, doing truncate on a GTT can cause a gorgeous shared pool latch
> deadlock that will effectively hang the whole instance.
 

This is not quite true. GTT truncate has no relation with Shared pool latch and it is not dead lock sensitive. Truncating a temporary table will require a X lock and X pin for the object (to update the object definition in the dictionary cache and library cache) and may be to update the TAB$ entry. Shared pool latch has no role in truncates.

However, this will (acquiring X lock on lib cache object and changing the data_object# after the truncates in the meta data) will invalidate all the dependent cursors in the shared pool. But not to the extent of causing a shared pool latch deadlock. Libarary cache locks are serialized by design and becasue of this there may a long queue if more than one sessions start truncating the same table..

-- 

Best Regards,
K Gopalakrishnan 
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 27 2004 - 15:04:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US