Oracle 12c：如何获取性能最差活跃查询的详细数据及优化SQL

你的SQL可以优化，优化后能满足「获取活跃查询、包含用户名等完整字段、按成本类指标排序」的需求，具体优化点和最终SQL如下：

原SQL的不足 #

1. 缺少需求要求的用户名字段
2. v$sqlstats包含已从共享池老化的历史SQL，无法确保查询是活跃状态
3. 时间指标以微秒为单位，可读性差
4. 分页写法在Oracle 12c中可简化

优化后的SQL #

SELECT
    du.username,
    ss.sql_id,
    ss.sql_fulltext,
    ss.executions,
    -- 将微秒转换为秒，提升可读性
    ROUND(ss.elapsed_time / 1000000, 2) total_elapsed_sec,
    ROUND(ss.cpu_time / 1000000, 2) total_cpu_sec,
    ss.buffer_gets total_buffer_gets,
    ss.disk_reads total_disk_reads,
    -- 用NULLIF避免潜在除零错误
    ROUND((ss.elapsed_time / NULLIF(ss.executions, 0)) / 1000000, 2) avg_elapsed_sec,
    ROUND((ss.cpu_time / NULLIF(ss.executions, 0)) / 1000000, 2) avg_cpu_sec,
    ROUND(ss.buffer_gets / NULLIF(ss.executions, 0), 2) avg_buffer_gets,
    ROUND(ss.disk_reads / NULLIF(ss.executions, 0), 2) avg_disk_reads
FROM
    v$sqlstats ss
-- 关联获取SQL解析用户的用户名
JOIN dba_users du ON ss.parsing_user_id = du.user_id
-- 关联v$sql确保SQL仍在共享池中（活跃状态）
JOIN v$sql s ON ss.sql_id = s.sql_id
WHERE
    ss.executions > 0
-- 可选：筛选当前正在执行的查询，取消注释下面的条件
-- EXISTS (SELECT 1 FROM v$session se WHERE se.sql_id = ss.sql_id)
-- 按成本类指标排序，可替换为avg_cpu_sec/avg_buffer_gets/avg_disk_reads
ORDER BY
    avg_elapsed_sec DESC
-- Oracle 12c+简化分页语法
FETCH FIRST 25 ROWS ONLY;

关键优化说明 #

• 补充用户名信息：通过parsing_user_id关联dba_users，获取执行SQL的用户名，满足需求字段要求
• 精准锁定活跃查询：关联v$sql确保SQL未被共享池老化清除；若需要实时正在执行的查询，可启用EXISTS关联v$session的条件
• 提升可读性：将微秒时间转换为秒，计算结果更直观
• 增强鲁棒性：用NULLIF(executions, 0)避免极端场景下的除零错误
• 简化语法：使用Oracle 12c支持的FETCH FIRST 25 ROWS ONLY替代传统ROWNUM子查询，语法更简洁高效
• 灵活排序：可根据需求切换排序字段，比如按avg_cpu_sec（CPU成本）或avg_buffer_gets/avg_disk_reads（IO成本）排序

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