My Oracle Support Banner

RAC database hangs with enq: SQ - contention & latch: row cache objects & enq: US - contention (Doc ID 1484604.1)

Last updated on DECEMBER 04, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 02-Jul-2016***

Symptoms

Database hung when getting sequence next value

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Avg
  wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: SQ - contention 1,074 527,620 5.E+05 57.9 Configurat  <<<<<<

latch: row cache objects 1,643,759 238,142 145 26.1 Concurrenc  <<<<<<<

db file sequential read 6,766,919 37,162 5 4.1 User I/O
enq: US - contention 186,395 29,738 160 3.3 Other  <<<<<<<<<<<<<<
enq: TX - row lock contention 84 22,869 3.E+05 2.5 Applicatio  

                                                             Avg
                                        %Time Total Wait    wait    Waits   % DB
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
enq: SQ - contention              1,074     0    527,620  491266      0.0   57.9
latch: row cache objects      1,643,759     0    238,142     145      7.1   26.1
db file sequential read       6,766,919     0     37,162       5     29.4    4.1
enq: US - contention            186,395     0     29,738     160      0.8    3.3
enq: TX - row lock content           84     0     22,869  272256      0.0    2.5
SQL*Net message from dblin      715,751     0      2,329       3      3.1     .3
SQL*Net more data from dbl       55,339     0      1,313      24      0.2     .1
read by other session           164,790     0        961       6      0.7     .1
log file sync                   251,172     0        903       4      1.1     .1
direct path read                 76,273     0        344       5      0.3     .0


                                              % of Total Waits
                                 -----------------------------------------
                           Waits
                             4m
Event                      to 1h   =1h
-------------------------- ----- ----- ----- ----- ----- ----- ----- -----
enq: SQ - contention          66  68.3         2.9  28.8
          -------------------------------------------------------------

SQL ordered by Elapsed Time    DB/Inst: NXTCU01/NXTCU01  Snaps: 134327-134328


        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
       496,995.4         13,592         36.57   54.6     .0     .0 1stbxy633bpb8   Module: csmUMemo@cmil181 (TNS V1-V3)
select MEMO_1SQ.nextval into :b0:b1 from DUAL

        38,953.3            677         57.54    4.3     .0     .0 a4ntdaybf8cxk
Module: pcs_cdma_reseller_lisner@cmil271 (TNS V1-V3)
select WNP_TRX_HEADER_1SQ.nextval into :b0:b1 from DUAL

        30,939.3            149        207.65    3.4     .0     .0 23y994fnpkx2q
Module: julRSecur@cmil191 (TNS V1-V3)
select crm2csm_1sq.nextval into :b0:b1 from DUAL

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.