Posted by : Arjun Lagisetty Monday, August 17, 2015

The Problem:

Let's say if you visited the DBA and he told you that

"Hey, The SQL with ID ase234x is killing my CPU, do something"

What do you do? You go back to operator and boom you see that there are 100 session running how would you narrow down which session spawned that sql with that ID.

The Solution:

Use the below SQL which takes in either SQL_ID, username of the user who is running that sql or os username who is spawned that sql to get the Session Id, step number and the task number, using this you can quickly go to the particular session in the operator and work your magic.

SELECT SUBSTR(A.ACTION, 1, INSTR(A.ACTION, '/') -1)                                                      AS SESSION_ID,
  SUBSTR(A.ACTION, INSTR(A.ACTION, '/',1,1)     +1, INSTR(A.ACTION, '/',1,2)-INSTR(A.ACTION, '/',1,1)-1) AS STEP_NB,
  SUBSTR(A.ACTION, INSTR(A.ACTION, '/',1,2)     +1, INSTR(A.ACTION, '/',1,3)-INSTR(A.ACTION, '/',1,2)-1) AS STEP_RUN_NUMBER,
  SUBSTR(A.ACTION, INSTR(A.ACTION, '/',1,3)     +1)                                                      AS TASK_ORDER_NUMBER,
  B.SQL_TEXT AS SQL_TEXT
FROM V$SESSION A,  V$SQLAREA B
WHERE A.SQL_ADDRESS=B.ADDRESS
AND A.ACTION      IS NOT NULL
AND (A.USERNAME    =''
OR A.SQL_ID        =''
OR A.OSUSER          = '') 

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty