Tales From A Lazy Fat DBA

It was a case some time back while performing the performance analysis for one of the database, i encountered a curious wait event ‘ASM file metadata operation’. After further analysis i discovered that it’s happening specially when doing operations such as a DROP TABLESPACE, or in this case when using Data Pump. Though the server was basically not doing anything and much of the CPU cycles were IDLE but there was this strange wait event on ‘ASM file metadata operation’ with high waits on ‘ksv master wait’.

Below is the graphical depiction of the situation.

Image to highlight one of the major spike during the period.

Reason:
In an Exadata surrounds this is the suitable setting, nevertheless, in a non-Exadata environment, which it was in this case, this causes performance issues to arise as processes on the RDBMS awaits on a reply from the ASM which is trying to delivery smart-scan results.

Solution:
The following solutions are available for non-Exadata databases:
1. 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;
2. Upgrade to 12.1, when available. OR
3. Apply the 11.2.0.3 patch set OR
4. Apply one-off Patch 11800170, if available for your RDBMS and Grid Homes

Hope That Helps
Prashant Dixit