RE: Truncating tables in RAC environment

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kevin Closson Sent: Wednesday, March 15, 2006 5:41 PM
To: 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

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

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l