oracle 查询cpu占用情况,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;