Hello DBA

我们都知道truncate操作会做什么：移动HWM，释放extent，重新生成data_object_id。truncate之所以比比delete快，是因为它根本不需要删除数据。但是在实际中我们经常碰到truncate很慢的情况，实际上它比我们想象的要复杂。

1.在truncate之前，CKPT必须搜索整个buffer cache，把这个object的dirty buffer全部写回磁盘（这个操作在10g有了改进，我们后面再说）。在这个过程中，系统很可能会出现DBWR的等待事件，如果dirty buffer对应的redo还没有被写入，也有可能会出现LGWR的等待事件，比如log file sync。有人说truncate之前要做一次checkpoint，并不是特别确切。

When a truncate is issued, the checkpoint process does a complete scan (till 9204) of the buffer cache.  All of the dirty buffers of the object in the buffer cache are written down to disk.  All of the clean buffers are are invalidated.

2.是否使用了reuse storage，因为需要释放空间，尤其对于字典管理的表空间。很可能会出现Waits on ST enqueue的等待事件。

3.Waits on “RO enqueue” or “CI enqueue”

The RO enqueue known as “Multiple object resue” enqueue, is used to synchronise operations
between foreground process and a background process such as DBWR or CKPT.
It is typically used when you are dropping objects or truncating tables.

When a truncate/drop is issued,

1.the foreground first acquires the “RO” enqueue in exclusive mode,
2.then cross instance calls (or one call if it is a single object) are issued
(the “CI” enqueue is acquired for cross instance call)
3.The CKPT processes on each of instances requests the DBWR to write
the dirty buffers to the disk and invalidate all the clean buffers.
4. After DBWR finishes writing, the foreground process releases the RO
enqueue.

In effect this enqueue serializes the truncate/drop operations given concurrently.

前面说过，9i在truncate之前需要扫描整个SGA找到这个object的dirty buffer，但是10g作了改进：

In Oracle 10g, a new feature was introduced in order to improve performance of operations such as drop, truncate, shrink object. This feature uses ‘object queues’ in the buffer cache infrastructure which points to a list of buffers owned by an object. In 9i, we might scan the entire buffer cache to see which buffers belong to the object is getting truncated and write its dirty buffers to disk. On environments with huge buffer caches, buffer invalidation will take long time. In 10g, the buffer invalidation process only looks at the ‘object queues’ and writes out the dirty buffers to disk. Since ‘object queues’ are short, truncate/drop/shrink operations are very fast in 10g.

这个新特性用参数 “_db_fast_obj_truncate” 来控制，这个新特性有已知的bug，必要时可以关闭。

–EOF–High School Musical 3: Senior Year movieArmy of the Dead rip