As to truncating a GTT, I have to ask, why? What's the point? As soon
as you commit (or disconnect, depending on how the table is defined),
the data is gone. So why bother with a truncate, ever?
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"Exception: Some dividends may be reported as qualified dividends but
are not qualified dividends. These include:
* Dividends you received on any share of stock that you held for less
than 61 days during the 121-day period that began 60 days before the
ex-dividend date. The ex-dividend date is the first date following the
declaration of a dividend on which the purchaser of a stock is not
entitled to receive the next dividend payment. When counting the number
of days you held the stock, include the day you disposed of the stock
but not the day you acquired it. See the examples below. Also, when
counting the number of days you held the stock, you cannot count certain
days during which your risk of loss was diminished. See Pub. 550 for
more details."
--IRS, Form 1040-A Instruction Booklet, Line 9b: Qualified Dividends
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Wednesday, March 15, 2006 5:23 PM
To: Amir.Hameed_at_xerox.com; rjamya_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: 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
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 15 2006 - 16:29:51 CST
