RE: Truncating tables in RAC environment
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Amir.Hameed@xxxxxxxxx>, <rjamya@xxxxxxxxx>
- Date: Wed, 15 Mar 2006 17:23:26 -0500
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
Other related posts:
