数据库版本：11.2.0.3.0

系统版本：centOS 5.7


遇到了等待事件：ASM file metadata operation

网上查到的信息说ASM file metadata operation是个内部的等待事件，该等待事件多了也没有什么解决办法和问题产生的原因。但是发现该等待事件与ksv master wait等待事件相关。

ksv master wait的等待事件信息较多，简单来说对于不是Exadata的库就是将参数 cell_offload_processing，修改为false，如下：

根据以上现象及日志体现，从oracle官方metalink中查找资料得知这是一个bug [BUG ID 1308282.1]

下面是官方metalink文档中关于这个bug的解释：

High 'ksv master wait' And 'ASM File Metadata Operation' Waits In Non-Exadata 11g

 

Symptoms

High waits for 'ksv master wait' while doing an ASM file metadata operation were reported when a data migration utility was running.   

This wait was also seen for a drop of a tablespace.

The AWR showed the top events were CPU (> 100%), with 'ASM file metadata operation' (7%).

 

Cause

Event 'KSV master wait' indicates the process on the RDBMS side is waiting for a reply from a process on the ASM side.   

In 11g, the parameter cell_offload_processing is set to TRUE.  

Although that is a parameter is not applicable for non-Exadata databases, it caused ASM to try to deliver smart-scan results.   

The issue was reported in Bug 11800170 - ASM IN KSV WAIT AFTER APPLICATION OF 11.2.0.2 GRID PSU.

After applying the workaround for this issue (see Solution below), a drop of a tablespace that used to take 13 minutes took 4 seconds.

 

Solution

The following solutions are available for non-Exadata databases:

For the quickest solution, use the workaround.  The workaround does not negatively impact non-Exadata databases. 

This parameter is to be set on the database instance.

alter system set cell_offload_processing = false;

Upgrade to 12.1, when available. OR

Apply the 11.2.0.3 patch set  OR

Apply one-off Patch 11800170, if available for your RDBMS and Grid Homes

Note:  At the time this note was written (March 2011), neither 12.1 nor 11.2.0.3 were available.

 

官方文档中给出的最快解决方式是修改oracle中的一个参数 cell_offload_processing，修改为false。

其中cell_offload_processing参数的功能说明如下：

CELL_OFFLOAD_PROCESSING
值：TRUE|FALSE
功能：启用或禁用智能扫描及其他智能存储功能
修改：可使用ALTER SESSION或ALTER SYSTEM在会话级别或系统级别动态修改