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: Truncating tables in RAC environment

RE: Truncating tables in RAC environment

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Wed, 29 Mar 2006 00:14:46 +0100 (BST)
Message-ID: <20060328231446.33092.qmail@web30908.mail.mud.yahoo.com>


Mark:

Sorry for jumping very late. I was on a short (!) vacation and just returned bay area.

The issue with GTT and truncates in RAC is very simple. In 9.2 the KGL code does not differentiate between the DDLs of the temporary table and permanent tables. So if you truncate the GTT in RAC, it locks the entire entire structure (rather locking the session's partition!) and because of the global coordination of the library cache/shared pool in RAC,it causes excessive messaging and lock conversions. If some other session is holding a lock on that object or doing truncates then we have to wait for the other session . This becomes nasty when the other session is working on the other node. I had encountered this during a benchmark and filed a bug.

I was told this is fixed in 10g and the fix is backported to 9206/7.

-Gopal

> I don't have a solution with the existing technology. This sounds
> like an
> excellent performance enhancement request to Oracle. They shouldn't
> need to
> write dirty buffers for the table being truncated, but they will need
> to be
> marked non-dirty or invalid, I think, so they don't get written out
> later.
> It would be interesting to see if the penalty varies if you read in
> and
> modify more blocks on the two nodes *not* the one from which you run
> the
> truncate. Maybe they are already doing it optimally, but a 4 second
> turn-around for 3 nodes seems high to me. If KGopal says GTT
> truncates are
> expensive, I'll take that on face value, too. But has anyone asked
> Oracle to
> look into why they are expensive and check whether they think they
> already
> have the process optimal? This sounds like one where a look-see at
> the code
> would be a heckuva lot quicker than designing tests to characterize
> the
> black box.
>
> Regards,
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Hameed, Amir
> Sent: Wednesday, March 15, 2006 12:06 PM
> To: rjamya_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: RE: Truncating tables in RAC environment
>
>
> Truncate is taking 4 seconds in RAC versus approx 1 second in non-RAC
> and there are a few tables that get truncated.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rjamya
> Sent: Wednesday, March 15, 2006 12:00 PM
> To: Hameed, Amir
> Cc: oracle-l_at_freelists.org
> Subject: Re: Truncating tables in RAC environment
>
> I don't know your definition of long time to truncate. I don't see
> much
> of a problem in a two node RAC. BTW to those interested, KGopal
> mentioned on the list some time ago that truncating GTTs is a very
> very
> expensive operation in RAC, should be avoided.
>
> Raj
>
> On 3/15/06, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
> > Folks,
> > It seems that truncating a table under RAC is more expensive than
> > truncating it under a single instance because of the dictionary
> cache
> > synchronization. We converted a single instance to a three-node RAC
> > and for those jobs that truncate tables during processing, the
> 10046
> > trace files show "DFS lock handle" wait. The tables that get
> truncated
>
> > are staging/intermediate tables and sometimes they contain a lot of
> > rows and therefore a delete command can take a long time to finish.
> On
>
> > the other hand a few seconds DFS wait is still much better than the
> > wait that a blind delete will cause. But I was wondering if there
> was
> > a better way to avoid/minimize this wait.
> >
> > Thanks
> > Amir
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> ----------------------------------------------
> Got RAC?
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 28 2006 - 17:14:46 CST

Original text of this message

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