查看Oracle CPU情况

 --发现那些SQL运行了大量的PARSE

select sql_text, parse_calls, executions from v$sqlarea order by parse_calls desc;

--SYS的总的PARSE情况

select name, value from v$sysstat where name like 'parse count%';

--CPU空间及繁忙情况

select * from v$osstat;

--查看每个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=ss.sid

and ss.sid>6 order by CPU desc;

--比较一下哪个session的CPU使用时间最多,然后查看该Session的具体情况:

select s.sid, s.event, s.MACHINE,s.OSUSER,s.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 and

s.sql_address=q.address;

---占用CPU最高的10个Session及其SQL语句

select s.sid,w.wait_time,w.seconds_in_wait,w.state,w.wait_time_micro,w.time_remaining_micro,w.time_since_last_wait_micro,p.USERNAME,

status,server,schemaname,osuser,machine,p.terminal,p.program,logon_time,w.event,w.wait_class,tracefile,sql_text,last_active_time

from v$session_wait w, v$session s, v$process p, v$sqlarea q

where s.paddr=p.addr and

s.sid

in

( select sid from

(

select ss.sid

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=ss.sid and ss.sid>6 order by ss.value desc

)where rownum<11

)

and s.sql_address=q.address;

相关推荐