DBA

 Problem : batch job run duration doubled from ~10 hours to ~20 hours Diag method : AWR, ASH, SQLSTAT Finding 1 : from AWR, easy to find out the time consumed mostly by sql_id='ahp911trpvs2w' Finding 2 : this sql_id is using same query plan 645295920, but performance varies. Finding 3 : ASH indicates that usually elapse time/execution is normally 0.1 seconds as in date 20210308, but jump up to 10 seconds on 20210505       ,  09:09:45 jsun> @sqlid_exec_history_sum Enter value for sql_id: ahp911trpvs2w SQL_ID              PLAN RUNDATE         EXEC START_TIME     END_TIME           BUFFER   PHY_READ     cpu(s)     elp(s)       READ     IOWAIT     DIRECT BUFFER_PER_EXEC ELPS_PER_EXEC ------------- ---------- --------- ---------- -------------- -----...

 https://tanelpoder.com/posts/log-file-switch-checkpoint-incomplete-and-lgwr-waiting-for-checkpoint-progress/ https://tanelpoder.com/snapper/