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

这是我自己平时用的一个监控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

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;
   

关于号主，姚远：

• Oracle ACE（Oracle和MySQL数据库方向）
• 华为云最有价值专家
• 《MySQL 8.0运维与优化》的作者
• 拥有 Oracle 10g、12c和19c OCM等数十项数据库认证
• 曾任IBM公司数据库部门经理
• 20+年DBA经验，服务2万+客户
• 精通C和Java，发明两项计算机专利

一个输出的例子如下：

INST_SID   USERNAME   SQL_EXEC_START        SQL_ID          SQL_TEXT                                   EVENT                                WAIT_SEC
1:1699     YUAN       +00 00:00:00.000000   4nq95bucaf3s1   select sum(l_extendedprice) / 7.0 as avg   IDLE: PX Deq: Table Q Normal         0.014754
1:730      YUAN       +00 00:00:01.000000   04pfkq1nb6tu5   select ps_partkey, sum(ps_supplycost * p   ON CPU                           0.028103
1:2909     YUAN       +00 00:00:01.000000   2w6ykk7f8apgj   select o_orderpriority, count(*) as orde   IDLE: PX Deq: Table Q Normal     0.013366
1:6778     YUAN       +00 00:00:01.000000   04pfkq1nb6tu5   select ps_partkey, sum(ps_supplycost * p   IDLE: PX Deq: Execution Msg      0.537151
1:2061     YUAN       +00 00:00:02.000000   dwr9nd8gqqrj4   select ps_partkey, sum(ps_supplycost * p   IDLE: PX Deq Credit: need buffer     0.828945
1:2180     YUAN       +00 00:00:02.000000   2jnuqfkprzgya   select o_year, sum(case when nation = 'U   direct path read                     0.000701
1:6660     YUAN       +00 00:00:03.000000   06pst1u6b434j   select * from (select l_orderkey, sum(l_   IDLE: PX Deq Credit: need buffer     0.000125
1:7021     YUAN       +00 00:00:03.000000   dkhax46cjukju   select nation, o_year, sum(amount) as su   direct path read                     0.000837
1:1578     YUAN       +00 00:00:05.000000   36vzwcqw6zr81   select * from (select c_name, c_custkey,   IDLE: PX Deq: Execution Msg          0.521907
1:2182     YUAN       +00 00:00:14.000000   7bsgdav4drm1u   select nation, o_year, sum(amount) as su   IDLE: PX Deq: Execution Msg          8.083147
1:1095     YUAN       +00 00:00:19.000000   1n4x29ku1t0zj   select * from (select s_name, count(*) a   IDLE: PX Deq: Table Q Normal       0.001494


11 rows selected.