RE: Truncating tables in RAC environment

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