OracleCPU使用情况查询

OracleCPU使用情况查询

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