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 ------------- ---------- --------- ---------- -------------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ------------- ahp911trpvs2w 645295920 20210308 529 1930 0000 22125368 0