SQL for Informatica metadata tables to find out long running sessions, SQL below uses metadata tables REP_TASK_INST_RUN and OPB_SESS_TASK_LOG.
You can even use it to pull information for a specific time range also, like long running sessions of last two days, Code for time range is marked in green.
This is one more instance which tells uses of informatica metadata tables.
Typically this SQL is helpful when you need to understand the Sessions which are performing poorly and need to be tweaked, Query can also be used for system testing, post production and production support activities.
SELECT RepTask.SUBJECT_AREA,RepTask.INSTANCE_NAME, TRUNC(AVG(END_TIME-START_TIME)*24, 2) SESS_RUN_HOURS, MIN(RepTask.START_TIME) START_TIME, SUM(SessTask.SRC_SUCCESS_ROWS) SRC_ROWS, SUM(SessTask.TARG_SUCCESS_ROWS) TGT_ROWS FROM INFO.REP_TASK_INST_RUN RepTask, INFO.OPB_SESS_TASK_LOG SessTask WHERE RepTask.run_err_code=0 AND (RepTask.END_TIME-RepTask.START_TIME)>= 1/24 AND RepTask.INSTANCE_ID = SessTask.INSTANCE_ID AND rownum <100 GROUP BY RepTask.INSTANCE_NAME, RepTask.SUBJECT_AREA Order By SESS_RUN_HOUR desc; AND RepTask.START_TIME >= TRUNC(SYSDATE)-2/24 --Today's bad performing Sessions
Change Schema, Code marked is subject to change