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.
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 = '')
