|
Summary
Doing multiple concurrent truncates you may get a very long time to be completed. When the truncate is executed individually, they are completed fast. Monitoring the system while doing the truncates you may see the local write time wait event.
What is local write wait?
Typically DBWR has to free up some buffers when you want to read something from the disk. During this process there are chances that you will be waiting for your local buffer (i.e blocks dirtied/invalidated by your session) to be written to disk. During this time the waits are shown as local write waits.
Why concurrent truncates causes local write waits and Library Cache Pin/Locks?
Processes that involve tables being truncated and repopulated in multiple (for example in concurrent batches).
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 table, then repopulate for another usage. And then to do the poplulate/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.
Workaround
There are 2 or 3 possible workarounds.
1. If you have large buffer cache try to reduce it, it may help.
2. Second solution is to create the tables being truncated on a tablespace that has different Oracle block size than the rest of the tablespaces. By doing this, the tables will
be placed on a different buffer pool, which will improve truncate time. Having
a separate buffer pool for tables will also reduce RO enqueue
contention when multiple AE jobs are running in parallel and truncating tables.
3. Third solution is to apply Oracle patch 6844739, but rarely solves the problem
|
