Oracle High CPU N High Memory Utilization

 ps -eo pcpu,pid,user,args|sort -k 1 -r|head -10
%CPU   PID     USER COMMAND
 9.3  5632    ora11 oracle<xxxx> (LOCAL=NO)
 7.4 29522    ora10 oracle<xxxx> (LOCAL=NO)
 7.3 17050    ora10 oracle<xxxx> (LOCAL=NO)
 7.2 19943    ora10 oracle<xxxx> (LOCAL=NO)
 7.2 19364    ora10 oracle<xxxx> (LOCAL=NO)
 5.0 22850   ora11p ora_ctwr_<xxxx>
 5.0 21219   ora11p ora_ctwr_<xxxx>
 4.0 18838   ora11p ora_ctwr_<xxxx>
 3.7  5832   ora11p ora_ctwr_<xxxx>

The above command lists the top CPU used processes:

Below Query gives the sid and serial for the PID

select vs.sid,vs.serial#,vs.username,vs.osuser,vs.machine,vs.program,vs.status,vs.logon_time from v$session vs, v$process vp where vs.paddr=vp.addr and vp.spid

     in (29522,17050,19943,19364);

select x.sql_text from v$session s,v$sqltext x where s.sql_address=x.address and s.sid=&sid order by sid,piece asc;

Above query gives the sql being executed by the SID passed.

Since CTWR is also consuming more CPU cy cles..a little bit googling revealed that it's because of know bug in 11.2.0.2 which can be fixed by applying a patch or upgrade to 11.2.0.3.

========================

ps -eo pmem,pcpu,pid,args|sort -n -r|head -10 --> Unix

https://delightlylinux.wordpress.com/2012/06/25/what-is-pid-and-ppid/

top
then press Op and enter..