Re: Truncate command internal behavior

harvinder76_at_gmail.com wrote:
> 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.

Well, since you are quoting Burleson, that assertion is going to be suspect (perhaps by over-generalization, if nothing else). In context, he does appear to be saying all dirty buffers. How big is your db_cache_size? Does making it smaller and testing with no other load change your results? How about testing with more dirty buffers?

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

Varies by version/config/bug. Might help if you posted yours, and perhaps a replicable demo. See bug 5177241 for some more info. Looks like Oracle is dancing around issues with CKPT/DBWR communication in 10g, the RO enqueue is used to syncronize that, which is probably where you are seeing fast object reuse waits. You might help everyone by verifying if it is an issue on the latest patchset and submitting it as a bug if so. You may have hit something new because of your serialization.

I also would like to know exactly what oracle does in a truncate. Seems like RAC complicates the issue, even for non-RAC environments. There was a time when a truncate could simply set the highwatermark and free lists to 0, but that time seems gone.

jg

--
@home.com is bogus.
http://www.beyondsecurity.com/whitepapers/SolomonEvronSept06.pdf