Oracle查询活跃运行SQL却返回已完成语句的原因排查

核心原因分析 #

• ACTIVE状态的误解：Oracle中gv$session.status = 'ACTIVE'仅表示会话未处于空闲（IDLE）状态，并非等同于SQL正在执行。比如SQL Developer中，会话执行完SQL后不会立即切换为IDLE，可能保持ACTIVE状态等待下一次操作，此时sql_id仍保留上一次执行的SQL标识。
• 共享池SQL的残留：gv$sqlarea存储的是共享池中的SQL元数据，只要SQL未被老化出共享池，就会一直存在。即使会话中对应的SQL已经执行完成，gv$session.sql_id仍会指向这条已完成的SQL，导致关联后被查询出来。
• sql_exec_start未实时清空：该字段记录的是SQL执行的起始时间，SQL完成后不会被立即重置，因此排序时这些已完成的SQL仍会被纳入结果。

改进后的查询语句 #

要精准筛选正在运行的活跃SQL，可以加入更多判断条件，确保会话确实在执行SQL：

SELECT a.sql_id,
       sql_exec_start,
       a.inst_id,
       a.sid,
       a.serial#,
       a.username,
       b.sql_text
FROM gv$session a
INNER JOIN gv$sqlarea b
ON a.sql_id     = b.sql_id
AND a.inst_id = b.inst_id
WHERE a.status    = 'ACTIVE'
AND a.type      = 'USER'
AND a.sql_id IS NOT NULL
-- 过滤会话状态为执行中或等待SQL相关资源
AND a.state IN ('EXECUTING', 'WAITING')
-- 关联SQL执行视图，确保SQL处于活跃执行状态
AND EXISTS (
    SELECT 1 
    FROM gv$sql_execution e
    WHERE e.inst_id = a.inst_id
      AND e.sql_id = a.sql_id
      AND e.sql_exec_id = a.sql_exec_id
      AND e.status = 'EXECUTING'
)
ORDER BY sql_exec_start ASC;

补充说明 #

如果需要监控长运行SQL，也可以结合gv$session_longops视图，该视图专门记录运行时间超过6秒的SQL执行情况，能更精准定位正在运行的耗时语句。

内容的提问来源于stack exchange，提问作者Javi Torre