truncate command internal behavior
- From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Oct 2006 10:34:22 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
