RAC database hangs with enq: SQ - contention & latch: row cache objects & enq: US - contention
(Doc ID 1484604.1)
Last updated on JULY 02, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform.
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: XXXXXX TNS V1-V3)
select YYYYYY.nextval into :b0:b1 from DUAL
38,953.3 677 57.54 4.3 .0 .0 a4ntdaybf8cxk
Module: XYZ@ZZZ (TNS V1-V3)
select ZZZZ.nextval into :b0:b1 from DUAL
30,939.3 149 207.65 3.4 .0 .0 23y994fnpkx2q
Module: XYZ@XXX (TNS V1-V3)
select XYZ.nextval into :b0:b1 from DUAL
Changes
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 |
| Changes |
| Cause |
| Solution |
| References |