How To Access SESSION Information in Oracle from the Process ID in the Operating System

dbtut

5 years ago

You can use the following script to access SQL information in Oracle from a PROCESS ID on the operating system.

SELECT T.SQL_ID, T.SQL_TEXT
FROM GV$PROCESS P, GV$SESSION S, GV$SQLAREA T
WHERE     P.ADDR = S.PADDR
AND S.SQL_ADDRESS = T.ADDRESS
AND S.SQL_HASH_VALUE = T.HASH_VALUE
AND P.SPID = &PROCESS_ID;

SELECT T.SQL_ID, T.SQL_TEXT

FROM GV$PROCESS P, GV$SESSION S, GV$SQLAREA T

WHERE P.ADDR = S.PADDR

AND S.SQL_ADDRESS = T.ADDRESS

AND S.SQL_HASH_VALUE = T.HASH_VALUE

AND P.SPID = &PROCESS_ID;

If your Oracle database is not RAC, you can use the following query.

SELECT T.SQL_ID, T.SQL_TEXT
FROM V$PROCESS P, V$SESSION S, V$SQLAREA T
WHERE     P.ADDR = S.PADDR
AND S.SQL_ADDRESS = T.ADDRESS
AND S.SQL_HASH_VALUE = T.HASH_VALUE
AND P.SPID = &PROCESS_ID;

SELECT T.SQL_ID, T.SQL_TEXT

FROM V$PROCESS P, V$SESSION S, V$SQLAREA T

WHERE P.ADDR = S.PADDR

AND S.SQL_ADDRESS = T.ADDRESS

AND S.SQL_HASH_VALUE = T.HASH_VALUE

AND P.SPID = &PROCESS_ID;

Especially when you examine the TOP SQLs, you can instantly catch the PROCESS ID over the operating system and you can quickly understand which query has the highest CPU COST.

This script was tested in 11gR1 and 11gR2. Please test the scripts on your test servers before running them in the production environment.