Hi All,
Oracle 19c
When I look v$lock, 1143 SID background process blocking the other sessions. oracle@ftisttamlive01 (CKPT) cause a waiting. It happen sometimes and slowing down the database. What do you recommend?
SELECT
DECODE (BLOCKING\_SESSION, NULL, NULL, 'BLOCKED') STATUS,
SID,
LMODE,
REQUEST,
CTIME DURATION,
USER,
PROGRAM,
BLOCKING\_SESSION
FROM V$LOCK L JOIN V$SESSION S USING (SID) LEFT JOIN V$SQLAREA USING (SQL\_ID)
WHERE BLOCK = 1 OR REQUEST > 0
ORDER BY STATUS;
BLOCKED 685 0 6 3 SYS oracle@ftisttamlive01 (CLMN) 1143
BLOCKED 1629 0 4 21730 SYS oracle@ftisttamlive01 (W00B) 475
BLOCKED 1407 5 6 62 SYS uwsgi@APPLINUX (TNS V1-V3) 1143
BLOCKED 1391 5 6 1 SYS ORACLE.EXE 1143
1143 2 0 1 SYS oracle@ftisttamlive01 (CKPT)
475 6 0 21730 SYS Toad.exe
1143 2 0 62 SYS oracle@ftisttamlive01 (CKPT)
1143 2 0 40 SYS oracle@ftisttamlive01 (CKPT)
SELECT
A.SESSION_ID,
A.SESSION_SERIAL#,
A.EVENT_ID,
A.EVENT,
A.WAIT_CLASS,
COUNT(*)
FROM V$ACTIVE_SESSION_HISTORY A
GROUP BY A.SESSION_ID, A.SESSION_SERIAL#, A.EVENT_ID, A.EVENT, A.WAIT_CLASS
ORDER BY 6 DESC;
475 18083 2576559565 enq: CR - block range reuse ckpt Other 997
928 30692 2900469894 virtual circuit wait Network 997
1629 22054 1629782133 enq: TX - contention Other 997
685 48970 2576559565 enq: CR - block range reuse ckpt Other 731
1391 34722 38438084 direct path write temp User I/O 692
552 47695 1781586680 undo segment extension Configuration 615
1600 36216 2576559565 enq: CR - block range reuse ckpt Other 614
965 45164 38438084 direct path write temp User I/O 579
1456 40856 38438084 direct path write temp User I/O 534
741 2983 38438084 direct path write temp User I/O 435
15 49816 38438084 direct path write temp User I/O 378
538 54865 38438084 direct path write temp User I/O 369
554 58482 38438084 direct path write temp User I/O 362
986 12091 38438084 direct path write temp User I/O 341
766 6624 38438084 direct path write temp User I/O 337
1008 11119 38438084 direct path write temp User I/O 334
1223 13785 38438084 direct path write temp User I/O 315
1407 12434 38438084 direct path write temp User I/O 282
269 2165 38438084 direct path write temp User I/O 281
SELECT
A.EVENT_ID,
A.EVENT,
A.WAIT_CLASS,
COUNT(*)
FROM V$ACTIVE_SESSION_HISTORY A
GROUP BY A.EVENT_ID, A.EVENT, A.WAIT_CLASS
ORDER BY 4 DESC;
1328744198 log file sync Commit 21780
8220
38438084 direct path write temp User I/O 8158
2576559565 enq: CR - block range reuse ckpt Other 2345
4205197519 enq: KO - fast object checkpoint Application 1938
4093028837 SQL*Net message from dblink Network 1328
2900469894 virtual circuit wait Network 1168
1629782133 enq: TX - contention Other 1009
2093619153 direct path sync User I/O 821
Thanks in advance