Truncate command internal behavior

Hi,

In our application we array insert about 1000-10k rows into staging tables and then insert into application tables from this staging tables and then truncate this staging tables. All of this is done under DTC in serializable isolation level (business reasons), and we iterate this step about for 3-5M rows. Truncate command is taking about 3-4 sec and i can see the following 2 wait events in the sql_trace that takes lot of time:
local write wait
fast object reuse

If we replace the truncate with delete it works about 20-30 times better and i don't see the above waits.
So before making this application change, we would like to get more internal working of the truncate command. I did't found too much information in oracle documents on truncate command behavior when working under DTC.

Following is the extract from one of articles on truncate: "Whenever a program issues a truncate table, uses temporary tables or runs a large data purge, Oracle must sweep all of the blocks in the db_cache_size to remove dirty blocks"
It is not clear whether oracle remove all dirty buffers or just for this particular object and is this equvalent to checkpoint.

It will be great if someone can post feedback or thought about what exactly oracle do for truncate command.

Thanks
--Harvinder Received on Tue Oct 17 2006 - 09:36:52 CDT