Oracle high CPU usage

Below is sql statement to check CPU usage on database. You can login with any DB user and run it.

select 	nvl(ss.USERNAME,'ORACLE PROC') username,
	se.SID,ss.logon_time,
	VALUE cpu_usage,osuser
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
order  	by VALUE desc
------------------
SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
s.osuser "OS User", s.program "Program", substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and nvl(a.sql_text,'-')!='-'
--and s.sid in (418)
and p.spid in (<your OS process id on the db server by running command prstat>)

Advertisement

---