enq: SS - contention" and "DFS lock handle" oracle event in Oracle 11g RAC

Issue Analysis on "enq: SS - contention" and "DFS lock handle" oracle event in Oracle 11g RAC

This enque is usually come for Sorting cases ( called Sort Segment issues).

In some cases you can see a "hang" situation when someone is modifying a file used for temp space.  IN these cases, the wait event for TEMP space will include:

-- This script was used to Find the blocking sessions

select INST_ID,
       sid,
       serial#,
       BLOCKING_SESSION,
       username,
       EVENT,
       status,
       BLOCKING_SESSION_STATUS "block_stat",
       program,
       sql_id
  from v$session a
 where BLOCKING_SESSION IS NOT NULL;

-- Some sample:

SID : 1712
EVENT enq: SS - contention
P1TEXT name|mode
P2TEXT tablespace #
P3TEXT dba
WAIT_CLASS# 0
WAIT_CLASS Other

-- This script was used to find the source of the TEMP usage, in this case, SS contention:

select distinct u.username,
                u.osuser,
                w.event,
                w.p2text as reason,
                ts.name as tablespace,
                nvl(ddf.file_name, dtf.file_name)
  from v$session_wait w, v$session u, v$tablespace ts
  left outer join dba_data_files ddf on ddf.tablespace_name = ts.name
  left outer join DBA_TEMP_FILES dtf on dtf.tablespace_name = ts.name
 where u.sid = w.sid
   and w.p2 = ts.TS#
   and w.event = 'enq: SS - contention';

-- Find block change tracking

select p1 "File #". p2 "Block #", p3 "Reason Code"
  from v$session_wait
 where event = 'xxx';

Note:

Next, you can find the source of the hanging contention.  Here is a complete article on getting the values from p1, p2 and p3.

P1-->The absolute file number for the data file involved in the wait.
P2-->The block number within the data file referenced in P1 that is being waited upon.
P3-->The reason code describing why the wait is occurring.


Solution I applied and working fine:

1) If you have 'n' nodes in RAC, then add 'n' number tempfiles for your TEMP tablespace.
2) Give suuficient space to tempfiles.
3) GTT use should be controlled in application.
4) Avoid un-necessary sorting, like if indexed column is used in as first in "select" and same is used by order by clause.

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.

Showing a real-time issue:

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

Session 1 on node-A: nextval -> 1001 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval –> 1002
Session 1 on node-B: nextval -> 1003 (DFS Lock handle)
Session 1 on node-B: nextval –> 1004
Session 1 on node-A: nextval -> 1005 (DFS Lock handle)
Session 1 on node-A: nextval -> 1006 (more selects)
Session 1 on node-A: nextval –> 1998
Session 1 on node-B: nextval -> 1999 (DFS Lock handle)
Session 1 on node-B: nextval -> 2000 (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.

Solution( In case of high transaction based OLTP):

Create sequences with nocache.