Re: truncate command internal behavior

The comment about

    "must sweep all of the blocks in the db_cache_size to remove dirty blocks" is several years out of date. Since 8i, Oracle has had a checkpoint queue which lists all the dirty blocks in order of dirtying. So the work done in clearing dirty blocks should be minimal.

The historic problem with truncate was that Oracle had to "sweep" the cache to find all the CLEAN blocks for an object you were
truncating or dropping so that it could mark the buffer-headers as free.

In 10gR2 (as Mladen Gogala will chip in) there is a new mechanism which is supposed to eliminate this requirement. But it seems to have some not so good side effects - which is what your 'fast object reuse' wait is about. There is an objectbased  linked list running through all the buffer headers for a given object which should make this 'clean sweep' efficient - but it looks like it doesn't work properly.

The "local write wait" is there because when you truncate a table, your session (rather then DBWR) writes the table segment header block, any index segment header blocks, and any index root blocks for that table. The fact that the writes are synchronous in your session's time (rather than being left to DBWR to write later) make their direct performance impact much more dramatic than normal.

Is there any chance you could change to using global temporary tables with on commit delete rows ? This might make things less painful - though I don't know how much benefit it would give.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Harvinder Singh" <Harvinder.Singh_at_MetraTech.com> To: "oracle-l" <oracle-l_at_freelists.org> Sent: Tuesday, October 17, 2006 3:34 PM Subject: 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
--

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

--

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.4/477 - Release Date: 16/10/2006

--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 17 2006 - 10:13:56 CDT