Shulou(Shulou.com)06/01 Report--

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 00 0:00.49 0.2 1119m 17m 15m S oracle

15100 oraprod 16 00 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.