ORACLE 实时TOP SESSION查询_oracle top session-优快云博客

本文链接：https://blog.youkuaiyun.com/justinhai/article/details/12951071

本文提供了一种方法查询当前运行的SESSION中消耗资源最多的SQL语句，包括CPU、内存和I/O使用情况，并通过脚本实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询目前正在运行的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