查看oracle数据库的使用状态



所要用到的视图：
 v$session,v$sesstat,v$statname,v$sqlarea,v$session_wait,v$process
select * from v$process;
select * from v$session;
select * from v$sqlarea;

查看每个session的CPU的占用情况：
select ss.sid,se.command,ss.value CPU,se.username,se.program
from v$sesstat ss,v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name='CPU used by this session')
and se.sid=se.sid
and ss.sid>6
order by ss.sid;

比较哪个session的CPU占用时间最多，然后查看该session的具体情况：
select s.sid,event,wait_time,w.seq#,q.sql_text
from v$session_wait w,v$session s,v$process p,v$sqlarea q
where s.paddr=p.addr and
s.sid=p.pid and
s.sql_address=q.address;

下列找出当前session中最高的logical和Physical I/O比率：
SELECT name, statistic#
FROM V$STATNAME
WHERE name IN('session logical reads','physical reads') ;
SELECT ses.sid, DECODE(ses.action,NULL,'online','batch')"User",ses.username, MAX(DECODE(sta.statistic#,12,sta.value,0))/greatest(3600*24*(sysdate-ses.logon_time),1)"Log IO/s",
       MAX(DECODE(sta.statistic#,78,sta.value,0))/greatest(3600*24*(sysdate-ses.logon_time),1)"Phy IO/s",60*24*(sysdate-ses.logon_time)"Minutes"
FROM V$SESSION ses,V$SESSTAT sta
WHERE ses.status= 'ACTIVE'
AND sta.sid= ses.sid
AND sta.statistic# IN (12,78)
GROUP BY ses.sid, ses.username,ses.action, ses.logon_time
ORDER BY SUM( DECODE(sta.statistic#,78,100*sta.value,sta.value) )/greatest(3600*24*(sysdate-ses.logon_time),1) DESC;

统计session中每个用户下cpu占用情况：
select distinct(nvl(se.username,0))"username",sum(nvl(ss.value,0))"CPU"
from v$sesstat ss,v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name='CPU used by this session')
and se.sid=se.sid
and ss.sid>6
group by se.username
order by sum(nvl(ss.value,0)) desc;