oracle怎么数据库的使用状态,查看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;