ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Truncate Takes A Long Time -- Waits on RO enqueue

Truncate Takes A Long Time -- Waits on RO enqueue

原创 Linux操作系统 作者:必有我师 时间:2009-01-20 08:56:43 0 删除 编辑

问题最初由puber  freebirdsky提出,棉花糖给出了如下的解释“truncate要先做一次checkpoint”,当时颇为不解,truncate为何要做checkpoint?于是向棉花糖虚心请教,给出了下面的metalink note 286363.1,记录下!

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4
This problem can occur on any platform.
06-12-2008 Validated currency.
Symptoms
Truncate table takes long time.
SQL trace shows the wait event is on RO enqueue.
Cause
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.
There are some bugs in 10g identified and verified in an unpublished Bugs:
5575748 - TRUNCATE 20 TIMES SLOWER 11G
5177241 -Truncate can hang converting RO enqueue SSX to X
Solution
1. For 10g slow truncate issues, switch off the new feature and revert to 9i functionality model:
ALTER SYSTEM SET "_db_fast_obj_truncate"=FALSE SCOPE=BOTH;
or change the initora/pfile as appropriate.
2. If the performance is to be improved in 9i, try improving the throughput of DBWR.
Setting db_writer_processes can help.
Refer to Note 62172.1 - Understanding and Tuning Buffer Cache and DBWR, section 'Increase DBWR Throughput'.
References
Note 62172.1 - Understanding and Tuning Buffer Cache and DBWR
http://realworld.us.oracle.com/vb/notes/enqRO.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16689238/viewspace-541510/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-09

  • 博文量
    16
  • 访问量
    16212