61-Oracle SQL Monitor-实操

原创最新推荐文章于 2026-11-10 09:31:17 发布 1.7k 阅读 AI写同款

远方1609

码龄22年墨衍会员 · AI 创作全网分发

577粉丝121篇原创

关注

Oracle 性能调优工具：SQL Monitor 阅读详情

AI摘要

本文围绕Oracle SQL Monitor展开，介绍其核心功能与价值，包括实时性能监控、瓶颈诊断与优化等。阐述技术原理，如数据采集、监控触发机制等。还说明了各版本获取方式、报告解读关键点、生成报告的方法，最后给出最佳实践建议，如生产环境谨慎使用、阈值优化等。

小伙伴们是不是一直使用ASH、AWR进行整体性能观测和排查，偶尔也需要单个sql进行监测。用sqlplus中脚本生成监控报告，有些繁琐，如果有条件直接使用SQLDEVELOPER图形界面观察（EMCC,EM24ai上直接查询更方便）。

一、SQL Monitor核心功能与价值

实时性能监控

动态捕获SQL执行细节，包括执行计划、等待事件、物理/逻辑读、CPU时间、I/O开销等。
自动监控执行时间超过5秒、并行执行或使用/*+ MONITOR */提示的SQL语句。

瓶颈诊断与优化

识别全表扫描、高资源消耗操作、锁等待等性能瓶颈。
提供执行计划可视化，对比历史执行趋势，定位性能退化原因。

多维度分析支持

历史数据分析：存储执行历史，支持性能趋势对比。
绑定变量捕获：揭示执行计划受参数影响的问题。

--参数需配置：STATISTICS_LEVEL必须来自 ALL, TYPICAL, BASIC 之间
-- 设置全局为 TYPICAL（推荐）
ALTER SYSTEM SET STATISTICS_LEVEL = 'TYPICAL';
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING';
--系统已更改

二、技术原理

数据采集层

动态性能视图：从V$SQL_MONITOR、V$SESSION等视图实时获取执行数据。
SQL Trace集成：依赖Oracle的SQL Trace机制生成原始跟踪文件，解析为结构化数据。

监控触发机制

满足任一条件即触发监控：
- 并行执行（DOP ≥ 2）
- 单次执行CPU/I/O时间 ≥ 5秒（可通过_SQLMON_THRESHOLD调整）。
- 语句包含/*+ MONITOR */提示。

存储与处理

数据暂存于SGA，通过AWR快照持久化到DBA_HIST_SQL_MONITOR。
执行计划行数默认限制300行，可通过_SQLMON_MAX_PLANLINES调整。

三、各版本SQL Monitor获取方式

1. 23ai

--提前获得sql_id,从v$sql、awr，当前会话等均可
SELECT /* 20250622_SQLMonitor*/ e.FIRST_NAME,e.JOB_ID,e.IS_PROCESSED 
FROM HR.EMPLOYEES e WHERE e.EMPLOYEE_ID>10;
--找寻SQL_ID
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* 20250622_SQLMonitor*/%';
-- 定位行锁争用源头,最近5分钟
SELECT sample_time, session_id, sql_id, blocking_session
FROM v$active_session_history
WHERE event='enq: TX - row lock contention'
  AND sample_time > SYSDATE - 5/1440;
--按照查询text中的元素
SELECT sql_id, sql_text 
FROM v$sql 
WHERE sql_text LIKE '%employees%';
--查询V$SQL视图（实时TOP SQL）
SELECT sql_id, sql_text, executions, buffer_gets, disk_reads, elapsed_time
FROM v$sql 
WHERE buffer_gets > 10000   -- 过滤高内存消耗
   OR elapsed_time > 1000000 -- 过滤长耗时
ORDER BY elapsed_time DESC;  -- 按执行时间排序

--以上查询均可查询SQL_ID,使用第一个标记定位20250622_SQLMonitor找到的SQL_ID
--
SQL_ID        PLAN_HASH_VALUE
------------- ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
7mab2mxuhbyag      1445457117
SELECT /* 20250622_SQLMonitor*/ e.FIRST_
9tb4h6mg63yn4      2316499954
SELECT /* 20250622_SQLMonitor*/ e.FIRST_
-- 生成TEXT报告
SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id       => '9tb4h6mg63yn4', 
  type         => 'TEXT', 
  report_level => 'ALL'
) FROM dual;
--
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'9TB4H6MG63YN4',TYPE=>'TEXT',REPORT_LEVEL=>'ALL')
____________________________________________________________________________________________
SQL Monitoring Report

-- 生成ACTIVE报告（提前联网下载对应的文件，加载Flash动态元素形成动态报告）
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id       => '9tb4h6mg63yn4', 
  type         => 'ACTIVE'
) FROM dual;
--
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'9TB4H6MG63YN4',TYPE=>'ACTIVE')                                                            
_____________________________________________________________________________________________________________________________________________________________________________________________________________________________
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
  <script language="javascript" type="text/javascript">
   <!--
      var version = "23.0.0.0.0";
     var swf_base_path = "https://download.oracle.com/otn_software/omx/";

         document.write('<script language="javascript" type="text/javascript" ' +
                        'src="' + swf_base_path + 'emsaasui/emcdbms-dbcsperf/active-report/scripts/activeReportInit.js?' +
                        Math.floor((new Date()).getTime()/(7*24*60*60*1000)) +
                       '"></' + 'script>');
      -->
  </script>
 </head>
 <body onload="sendXML();">
  <script type="text/javascript">
       writeIframe();
     </script>
  <script id="fxtmodel" type="text/xml">
   <!--FXTMODEL-->
   <report db_version="23.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="3" con_name="FREEPDB1" timezone_offset="28800" dbtz_offset="0" time_at_dbtz="off" packs="2" service_type="0">
    <report_id><![CDATA[/orarep/sqlmonitor/main%3fsql_id%3d9tb4h6mg63yn4]]></report_id>
   </report>
   <!--FXTMODEL-->
  </script>
 </body>
</html>

2. 19c

-- 使用新包DBMS_SQL_MONITOR
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id       => '1vrdkx6kqbfcz',
  type         => 'HTML'
) FROM dual;
--
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(SQL_ID=>'1VRDKX6KQBFCZ',TYPE=>'HTML')
--------------------------------------------------------------------------------
<html>
  <head>
    <title> SQL Monitor Report </title>
    <style type="text/cs

3. 图形化工具

Enterprise Manager (EM)

路径：Performance > SQL Monitoring > Monitored SQL Executions 。

SQL Developer

路径：Tools > Monitor SQL 。

四、报告解读关键点

以ACTIVE报告为例（可视化最佳）：

执行计划图

颜色标识：红色表示高消耗操作（如全表扫描），绿色表示高效操作。
进度条：显示各操作实际执行时间占比。

性能指标表

指标	说明
Buffer Gets	逻辑读次数，高值可能需优化索引
Disk Reads	物理I/O次数，高值需检查内存配置
Wait Events	如db file sequential read表示索引扫描等待
CPU Time	消耗CPU时间，接近总时间说明CPU瓶颈

历史对比

对比多次执行的Rows Processed、Elapsed Time变化，识别性能退化。

五、生成报告

1. 监控指定SQL（强制启用）

SELECT /*+ MONITOR */ employee_id, SUM(salary) 
FROM hr.employees e
GROUP BY e.employee_id;

2. 生成离线ACTIVE报告

# 下载依赖脚本
mkdir -p /tmp/sqlmon
cd /tmp/sqlmon
wget http://download.oracle.com/otn_software/emviewers/scripts/{flashver.js,loadswf.js,document.js}
wget http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf
--
Length: 578 [application/x-javascript]
Saving to: ‘document.js’

document.js                      100%[=========================================================>]     578  --.-KB/s    in 0s
2025-06-22 11:31:47 (13.6 MB/s) - ‘document.js’ saved [578/578]
HTTP request sent, awaiting response... 200 OK
Length: 996875 (974K) [application/x-shockwave-flash]
Saving to: ‘sqlmonitor.swf’
sqlmonitor.swf                   100%[=========================================================>] 973.51K  1.02MB/s    in 0.9s

--
# 生成报告（替换SQL_ID）
-- 1. 启用SPOOL并指定路径
SPOOL /tmp/sqlmon/sqlmon_report.html
-- 2. 生成ACTIVE报告（依赖已下载的本地脚本）
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id      => '9tb4h6mg63yn4',
  type        => 'ACTIVE',
  base_path   => 'file:///tmp/sqlmon'  -- 本地脚本路径
) FROM dual;

-- 3. 结束SPOOL
SPOOL OFF
_________________________________________________________________________________________________________________________________________________
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf