-- ############################################################################################# -- -- %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 /