Closed
Description
In SQL described as constant GET_LAST_STEP_EXECUTION
SELECT SE.STEP_EXECUTION_ID,
SE.STEP_NAME,
SE.START_TIME,
SE.END_TIME,
SE.STATUS,
SE.COMMIT_COUNT,
SE.READ_COUNT,
SE.FILTER_COUNT,
SE.WRITE_COUNT,
SE.EXIT_CODE,
SE.EXIT_MESSAGE,
SE.READ_SKIP_COUNT,
SE.WRITE_SKIP_COUNT,
SE.PROCESS_SKIP_COUNT,
SE.ROLLBACK_COUNT,
SE.LAST_UPDATED,
SE.VERSION,
JE.JOB_EXECUTION_ID,
JE.START_TIME,
JE.END_TIME,
JE.STATUS,
JE.EXIT_CODE,
JE.EXIT_MESSAGE,
JE.CREATE_TIME,
JE.LAST_UPDATED,
JE.VERSION
from BATCH_JOB_EXECUTION JE,
BATCH_STEP_EXECUTION SE
where SE.JOB_EXECUTION_ID in (SELECT JOB_EXECUTION_ID
from BATCH_JOB_EXECUTION
where JE.JOB_INSTANCE_ID = ?)
and SE.JOB_EXECUTION_ID = JE.JOB_EXECUTION_ID
and SE.STEP_NAME = ?
order by SE.START_TIME desc, SE.STEP_EXECUTION_ID desc;
subquery (SELECT JOB_EXECUTION_ID from BATCH_JOB_EXECUTION where JE.JOB_INSTANCE_ID = ?) filters by JE.JOB_INSTANCE_ID which is outside of this subquery and as a result this subquery will scan the whole table and DB performs filtering by JOB_INSTANCE_ID at the very end.
The issue is only reproducible when you have millions of records in BATCH_JOB_EXECUTION
The fix is simply rewrite subquery and remove JE. – like this where JOB_INSTANCE_ID = ?