Correlating SQL statement in DB Sessions with ODI Sessions in ODI 11.1.1.7

Author : Jayant Mahto

NOTE: This support is only available for Oracle database.

When a LP is executed by an ODI agent then it creates ODI sessions for the scenario steps and sends appropriate SQL to the DB for execution, which in turn creates DB sessions. For long running jobs (or when there is a suspicion that some sessions are hanging in ODI) we need to correlate ODI sessions and the corresponding DB sessions.

This is achieved by examining the content of the ACTION column in the V$SESSION, which has the following format:

Action: <SESSION_ID>/<STEP_NB>/<STEP_RUN_NUMBER>/<TASK_ORDER_NUMBER>

SESSION_ID: ODI Session ID. It is displayed in operator log.

STEP_NB: Step number (ex: the step number in the package). It is displayed in operator log.

STEP_RUN_NUMBER: If you restart the same session then this will provide Nth run info. Note that if the LP scenario step is set to create a new session in case of failure for LP restart then this number will always show 1.

TASK_ORDER_NUMBER: Task order number for the step. It is displayed in operator log.

Finding out the SQL statement being executed in the database:

The following SQL in the database will provide all the DB sessions being executed by the user '<user_name>'. Run it on the database with DBA user and replace <user_name> with the DB user being used by ODI to create DB sessions.

select a.action, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.action is not null and
a.username='<user_name>'

/

Look into the content of the ACTION column to correlate it back to ODI sessions.

In the following example the SESSION_ID: 38420, STEP_NB: 2 and TASK_ORDER_NUMBER: 21 is in wait state. It is third re-start of the same session, marked by STEP_RUN_NUMBER.

This is the result of the SQL:

Correlating this SQL to the ODI session step in the operator: