-- #############################################################################################
--
-- %Purpose: Show SQL-Code of CPU-Intensive Oracle Prozesses in the Memory
--
-- #############################################################################################
--
-- Tuning an application involves tuning the SQL statements that are poorly
-- designed. While tuning applications, it is important for a DBA to find out which
-- SQL statements are consuming a large amount of CPU resources.
-- After tracking down these statements, the DBA can tune them to consume a less
-- CPU, improving response timings considerably. The script will work only on UNIX
-- operating systems.
--
-- It displays the top 10 CPU-intensive Oracle processes on the
-- operating system with the first column giving the %CPU used, the second column
-- unix PID, the third column USER , the fourth column TERMINAL, and the last
-- column Unix PROCESS. Enter the Unix PID at the prompt and it will display the
-- statement belonging to that process.
--
-- ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20
--
-- #############################################################################
--
column username format a10
column terminal format a9
column sql_text format a30
prompt
prompt
prompt
prompt Enter The UNIX PID :
accept PID
--
SELECT  a.username, a.terminal, a.program, b.sql_text
FROM  v$session a, v$sqlarea b, v$process c
WHERE   (c.spid = '&PID' OR a.process = '&PID')
AND   a.paddr = c.addr
AND   a.sql_address = b.address
/
