Title: Viewing The SQL Statement Of The CPU-Intensive Oracle Processes

Author:Jayant Kulkarni (or jaykul@hotmail.com), an Oracle engineer for WIPRO Infotech in
Nariman Point Maharashtra, India.

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.

/*******************************************************************/
/* FILE         : j_cpusess.sql                                    */
/* PURPOSE      : To display the sessions and their sqltext for cpu*/
/*                intensive processes.                             */
/* AUTHOR       : Jayant R Kulkarni (WIPRO ORACLE SUPPORT).        */
/* DATE         : 14 Jan 1999                                      */
/*******************************************************************/
cl scr
prompt Displaying The CPU Intensive Oracle Processes ...
prompt
prompt
!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
/