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
本文题目：HowtoToFindoutsessionsorSQLs
网页地址：
http://bjjierui.cn/article/gocgdi.html
