RE: truncate command internal behavior

First, thanks JL for keeping it crystal clear what the wait is for.

Second, to OP: Given the size of your iterations and your operational
"business reason" constraints my suggestion is to use multiple staging
tables in a round robin. The apparently (and confusingly so) expensive cache
"sweep" may still occur, but you should no longer be waiting for it. Thus
you should be able to enjoy the abbreviated undo generation of truncate as compared to delete without gating your process. My underlying presumption is that your real staging/cleaning work will take more time than the cache
"sweep" and that you have a sufficient number of CPUs (the kind that do
computations, not the insanely chosen acronym for Patches) that the cache
"sweep" isn't merely attempting to parallelize resources you don't have.

Third, a bit of curiosity: Does the time to truncate vary with the buffer cache size? That would imply a horribly blown cache search which I certainly hope is not the case. If so, you'd have to make the round robin have enough members so that real work can't wrap around faster than 5 seconds or so, meaning that to be safe you'd have to base that on your smallest batch size's "real work" time to process. Eeew! That is not sounding very good. Here's hoping that is not the problem and that they soon fix whatever the problem is... Pretty much the whole point of truncate is for it to be cheap, fast, and small in undo compared to deleting all the rows.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, October 17, 2006 11:14 AM To: oracle-l
Subject: 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



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