ORACLE实时TOP SESSION查询

查询目前正在运行的SESSION的TOPSQL，不是很准，但是够用，感觉比从ASH里面查更直观一点，

一直想结合OS 的ps 命令写个脚本直接找出TOP CPU 进程的SQL，但AIX 的ps 命令出来的CPU占用

率更TOPAS 显示差太多，阀值很不好定。

with tsql as (select  
       s.sid, s.BLOCKING_SESSION lk_sid,  s.machine,s.osuser,  S.SQL_ID,       Q.CHILD_NUMBER,
       S.EVENT,       Q.EXECUTIONS EXECS, 
       round(Q.ELAPSED_TIME / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS) / 1000 / 1000,3) ELTM,
       round(Q.BUFFER_GETS / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS),2) buffget, 
       round(Q.CPU_TIME / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS),2) CPUTM, 
       round(Q.DISK_READS / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS),2) DISKRDS, 
       case when s.lockwait is not null then substr(s.p2raw,-8)||substr(s.p3raw,-8) end lk_xid  
       ,Q.LAST_ACTIVE_TIME,Q.SQL_TEXT 
  from v$session s, v$sql q 
 where s.STATUS = 'ACTIVE' 
   AND S.SQL_ID = Q.SQL_ID 
   AND S.SQL_CHILD_NUMBER = Q.CHILD_NUMBER) 
select * from ( 
select 'cpu' item,rownum sn,a.* from ( 
select    
       sid, lk_sid, machine, osuser,    SQL_ID,       CHILD_NUMBER,       EVENT,       EXECS,
       ELTM,       buffget,       CPUTM,       DISKRDS,    lk_xid,   LAST_ACTIVE_TIME,SQL_TEXT  
  from tsql order by CPUTM desc )a ) where sn <=15     
union all 
select * from ( 
select 'ram' item,rownum sn,a.* from ( 
select    
       sid, lk_sid ,  machine, osuser,    SQL_ID,       CHILD_NUMBER,       EVENT,       EXECS,
       ELTM,       buffget,       CPUTM,       DISKRDS,   lk_xid,    LAST_ACTIVE_TIME,SQL_TEXT  
 from tsql order by buffget desc )a ) where sn <=15     
union all 
select * from ( 
select 'i/o' item,rownum sn,a.* from ( 
select    
       sid, lk_sid ,  machine, osuser,    SQL_ID,       CHILD_NUMBER,       EVENT,       EXECS,
       ELTM,       buffget,       CPUTM,       DISKRDS,   lk_xid,    LAST_ACTIVE_TIME,SQL_TEXT  
 from tsql order by DISKRDS desc )a ) where sn <=15