脚本：监控Oracle中正在运行的SQL

数据运维 2024-04-26 Escape 手机阅读

这是一个监控Oracle中正在运行的SQL的脚本，有需要的请收藏，运行时直接复制和粘贴即可。

    col inst_sid heading "INST_ID|:SID" format a7
    col username format a10
    col machine format a12
    col sql_exec_start heading "SQL|START|D HH:MM:SS" format a11
    col sql_id format a13
    col sql_text format a40
    col event format a33
    col wait_sec heading "WAIT|(SEC)" format 99999
    set linesize 200
    <br>
    select ses.inst_id||chr(58)||ses.sid as inst_sid
    ,username
    ,(sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start
    ,ses.sql_id
    ,substr(sql.sql_text,1,40) sql_text
    ,substr
    (case time_since_last_wait_micro
    when 0 then (case wait_class when 'Idle' then 'IDLE: '||event else event end)
    else 'ON CPU'
    end
    ,1,33) event
    ,(case time_since_last_wait_micro
    when 0 then wait_time_micro
    else time_since_last_wait_micro
    end) 1000000 wait_sec
    from gv$session ses,gv$sqlstats sql
    where ses.inst_id||chr(58)||ses.sid sys_context ('USERENV','INSTANCE')||chr(58)||sys_context ('USERENV','SID')
    and username is not null
    and status='ACTIVE'
    and ses.sql_id=sql.sql_id (+)
    order by sql_exec_start,
    username,ses.sid,
    ses.sql_id;

    <br>