RAC database hangs with enq: SQ - contention & latch: row cache objects & enq: US - contention

(Doc ID 1484604.1)

Last updated on JULY 02, 2016

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms