Top user of Memory , IO and CPU

SELECT
‘Top Physical I/O Process’ CATEGORY,
SID,
USERNAME,
ROUND(100 * TOTAL_USER_IO/TOTAL_IO,2) PCT_USED
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_IO
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME in (‘physical reads’,’physical writes’,
‘physical reads direct’,’physical reads direct (lob)’,
‘physical writes direct’,’physical writes direct (lob)’)
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_IO
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME in (‘physical reads’,’physical writes’,
‘physical reads direct’,’physical reads direct (lob)’,
‘physical writes direct’,’physical writes direct (lob)’))
WHERE ROWNUM < 2
UNION ALL
SELECT "Top Logical I/O Process",
SID,
USERNAME,
ROUND(100 * TOTAL_USER_IO/TOTAL_IO,2) PCT_USED
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_IO
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME in ("consistent gets&","db block gets")
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_IO
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME in ("consistent gets&","db block gets"))
WHERE ROWNUM < 2
UNION ALL
SELECT "Top Memory Process",
SID,
USERNAME,
ROUND(100 * TOTAL_USER_MEM/TOTAL_MEM,2)
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_MEM
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME in ("session pga memory","session uga memory")
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_MEM
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME in ("session pga memory","session uga memory") )
WHERE ROWNUM < 2
UNION ALL
SELECT 'Top CPU Process',
SID,
USERNAME,
ROUND(100 * TOTAL_USER_CPU/GREATEST(TOTAL_CPU,1),2)
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_CPU
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME = "CPU used by this session"
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_CPU
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME = "CPU used by this session")
WHERE ROWNUM < 2;

Advertisements

2 Responses

  1. When I copy and paste this query I get an ORA-01756 error….I’ve gone through this query several times and be damned if I can find an unterminated string….

    • can you please check again..i have modified the script..it had some special characters

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: