Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You can use the following script to access SQL information in Oracle from a PROCESS ID on the operating system.
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 | 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.
