| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Truncate performance problems
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.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Optimising Oracle Seminar
http://www.jlcomp.demon.co.uk/seminar.html
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.
 |
 |