--发现那些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;