小伙伴们是不是一直使用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/cs3. 图形化工具
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 六、最佳实践建议
生产环境谨慎使用
- 监控本身消耗约5%~10%性能，避免长期开启。
- 长期保持 TYPICAL 级别，仅在诊断时临时切换会话或使用 Hint 。
- 避免全局设置 ALL，以免持续性能开销影响系统吞吐量。
阈值优化
- 调整监控阈值：ALTER SYSTEM SET "_sqlmon_threshold"=2 SCOPE=MEMORY;（单位：秒）。
结果归档
- 定期保存报告至文件系统，避免AWR清理后丢失。
复杂语句优化和依赖关系
- 优先关注执行计划中宽度最宽的操作节点（资源消耗最大）。
- AWR、ADDM 等功能需 STATISTICS_LEVEL = TYPICAL｜ALL，否则无法正常工作 。
- 若尝试设置为 BASIC 时启用了自动 SGA 管理（SGA_TARGET > 0），会报错 ORA-00830 。
扫一扫
1671
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
