RE: Truncating tables in RAC environment
- From: K Gopalakrishnan <kaygopal@xxxxxxxxx>
- To: mwf@xxxxxxxx, Amir.Hameed@xxxxxxxxx, rjamya@xxxxxxxxx
- Date: Wed, 29 Mar 2006 00:14:46 +0100 (BST)
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
--- "Mark W. Farnham" <mwf@xxxxxxxx> wrote:
> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hameed, Amir
> Sent: Wednesday, March 15, 2006 12:06 PM
> To: rjamya@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of rjamya
> Sent: Wednesday, March 15, 2006 12:00 PM
> To: Hameed, Amir
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxx> 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
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> ----------------------------------------------
> Got RAC?
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
