Ensure CELL_OFFLOAD_PROCESSING is FALSE in Non-Exadata
A colleague of mine recently brought to my attention a curious wait event that they were experiencing for 'ASM file metadata operation' which occurs when doing operations such as a DROP TABLESPACE, or in this case when using Data Pump. Though the server was basically not doing anything the CPU usage was 100% and there was the strange wait event on 'ASM file metadata operation' with high waits on 'ksv master wait'. So to Oracle Support they went via opening an SR.
It seems that upon applying a PSU to 11.2.0.2 (as of this writing PSU 2 is that latest so likely this will not occur in PSU 3 but that is only my guess) the parameter cell_offload_processing is set to TRUE. In an Exadata environment this is the appropriate setting, however, in a non-Exadata environment, which it was in this case, this causes performance issues to arise as processes on the RDBMS side await on a reply from the ASM side which is trying to delivery smart-scan results.
The quick fix is of course to simply reset the parameter to FALSE, i.e. 'ALTER SYSTEM SET cell_offload_processing = FALSE scope=both'. If you prefer you can instead apply patch 11800170. Per the MOS note, "High 'ksv master wait' And 'ASM File Metadata Operation' Waits In Non-Exadata 11g [ID 1308282.1]" this issue is fixed in 11.2.0.2 PSU 3, 11.2.0.3 and 12.1.
It seems that upon applying a PSU to 11.2.0.2 (as of this writing PSU 2 is that latest so likely this will not occur in PSU 3 but that is only my guess) the parameter cell_offload_processing is set to TRUE. In an Exadata environment this is the appropriate setting, however, in a non-Exadata environment, which it was in this case, this causes performance issues to arise as processes on the RDBMS side await on a reply from the ASM side which is trying to delivery smart-scan results.
The quick fix is of course to simply reset the parameter to FALSE, i.e. 'ALTER SYSTEM SET cell_offload_processing = FALSE scope=both'. If you prefer you can instead apply patch 11800170. Per the MOS note, "High 'ksv master wait' And 'ASM File Metadata Operation' Waits In Non-Exadata 11g [ID 1308282.1]" this issue is fixed in 11.2.0.2 PSU 3, 11.2.0.3 and 12.1.
