locks DB 19c

[Roshan]

Oracle DB 19c

Hello Erman,

on 11 March 2023 as from 23:00, we noticed our batch which werre running hang. I checked the alert logs and found blocking sessions.

Please find attached the trace file
awrrpt_1_12May23.html

I sent you the trace file through mail.

Should I increase the redo logfile?

Thanks,
Roshan

[Roshan]

v$log output. how can I fix . In fact we restore from RAC to standalone.

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1        563 1.0737E+10        512          1 NO
CURRENT             1.4582E+12 12-MAY-23   9.2954E+18                    0

         2          1        561 6442450944        512          1 YES
ACTIVE              1.4582E+12 12-MAY-23   1.4582E+12 12-MAY-23          0

         3          1        562 1.0737E+10        512          1 YES
ACTIVE              1.4582E+12 12-MAY-23   1.4582E+12 12-MAY-23          0


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         4          2          1 6442450944        512          1 YES
INACTIVE            1.4560E+12 05-MAY-23   1.4560E+12 05-MAY-23          0

         5          1        560 1.0737E+10        512          1 YES
ACTIVE              1.4582E+12 12-MAY-23   1.4582E+12 12-MAY-23          0

         7          2          0 1.0737E+10        512          1 YES
UNUSED                       0                      0                    0


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         8          2          0 1.0737E+10        512          1 YES
UNUSED                       0                      0                    0

[ErmanArslansOracleBlog]

No. You already have redologs which are unused.
You have to concantrate on the log file sync wait.

Also cancel these kinds of things : dbms_stats.gather_database_stats_job_proc (), take them to non-business hours.

Try to decrease the utilization of CPU and I/O Subsystem in general.. Log File sync will be increased by the increase of CPU and I/O utilization.. Frequent and concurrent commit can also increase it.. Check your env with these in mind.