dm的SQL监控

1 SQL_NODE_HISTORY​​ 视图

1.1 V$SQL_NODE_HISTORY 列定义及用途​​

​​序号​​	​​列名​​	​​数据类型​​	​​说明​​
1	SEQ_NO	INTEGER	​​操作符序号​​：对应 EXPLAIN 执行计划中的行号，标识操作符在计划树中的位置。
2	EXEC_ID	BIGINT	​​执行ID​​：唯一标识SQL执行过程，关联 V$SQL_HISTORY.EXEC_ID。
3	NODE	BIGINT	​​节点ID​​：SQL执行节点的唯一标识符。
4	TYPE$	INTEGER	​​节点类型​​：标识操作符类型（如 HASH JOIN、TABLE SCAN）。
5	BYTES_DYNAMIC_ALLOCED	BIGINT	​​动态分配字节数​​：操作符执行期间动态申请的内存大小（单位：字节）。
6	BYTES_DYNAMIC_FREED	BIGINT	​​动态释放字节数​​：操作符执行期间释放的动态内存大小。
7	N_ENTER	INTEGER	​​进入次数​​：操作符被调用的次数（如嵌套循环的迭代次数）。
8	TIME_USED	INTEGER	​​耗时（微秒）​​：操作符执行总时间（关键性能指标）。
9	PLN_OP_ID	INTEGER	​​MPP操作符序号​​：MPP模式下通讯操作符的序号（非MPP环境为0）。
10	BYTES_SEND	INTEGER	​​发送字节数​​：MPP节点间发送的数据量（单位：字节）。
11	BYTES_RECV	INTEGER	​​接收字节数​​：MPP节点间接收的数据量。
12	ROWS_SEND	INTEGER	​​发送行数​​：MPP节点间发送的数据行数。
13	ROWS_RECV	INTEGER	​​接收行数​​：MPP节点间接收的数据行数。
14	BDTA_SEND	INTEGER	​​发送BDTA次数​​：MPP通信中发送BDTA（批量数据传输）的次数。
15	BDTA_RECV	INTEGER	​​接收BDTA次数​​：MPP通信中接收BDTA的次数。
16	MAL_ID	BIGINT	​​邮件标识号​​：MPP通信中使用的邮件ID。
17	MPP_EXEC_ID	BIGINT	​​MPP执行序号​​：同一MPP会话中所有节点共享的唯一执行序号。
18	HASH_USED_CELLS	BIGINT	​​哈希表使用槽数​​：哈希操作符中已使用的哈希桶数量。
19	HASH_CONFLICT	BIGINT	​​哈希冲突次数​​：哈希操作符中发生冲突的次数。
20	STASK_NO	INTEGER	​​子任务号​​：并行执行中子任务的编号。
21	THRD_NO	INTEGER	​​线程号​​：执行操作符的线程ID。
22	MEM_USED	BIGINT	​​内存使用（KB）​​：操作符消耗的内存大小。
23	DISK_USED	BIGINT	​​磁盘使用（KB）​​：操作符溢出到磁盘的数据量（如大排序）。
24	DHASH3_USED_CELLS	VARCHAR(500)	​​动态哈希表槽使用​​：动态哈希算法的调试信息（格式：use_1/all_1, use_2/all_2...）。
25	DHASH3_CONFLICT	VARCHAR(300)	​​动态哈希冲突​​：动态哈希算法的冲突详情（格式：conflict_1, conflict_2...）。
26	HASH_SAME_VALUE	BIGINT	​​哈希相同值总数​​：开启相同值优化时，哈希表中重复值的总数。
27	SORT_BUF_ROWS	BIGINT	​​排序缓冲区行数​​：排序操作符缓存的行数（关闭大内存排序时有效）。
28	SORT_BUF_AVG_ROWSIZE	BIGINT	​​排序行平均大小（字节）​​：排序操作符中数据的预估平均行长。

---

​​1.2 核心应用场景​​

1. ​​定位性能瓶颈​​

• 通过 TIME_USED 和 N_ENTER 识别高耗时操作符（如 HASH JOIN 或 SORT）。
• 若 DISK_USED > 0，表明操作符因内存不足触发磁盘I/O，需调整 SORT_BUF_SIZE 等参数。

2. MPP通信分析​​

• BYTES_SEND/RECV 和 ROWS_SEND/RECV 帮助评估MPP节点间数据倾斜。

3. ​​内存优化​​

• MEM_USED 过高时，需检查是否因统计信息不准导致哈希表过大。

1.3 使用前提

需开启监控参数：

CALL SP_SET_PARA_VALUE(1, 'ENABLE_MONITOR', 1);          -- 全局监控
CALL SP_SET_PARA_VALUE(1, 'MONITOR_SQL_EXEC', 1);       -- 记录SQL执行
CALL SP_SET_PARA_VALUE(1, 'MONITOR_TIME', 1);           -- 记录时间消耗

2 单个SQL的实时监控报告

2.1 核心参数说明

参数名​​	​​数据类型​​	​​必填​​	​​说明​​
SQL_ID	VARCHAR2	否	SQL语句的唯一标识符（从 V$SQL_HISTORY.SQL_ID 获取）
SESSION_ID	BIGINT	否	会话ID（V$SESSIONS.SESS_ID），-1 表示当前会话
SESSION_SERIAL	INT	否	会话序列号（V$SESSIONS.SESS_SEQ），与会话ID组合唯一标识会话
SQL_EXEC_ID	INT	​​关键​​	SQL执行号（消息窗口或 V$SQL_HISTORY.EXEC_ID 获取）
TYPE	VARCHAR2	否	报告格式：​​仅支持 ’TEXT’（文本格式）​​
REPORT_LEVEL	VARCHAR2	否	报告详细程度：'BASIC'（基础）/ 'TYPICAL'（典型）/ 'ALL'（全部）
START_TIME_FILTER	DATETIME	否	报告起始时间过滤（例：'2024-01-01 00:00:00'）
END_TIME_FILTER	DATETIME	否	报告结束时间过滤
INST_ID	INT	否	MPP环境下节点ID（非MPP环境忽略）

2.2 重点参数使用场景

2.2.1 通过 SQL_EXEC_ID 定位单次执行​

-- 示例：查询执行号1001的监控报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID => 1001) 
FROM DUAL;

2.2.2 通过会话信息定位​

-- 示例：查询指定会话的最后一条SQL报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
    SESSION_ID     => 12345,       -- 会话ID
    SESSION_SERIAL => 67890        -- 会话序列号
) FROM DUAL;

2.2.3 通过 SQL_ID 分析重复SQL​

-- 示例：分析高频SQL（需配合时间过滤）
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
    SQL_ID          => '8A7B3C1D', 
    START_TIME_FILTER => SYSDATE - 1,  -- 过去24小时
    END_TIME_FILTER   => SYSDATE
) FROM DUAL;

2.3 注意事项

2.3.1 前置条件

• 需开启监控参数：

SP_SET_PARA_VALUE(1, 'ENABLE_MONITOR', 1);
SP_SET_PARA_VALUE(1, 'MONITOR_SQL_EXEC', 1);

• 参数未开启时返回空报告。

2.3.2 MPP限制

MPP环境下报告仅限​​当前节点​​数据，跨节点需在各节点单独执行。

2.3.3 输出长度

建议在DISQL中设置 SET LONG 999999 避免文本截断。

2.3.4 结束

生产环境使用后可以关闭监控参数以减少开销

SP_SET_PARA_VALUE(1, 'MONITOR_SQL_EXEC', 0);

3 ET函数报告

3.1 ET报告核心参数说明

​​参数名​​	​​数据类型​​	​​说明​​
SEQ	INTEGER	​​操作符序号​​：执行计划中的步骤编号（与EXPLAIN输出一致）
OPERATOR	VARCHAR(128)	​​操作符类型​​：如CSCN2（全表扫描）、SSEK2（索引扫描）
TIME(us)	BIGINT	​​耗时（微秒）​​：该操作符执行总时间（​​最关键性能指标​​）
ROW_COUNT	BIGINT	​​处理行数​​：操作符输出/处理的数据行数
MEM_USED(KB)	BIGINT	​​内存使用​​：操作符消耗的内存大小（单位KB）
DISK_USED(KB)	BIGINT	​​磁盘使用​​：操作符溢出到磁盘的数据量（>0 表示内存不足）
N_ENTER	INTEGER	​​调用次数​​：操作符被执行的次数（如嵌套循环的迭代次数）
HASH_CONFLICT	BIGINT	​​哈希冲突次数​​：哈希连接/分组操作中的冲突次数（高值需警惕）
HASH_USED_CELLS	BIGINT	​​哈希表槽使用数​​：哈希操作中实际使用的槽位数量
SORT_BUF_ROWS	BIGINT	​​排序缓冲区行数​​：排序操作缓存的行数（反映排序规模）
BYTES_DYNAMIC_ALLOCED	BIGINT	​​动态分配内存​​：操作符临时申请的内存字节数
BYTES_DYNAMIC_FREED	BIGINT	​​动态释放内存​​：操作符释放的动态内存字节数

3.2 关键性能诊断场景

1. 识别瓶颈操作符​​

• ​​高 TIME(us) + 高 ROW_COUNT​​
  → 全表扫描（CSCN2）需检查是否缺失索引* ​​高 TIME(us) + 高 HASH_CONFLICT​​
  → 哈希连接效率低，需调整HJ_BUF_SIZE或改用嵌套循环* ​​DISK_USED > 0​​
  → 排序/哈希操作内存不足，需增大SORT_BUF_SIZE或HJ_BUF_SIZE

​​2. 内存优化依据​​

• ​​MEM_USED 持续高位​​
  → 检查统计信息准确性（DBMS_STATS.GATHER_TABLE_STATS）* ​​BYTES_DYNAMIC_ALLOCED 突增​​
  → 复杂查询需拆分或优化中间结果集

3.3 注意事项

1. ​​前置条件​​

-- 需动态开启参数（默认关闭）
CALL SP_SET_PARA_VALUE(1, 'ENABLE_MONITOR', 1);

-- 开启监控（会话级）
CALL SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC', 1);
CALL SF_SET_SESSION_PARA_VALUE('MONITOR_TIME', 1);

2. ​​执行号有效期​​

• ET报告仅保留​​最近1000条SQL​​的执行详情（受V$SQL_HISTORY视图容量限制）

3. ​​MPP环境​​

• 需在​​各节点单独执行​​ ET(EXEC_ID) 查看本地计划