RE: Truncating tables in RAC environment
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <kevinc@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>, <Mark.Bobak@xxxxxxxxxxxxxxx>
- Date: Wed, 15 Mar 2006 18:51:31 -0500
GTTs are only of interest in that they may be a quick pointer to where to
look in the code for the problem (if in fact it is a problem in the code and
not just a necessary cost of the functionality). I wonder if there is the
same problem with disposing of the GTT when it is released, but you just
don't see it because you're not waiting for it. Unless someone re-uses a GTT
in a process as an iterative scratch pad I don't see a need to truncate it,
and unless you're size constrained the elapsed time should be better with a
new GTT. (in re: Mark Bobak's note).
My note speculated on some data that would be interesting to check. I have
no evidence they are writing the dirty buffers, unless that is what the DFS
wait is for, but that would be an easy oops. I think we agree they shouldn't
need to write the buffers unless there is some subtlety I don't grok or it
is actually faster to write the dirty buffers (I'm doubting that.) Does the
excess overhead scale with the number of dirty blocks for the table being
truncated, with the number of nodes, or both? I'm asking, not telling. The
previous commenter said 1 second for a single node, 4 seconds for 3 nodes.
Whether that goes up with number of dirty blocks for the table is part of
the question, and whether it goes up at something consistent with memory
searching or something consistent with block writing is the other part of
the question.
I wonder if they have to walk the chains or if they can hash to the relevant
row block addresses (the ones up to the high water mark for that table).
That's why I mentioned that this one sounded like a look-see at the code
would be more efficient than trying to construct characterization tests.
Knowing the answers could have an impact on workflow and transaction design.
For example, though it might seem natural to truncate some table right after
a process completes, you might defer it to the start of the next time the
process is used, by which time all the relevant dirty blocks may have been
flushed in slack time. Of course on a continuously busy system that is more
total work, so deployment environment becomes a design consideration, which
is suboptimal to the design process.
I find this all interesting. I've had a warm place in my heart for truncate
table for a long time. Clearly, when truncate is appropriate it remains much
faster and less costly in resources than the corresponding unrestricted
delete operation, but it is good to understand how it operates differently
in RAC and relative to what factors the costs scale. I'm saying I don't know
the answer and I hope an Oracle lurker may take a look and tell us.
I wonder if the code path dismissing dirty blocks is the same for truncate
table as it is for drop table? It probably should be, but if it is not then
the old (ie. before truncate) means of avoiding row deletion overhead of
drop and recreate table behaves differently. Of course that has its own side
effects such as invalidating various things and having to do re-grants,
re-synonyms, and re-compilations, which is just one of the reasons for the
warm spot I have in my heart for truncate table.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Kevin Closson
Sent: Wednesday, March 15, 2006 5:41 PM
To: oracle-l@xxxxxxxxxxxxx
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
Is there evidence that the dirty buffers are being written? It
should only shoot down the buffers in each SGA. Depending on
what is going on at the time, that can be quite expensive...
certainly if there are 1 million buffers in 4 RAC instances...
That is a lot of chain walking
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
