Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,101
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

CELL_OFFLOAD_PROCESSING value in Non-Exadata Surrounds! — “High ‘ksv master wait’ And ‘ASM File Metadata Operation’ Wait Events

Posted by FatDBA on January 27, 2016

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.
asm1

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: