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;
--发明那些SQL运行了大批的PARSE
select sql_text, parse_calls,