查看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;
?
<script type="text/javascript"></script>
