Home > RAC > RAC Performance: DFS Lock Handle + Row Cache Lock + ENQ: SQ Contention , hanged the rac instance.

RAC Performance: DFS Lock Handle + Row Cache Lock + ENQ: SQ Contention , hanged the rac instance.

Symptoms:

Alert log says WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

Wait events in Node A: DFS Lock Handle and hanged

Wait events in Node B: row cache locks and library cache locks (sessions permitted but very slow)

AWR reports says for the interesting period: DFS Lock Handle

Background:- DFS Lock Handle

DFS stands for distributed file system is an ancient name, associated with cluster file system operations, in a Lock manager supplied by vendors in Oracle Parallel Server Environment (prior name for RAC). But, this wait event has morphed and is now associated with waits irrelevant to database files also.

This will occur in RAC environment, possible with sequences especially when you have sequences + cache + Ordered set.

Means created like this, create sequence s1 min value 1 cache 20 order;

As RAC is multi instance environment, the values of the sequences need to be synchronized as they are need to be ordered.

Simulation of the issue:-

For example consider this sequence of sessions and their possible waits while accessing sequence next value:-

Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)

Session 2 on node-A: nextval –> 102

Session 1 on node-B: nextval -> 103 (DFS Lock handle)

Session 1 on node-B: nextval –> 104

Session 1 on node-A: nextval -> 105 (DFS Lock handle)

Session 1 on node-A: nextval -> 106 (more selects)

Session 1 on node-A: nextval –> 998

Session 1 on node-B: nextval -> 999 (DFS Lock handle)

Session 1 on node-B: nextval -> 1000 (CR read)

If you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1″ parameter been the object_id of the sequence.

Ref:-

http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/

Troubleshooting ‘DFS lock handle’ waits « Oracle database internals

Row cache lock

In order for DDL to execute, it must acquire a row cache lock to lock the Data Dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows. The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as needed but when these requests wait and time out is when we see the row cache lock wait.

Each row cache lock will be on a specific data dictionary object. This is called the enqueue type and can be found in the v$rowcache view. In this sample select from v$rowcache you can find the enqueue types and the type of activity being performed within the dictionary cache.

Row cache lock will be associated with specific enqueue type (described in v$lock_type) on a dictionary row. Check the following view can provide you the fair idea of dictionary cache aka row cache activity. Check the note : 468334.1 for different types of rowcache locks

View: V$rowcache

In RAC Environments this can appear as the library and dictionary cache’s are global and has to be to synchronized. Further more, this event can appear if the sequences specified as nocache in RAC environments.

ENQ: SQ Contention

The SQ enqueue is the Sequence Cache enqueue (a.k.a. enq: SQ – contention) is used to serialize access to Oracle sequences . Especially when specified cache+noorder for sequences attribute in RAC environments

hence, Specify cache + order for sequences in RAC.

 

About these ads
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

BigData - Oracle-info.com

Sureshgandhi's Blog : Its time to learn BigData

Oracle Alchemist

Steve Karam's Oracle Blog

ishudba

The King's .... "Joy of Life"..

the dba diary !!!

Naga's Blog : Oracle DBA experiences

ORACLE-INFO

Sureshgandhi's Blog: It's all about Oracle..Just Learn,Apply & Share.

SureshGandhi

All about Oracle.......

All about Oracle.......

DB Optimizer

database performance, SQL tuning and data visualizatoin

jarneil

The thoughts of Jason Arneil

All Things Oracle

All about Oracle.......

Dion Cho - Oracle Performance Storyteller

We are natural born scientists

All about Oracle.......

All about Oracle.......

All about Oracle.......

Miladin Modrakovic's Blog: Oraclue

Oracle internals, debugging and undocumented features

Coskan's Approach to Oracle

What I learned about Oracle

The Arup Nanda Blog

All about Oracle.......

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

All about Oracle.......

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

All about Oracle.......

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Iordan Iotzov's DBA blog

The opinions here may not necessarily reflect that of my employers - both past and present. The comments left by the reviewers are theirs alone and may not reflect my opinion. None of the advice is warranted to be free of errors and omissions. Please use at your own risk and after thorough testing in your environment.

Doug's Oracle Blog

All about Oracle.......

Oracle related stuff

All about Oracle.......

All about Oracle.......

Charles Hooper's Oracle Notes

Miscellaneous Random Oracle Topics: Stop, Think, ... Understand

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle E-Business Suite Technology

All about Oracle.......

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

Oracle DBA - Tips and Techniques

Knowledge Is Power - Share IT ....

Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Laurent Schneider

All about Oracle.......

Kerry Osborne's Oracle Blog

All about Oracle.......

Hemant's Oracle DBA Blog

All about Oracle.......

Oracle Enterprise Manager

All about Oracle.......

Structured Data

Thoughts on: Big Data, Hadoop, Databases, Platform, Performance & Scalability

Oracle Scratchpad

Just another Oracle weblog

Karl Arao's Blog

Just another weblog about Oracle,Linux,Troubleshooting,Performance,etc..etc..

The Data Warehouse Insider

All about Oracle.......

Online Apps DBA

One Stop Shop for Apps DBA's

All about Oracle.......

Microsoft SQL Server

All about Oracle.......

Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: