Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Truncate performance problems

Re: Truncate performance problems

From: Jonathan Lewis <>
Date: Tue, 4 May 2004 19:02:44 +0100
Message-ID: <001801c43202$040709b0$7102a8c0@Primary>

There is an issue with truncating and dropping small tables when you have a large db_cache_size - but 30 minutes is a bit extreme.

When you drop or truncate, Oracle first writes all dirty blocks for the object to disc - probably by running the checkpoint queue.

But Oracle also has to set the STATE flag to free on all the relevant buffer headers. In theory, this could be done by checking the block address for each block in the (small) table, calculating the chain the block is on, latching it once and checking the chain. In practise, Oracle seems to walk the entire buffer cache - possibly latching every chain in turn (I can't remember whether this is quite accurate at the moment, it may take each latch just once, rather than once per chain).

For a large buffer cache, and with multiple drops going on, this could take some time.

It's just occurred to me that a similar problem could appear with the library cache latch - every time you drop an object all dependent cursors have to be invalidated, I can't remember if the same is true on a truncate.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Optimising Oracle Seminar

June 2004 UK Manchester
July 2004 Iceland
July 2004 USA California
Aug 2004 USA North Carolina
Sept 2004 UK Manchester
Sept 2004 USA NYC
Oct 2004 USA Boston

Hey All..

After several years of being away from this list, I am finally back. And sure enough.. I have a question for you all.. (Sybase got in the way of real RDBMS work) -- He he

Over the last year or so.. We've been dealing with an issue where truncating a table can take on the upwards of half an hour, or longer. These are small-ish tables.. with less than 100 extents.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Tue May 04 2004 - 13:00:28 CDT

Original text of this message