Boost your ODI jobs!

We have a lot of databases running many thousands of ODI (Oracle Data Integrator) jobs. The more jobs/sessions, the more our database holding ODI repository got saturated/busy. The CPU is starving and there are a lot of buffer gets…

After we analyzed some of the busiest databases, we decided to create a couple of indexes for the following statements regarding ODI repository & OJC:

– SQL ID: bk9b5u6zyvy59

SELECT count(*) RUNNING_SESSIONS
from SNP_SESSION
WHERE SESS_STATUS = :1 and AGENT_NAME=:2

-> index on SNP_SESSION(SESS_STATUS, AGENT_NAME)

– SQL ID: abqnw8x0bb0zs

Select AGENT_NAME, CONTEXT_CODE, FIRST_DATE, FIRST_USER, I_TXT_SESS_MESS,
I_TXT_SESS_PARAMS, LAST_DATE, LAST_USER, LOG_LEVEL, MASTER_AGENT_NAME, NB_CHILD_KO,
NB_CHILD_OK, NB_CHILD_RUN, NB_DEL, NB_ERR, NB_INS, NB_ROW, NB_UPD, PARENT_SESS_NO,
SCEN_NAME, SCEN_VERSION, SESS_BEG, SESS_DUR, SESS_END, SESS_KEYWORDS, SESS_NAME,
SESS_NO, SESS_RC, SESS_STATUS, SYNCHRO, THREAD_CREATION, THREAD_ID, USER_NAME
from SNP_SESSION where SESS_STATUS='Q' and( SYNCHRO='0' or SYNCHRO is null ) and ( MASTER_AGENT_NAME is
not null and MASTER_AGENT_NAME in
(SELECT distinct SESS2.MASTER_AGENT_NAME
FROM SNP_SESSION SESS2
WHERE SESS2.AGENT_NAME=:1 ) ) order by SESS_NO

-> index on SNP_SESSION(AGENT_NAME, MASTER_AGENT_NAME)

– SQL ID: 8aq4wbhjtccd7

SELECT SESS_NO, SESS_NAME, USER_NAME, AGENT_NAME, SESS_BEG, SESS_END, SESS_RC,
SNP_EXP_TXT.TXT SESS_MESS, SESS_STATUS
FROM SNP_SESSION, SNP_EXP_TXT
WHERE SNP_EXP_TXT.I_TXT=SNP_SESSION.I_TXT_SESS_MESS AND SNP_EXP_TXT.TXT_ORD=0 AND
SESS_STATUS =:1 AND LOG_LEVEL <=:2 UNION
SELECT SESS_NO, SESS_NAME, USER_NAME, AGENT_NAME, SESS_BEG, SESS_END, SESS_RC, ''
SESS_MESS, SESS_STATUS
FROM SNP_SESSION
WHERE (SNP_SESSION.I_TXT_SESS_MESS IS NULL OR SNP_SESSION.I_TXT_SESS_MESS=0) AND
SESS_STATUS =:3 AND LOG_LEVEL <=:4 order by SESS_NO DESC

-> index on SNP_SESSION(SESS_STATUS)

– SQL ID: 6x3zjv6v5ahdd

Select AGENT_NAME, CONTEXT_CODE, FIRST_DATE, FIRST_USER, I_TXT_SESS_MESS,
I_TXT_SESS_PARAMS, LAST_DATE, LAST_USER, LOG_LEVEL, MASTER_AGENT_NAME, NB_CHILD_KO,
NB_CHILD_OK, NB_CHILD_RUN, NB_DEL, NB_ERR, NB_INS, NB_ROW, NB_UPD, PARENT_SESS_NO,
SCEN_NAME, SCEN_VERSION, SESS_BEG, SESS_DUR, SESS_END, SESS_KEYWORDS, SESS_NAME,
SESS_NO, SESS_RC, SESS_STATUS, SYNCHRO, THREAD_CREATION, THREAD_ID, USER_NAME
from SNP_SESSION
where PARENT_SESS_NO=:1

-> index on SNP_SESSION(PARENT_SESS_NO)

– SQL ID: 2dvq03ausq59s

SELECT ODI_JC_JOBS.JOB_ID
FROM ODI_JC_JOB_JOBGROUP_LINKS JOIN ODI_JC_JOBS ON ODI_JC_JOBS.JOB_ID = ODI_JC_JOB_JOBGROUP_LINKS.JOB_ID
WHERE ODI_JC_JOB_JOBGROUP_LINKS.JOBGROUP_ID = :B3 AND ODI_JC_JOB_JOBGROUP_LINKS.ACTIVITY_STATUS = 'A' AND
ODI_JC_JOBS.ACTIVITY_STATUS = 'A'
AND NOT EXISTS (SELECT 1
FROM ODI_JC_RUNTIME_JOB
WHERE ODI_JC_RUNTIME_JOB.BATCH_NUMBER = :B4 AND ODI_JC_RUNTIME_JOB.JOBGROUP_ID = :B3 AND ODI_JC_RUNTIME_JOB.JOB_ID =
ODI_JC_JOBS.JOB_ID AND ODI_JC_RUNTIME_JOB.RUNTIME_STATUS = 'FINISHED')
AND NOT EXISTS (SELECT 1
FROM ODI_JC_RUNTIME_JOB
WHERE ODI_JC_RUNTIME_JOB.BATCH_NUMBER = :B4 AND ODI_JC_RUNTIME_JOB.JOBGROUP_ID = :B3 AND ODI_JC_RUNTIME_JOB.JOB_ID =
ODI_JC_JOBS.JOB_ID AND ODI_JC_RUNTIME_JOB.HISTORY_INDICATOR = 'N' AND ODI_JC_RUNTIME_JOB.RUNTIME_STATUS IN ('RUNNING',
'QUEUED'))
AND NOT EXISTS (SELECT 1
FROM ODI_JC_RUNTIME_JOB
WHERE ODI_JC_RUNTIME_JOB.BATCH_NUMBER = :B4 AND ODI_JC_RUNTIME_JOB.JOBGROUP_ID = :B3 AND ODI_JC_RUNTIME_JOB.JOB_ID =
ODI_JC_JOBS.JOB_ID AND ODI_JC_RUNTIME_JOB.HISTORY_INDICATOR = 'N' AND ODI_JC_RUNTIME_JOB.RUNTIME_STATUS LIKE '%ERROR%'
AND NOT(ODI_JC_JOBS.RERUN_AFTER_ERROR_AUTO = 'Y' AND ODI_JC_RUNTIME_JOB.RETRY_NO < :B6 AND ODI_JC_RUNTIME_JOB.FINISHED
+ (:B5 * (1/24/60/60)) < SYSTIMESTAMP AND REGEXP_LIKE(ODI_JC_RUNTIME_JOB.ODI_RETURN_MSG ,
ODI_JC_JOBS.RERUN_AFTER_ERROR_REGEXP, ODI_JC_JOBS.RERUN_AFTER_ERROR_REGEXP_MATCH)) )
AND NOT EXISTS (SELECT 1
FROM ODI_JC_JOB_DEPENDENCIES JD JOIN ODI_JC_JOB_JOBGROUP_LINKS JJL ON JD.JOB_ID = JJL.JOB_ID AND JJL.JOBGROUP_ID = :B3
AND JJL.ACTIVITY_STATUS = 'A' JOIN ODI_JC_JOBS J ON J.JOB_ID = JJL.JOB_ID AND J.ACTIVITY_STATUS = 'A'
WHERE JD.DEPENDENT_JOB_ID = ODI_JC_JOBS.JOB_ID
AND NOT EXISTS (SELECT 1
FROM ODI_JC_RUNTIME_JOB
WHERE ODI_JC_RUNTIME_JOB.BATCH_NUMBER = :B4 AND ODI_JC_RUNTIME_JOB.JOBGROUP_ID = :B3 AND
ODI_JC_RUNTIME_JOB.RUNTIME_STATUS = 'FINISHED' AND ODI_JC_RUNTIME_JOB.JOB_ID = J.JOB_ID))
AND :B2 + ODI_JC_JOBS.JOB_WEIGHT <= :B1
ORDER BY ODI_JC_JOBS.JOB_PRIORITY

-> index on ODI_JC_JOB_JOBGROUP_LINKS(JOBGROUP_ID, ACTIVITY_STATUS) and on ODI_JC_JOB_DEPENDENCIES(dependent_job_id)

Hope it helps!