Identify Oracle Process ID and related SQL

Identify Oracle Process ID and related SQL


--CPU used by session
SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;


--clientpid refers to PROCESS ID of the Client Process
SELECT b.sid Session_identifier,
       b.serial# Session_serial_number,
       a.spid OS_process_ID,
       b.process OS_client_process_ID
  FROM v$process a, v$session b
 WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');



       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
        15       1391 18626                    18620

Elapsed: 00:00:00.04
10:08:29 SQL> !ps -ef|grep 18626
ora112   18184 18620  0 10:08 pts/6    00:00:00 /bin/bash -c ps -ef|grep 18626
ora112   18186 18184  0 10:08 pts/6    00:00:00 grep 18626
ora112   18626 18620  0 Oct03 ?        00:00:00 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

10:08:56 SQL> !ps -ef|grep 18620
ora112   18189 18620  0 10:09 pts/6    00:00:00 /bin/bash -c ps -ef|grep 18620
ora112   18191 18189  0 10:09 pts/6    00:00:00 grep 18620
ora112   18620 18590  0 Oct03 pts/6    00:00:00 sqlplus   as sysdba
ora112   18626 18620  0 Oct03 ?        00:00:00 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

10:09:19 SQL>