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:

ACTION

SQL_TEXT

38420/2/3/21

update SCOTT.TARGET_EMP T set ( T.ENAME, T.JOB, T.MGR, T.HIREDATE, T.SAL, T.COMM, T.DEPTNO ) = ( select S.ENAME, S.JOB, S.MGR, S.HIREDATE, S.SAL, S.COMM, S.DEPTNO from SCOTT.I$_TARGET_EMP S where T.EMPNO =S.EMPNO ) where (EMPNO) in ( select EMPNO from SCOTT.I$_TARGET_EMP where IND_UPDATE = 'U' )

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


Comments:

I noticed this as well - when it messed up some of our DB triggers that rely on the action values!

Any idea what the meaning of the MODULE values are?

Posted by guest on September 25, 2013 at 08:05 AM PDT #

The ACTION and MODULE columns in V$SESSION tables are meant to be used by the application that creates the DB session. In this case the application is ODI agent and it stamps the appropriate values in ACTION and MODULE to the SQL statements sent to the database. It doesn't affect the values in other SQL statements of the DB sessions created by some other application and it is not clear how can it mess up your DB triggers.

The value in MODULE column is "JDBC Thin Client".

Posted by guest on November 14, 2013 at 02:53 PM PST #

Thanks for explanation on action values.

Can you please help me what are the module values mean..

Sample Module, action output from database..

MODULE ACTION
----------------------------------- -----------------------
ODI:1362461626310/525/4583007 10417525/2/1/6
ODI:1362461626310/525/4583007 10186525/2/1/7
ODI:1362461626310/525/4583007 10349525/2/1/7

Thanks

Posted by guest on April 11, 2014 at 10:13 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Oracle Blogs Admin-Oracle

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today