I have Database Management Software Oracle 10g installed on RHEL server, In a procedure having continuous truncate statements on different-2 tables start taking time.
A smallest table having 30 records took around 1.30 Hr to truncate and this is very abnormal situation. SGA size of DB is 7G and longest wait event is "local write wait". On investigation i found a very good article on Oracle support and i am sharing conclusion of it.
Processes that involve temporary tables being truncated and repopulated in multiple, concurrent batch streams may present this situation.
The underlying problem is we have to write the object's dirty buffers to disk prior to actually truncating or dropping the object. This ensures instance recoverability and avoids a stuck recovery. It seems at first glance perfectly reasonable to simply truncate a temporary table, then repopulate for another usage. And then to do the temporary populate/truncate operations in concurrent batches to increase throughput. However, in reality the concurrent truncates get bogged down as dbwr gets busy flushing those dirty block buffers from the buffer cache.
You will see huge CI enqueue waits. The multiple truncate operations in concurrent streams absolutely kill throughput.This is specially critical with large buffers.
1. Delete rather than truncate on samll temporary tables. (First, Best and Simplest solution)
2. Create the temporary tables on a tablespace that has different Oracle block size than the rest of the tablespaces. By doing this, the temporary tables will be placed on a different buffer pool, which will improve truncate time. Having a separate buffer pool for temporary tables will also reduce RO enqueue contention when multiple AE jobs are running in parallel and truncating temp tables.
So, if the blocks to be scanned are in a separate buffer (which we can assume it will be fairly small because it will only hold small temporary table) it is likely that it will take a short period of time.