How to To Findout sessions or SQLs

How To Findout sessions/users/ SQL’s which consuming High CPU usage in Oracle Database. 
I been monitoring the performance of our DB server Linux using "top" command, and I got this monitor dislay: 


I think via TOAD I can specify the user that eats a lot of CPU,from where I can get it so please advice. 


top - 22:43:43 up 52 days, 1:57, 6 users, load average: 0.99, 0.49, 0.30 
Tasks: 549 total, 2 running, 547 sleeping, 0 stopped, 0 zombie 
Cpu(s): 12.8% us, 0.5% sy, 0.0% ni, 86.5% id, 0.2% wa, 0.0% hi, 0.0% si 
Mem: 8309152k total, 8225644k used, 83508k free, 3752k buffers 
Swap: 10482308k total, 1993040k used, 8489268k free, 3575124k cached 


PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND 
12778 oradev2 25 0 100 1:57.33 1.7 1165m 135m 101m R oracle 
14329 applprod 15 0 5 2:09.04 0.1 46300 7780 4204 S httpd 
12960 appldev 16 0 1 0:00.25 0.0 2620 1304 784 R top 
13945 oraprod 16 0 0 0:00.49 0.2 1119m 17m 15m S oracle 
15100 oraprod 16 0 0 0:05.16 0.9 1121m 69m 65m S oracle 
1 root 16 0 0 0:22.47 0.0 3504 508 432 S init

Taking statspack and awr reports and you can findout the top cpu usage events. Following are the some sql queries which may help you kindly check these. 


1) Get the Process ID (PID) from TOP command which consume high CPU Usages. 


So the query to get the session details (SID) from OS PID (SPID) will be as per following. 


select s.sid from v$process p, v$session s 
where s.paddr=p.addr and p.spid = (PID) ; 


3) Once we get the session ID, base on this information we can get the actual SQL statement which is causing 
HIGH CPU usage on database server. 


We can use the following query to get the actual SQL STATEMENT. 


SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE 
= (select sql_hash_value from v$session 
where SID = (SID_WITCH_CAPTURED_IN_STEP_2) ; 


--# from below query you will findout sid:- 


SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage" 
FROM v$session se, v$sesstat ss, v$statname st 
WHERE ss.statistic# = st.statistic# 
AND name LIKE '%CPU used by this session%' 
AND se.sid = ss.SID 
AND se.username IS NOT NULL 
ORDER BY value DESC; 


select 
ss.username, 
se.SID, 
VALUE/100 cpu_usage_seconds 
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 
and 
ss.status='ACTIVE' 
and 
ss.username is not null 
order by VALUE desc; 


select sql_hash_value, count(*) from v$session 
where status = 'ACTIVE' group by sql_hash_value order by 2 desc; 


select sql_text,users_executing from v$sql where hash_value = ; 
























select 
ss.username, 
se.SID, 
VALUE/100 cpu_usage_seconds 
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 
and 
ss.status='ACTIVE' 
and 
ss.username is not null 
order by VALUE desc; 






SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE 
= (select sql_hash_value from v$session 
where SID = (SID_WITCH_CAPTURED_IN_STEP_2) ; 




at this part you should give sid which has generated from previos out put 


where SID = (SID_WITCH_CAPTURED_IN_STEP_2) 
for example if sid is 423 then you should give 
where SID = 423