enq: SS – contention wait event

dbtut

5 years ago

“enq: SS – contention” wait event may occur in problems with temp tablespace usage in Oracle RAC databases. At the same time, SMON may be preventing the use of temp space by other processes when cleaning large processes.

With the following query, you can detect the operations that have a “enq: SS – contention” wait event.

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'

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'

Solution

Re-create the existing TEMP table space to resolve the problem. You may want to read the article named “How To Recreate Temp Tablespace”