从慢查询到秒开：我在金仓KingbaseES SQL监控上的折腾一、背景咱们做数据库运维的，最怕的就是慢SQL。以前我

一、背景

咱们做数据库运维的，最怕的就是慢SQL。以前我用的土办法，就是定时去查sys_stat_statements，金仓里对应的视图，看看哪些SQL执行时间长、调用次数多。但这种方式有有问题，等你发现SQL慢了，业务已经受影响了。而且explain analyze虽然能看到执行计划，但它是在当前会话里跑一遍，跟生产环境实际跑的情况可能完全不一样。生产环境有并发、有锁等待、有资源竞争等等很多场景，这些在本地测试是模拟不出来的。

其实在金仓也内置了一套SQL监控机制。就是数据库自己有个摄像头一样的东西，可以实时把SQL的执行过程记录，比如说哪些步骤花了多少时间、用了多少IO、数据流怎么走，我们能看的清清楚楚的。

在这里插入图片描述

二、SQL监控

SQL监控跟普通的慢查询日志有啥区别？其实给友友们打个比方大家就知道了，像普通的慢查询日志就像医院的体检报告，告诉你哪项指标超标了；而SQL监控就像给病人做了全程录像的手术直播，你能看到SQL在执行过程中每一秒在干什么。我觉得这句话之后，大家对监控就有一定的了解了吧

三、SQL监控的核心功能

当SQL监视器功能处于启用状态时，若满足以下任一条件，则相应的SQL语句将被视为受控对象：

该SQL语句或PL/SQL子程序在单次执行过程中至少占用5秒的CPU时间或I/O操作时长。

SQL 语句并行执行。

SQL 语句使用hint/* +MONITOR */指定。

自动识别需要监控的SQL

金仓不是无脑监控所有SQL，那样性能开销太大。它有个智能阈值控制，默认情况下，只有满足以下条件的SQL才会被监控

实时监控与历史回溯

监控数据首先保存在内存的循环缓冲区里，默认保留最近15分钟的监控数据。如果SQL执行时间很长，你可以边执行边查，看执行到哪个阶段卡住了。这对于分析性能问题特别有用。

多维度报告生成

这是我最喜欢用的功能。比如有一次我发现一个查询在Hash Join阶段卡了20秒，仔细一看是内存不够，溢写到磁盘了。加了个work_mem参数就解决了，要是没有这个时间线的视图，根本定位不到或者很难查出来的问题。

四、SQL监控的目的是什么？

说了这么多功能，咱们得回归本质：金仓搞这么一套复杂的监控机制，到底想解决什么问题？

实时 SQL 监视功能用于定位具体语句的执行耗时问题，能够提供关于各查询执行时间及资源使用情况的详细信息。通过这种方式，管理员可以实现对具体SQL语句执行成本的有效评估。

实时 SQL 监控的场景包括：

频繁执行的 SQL 语句的执行速度比正常情况慢。

数据库会话性能降低。

并行 SQL 语句需要很长时间。

生成KWR快照花费的时间比预期的要长得多。

五、SQL监控的外部接口

接下来咱们来看看实际怎么用

5.1 SQL监控参数

参数名称	类型	说明
`sql_monitor.track`	会话级	SQL语句监控层级，ENUM类型，默认值`none` • `top`：监控顶层SQL语句 • `all`：监控嵌套层数小于64层的SQL语句 • `none`：不监控
`sql_monitor.max`	系统级	视图最大数据量，整数类型，默认值`1000`（最小值100）当统计结果达到上限时，自动移除旧数据释放存储空间，确保新SQL监控正常记录
`sql_monitor.track_options`	会话级	采集统计信息控制参数，ENUM类型，默认值`basic` • `basic`：采集语句全部统计信息和执行计划节点基本统计信息 • `full`：在`basic`基础上增加buffer及WAL统计（有性能消耗）
`sql_monitor.language`	会话级	网页版监控报告语言，TEXT类型，默认`chinese/chn` • `chinese/chn`：输出中文报告 • `english/eng`：输出英文报告

5.2 报告生成接口

主要通过DBMS_SQL_MONITOR包来生成报告，后面会详细讲。

5.3 配置参数

有几个关键参数需要了解（在kingbase.conf里配置，注意金仓的配置文件命名规范，要用sys_前缀，比如sys_monitor.conf，虽然实际主配置文件还是kingbase.conf，但自定义扩展配置建议用sys_开头）：

sys_sql_monitor.control：总开关，默认是auto，表示智能开启
sys_sql_monitor.max_plan_lines：单个SQL最多监控多少行执行计划，默认300
sys_sql_monitor.max_entries：内存中保留多少条监控记录，默认10000

修改这些参数后需要重启数据库，或者通过ALTER SYSTEM动态修改部分参数。

六、DBMS_SQL_MONITOR子程序详解

这是本文的技术核心，也是我在实际工作中用得最多的工具。DBMS_SQL_MONITOR是金仓提供的一个内置包，专门用来操作SQL监控功能。

子程序名称	说明
`SQL_MONITOR_RESET`函数	清空视图数据
`REPORT_SQL_MONITOR`函数	返回监控详细报告
`REPORT_SQL_MONITOR_LIST`函数	返回监控列表报告
`REPORT_SQL_MONITOR_TO_FILE`函数	将监控详细报告写入磁盘指定路径
`REPORT_SQL_MONITOR_LIST_TO_FILE`函数	将监控列表报告写入磁盘指定路径

6.1 MONITOR_SQL：手动标记监控

虽然金仓会自动监控慢SQL，但有时候我们需要强制监控某个SQL，不管它执行快还是慢。这时候可以用：

BEGIN
  DBMS_SQL_MONITOR.MONITOR_SQL(
    sql_id => 'abcd1234',  -- 要监控的SQL ID
    force  => TRUE          -- 强制监控
  );
END;
/

我在压测的时候经常用这个功能，想看看某个SQL在并发场景下的资源消耗细节。

6.2 REPORT_SQL_MONITOR：生成监控报告

这是最重要的函数，用法比较灵活。基本语法是：

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id       => '你的SQL_ID',
  report_level => 'ALL',      -- 详细程度：BASIC、TYPICAL、ALL
  type         => 'HTML'      -- 格式：TEXT、HTML、ACTIVE
) FROM DUAL;

参数详解：

sql_id：要查看的SQL标识。如果不知道，可以查V$SQL_MONITOR获取最近执行的SQL。
sql_exec_id：执行ID，一个SQL可能执行多次，用这个区分具体哪一次。
report_level：
- BASIC：只显示基本信息，最快
- TYPICAL：显示执行计划和统计信息，一般用这就够了
- ALL：显示所有细节，包括等待事件、并行执行细节等
type：
- TEXT：纯文本，适合命令行查看
- HTML：网页格式，带颜色高亮
- ACTIVE：交互式HTML，可以展开折叠，最直观但文件较大

实际使用技巧：

我通常会把HTML报告输出到文件，然后用浏览器打开看。在命令行可以这样操作：

\o /home/kingbase/sys_sql_report.html
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id => (SELECT sql_id FROM v$sql_monitor WHERE rownum=1),
  type   => 'HTML'
);
\o

6.3 REPORT_SQL_DETAIL：详细执行分析

这个函数比REPORT_SQL_MONITOR更详细，会分析SQL的绑定变量、执行历史、计划变化等。

SELECT DBMS_SQL_MONITOR.REPORT_SQL_DETAIL(
  sql_id    => '你的SQL_ID',
  start_time=> sysdate-1,    -- 分析最近一天的数据
  duration  => 86400
) FROM DUAL;

适合用来分析那些执行计划经常变化的"摇摆SQL"。

6.4 其他实用子程序

MONITOR_SESSION：监控某个会话的所有SQL

EXEC DBMS_SQL_MONITOR.MONITOR_SESSION(session_id => 12345);

STOP_MONITORING：停止监控某个SQL，释放内存资源

EXEC DBMS_SQL_MONITOR.STOP_MONITORING(sql_id => 'abcd1234');

PURGE_SQL_MONITOR_DATA：手动清理历史监控数据

EXEC DBMS_SQL_MONITOR.PURGE_SQL_MONITOR_DATA(older_than_days => 7);

七、实战演示：从慢查询到优化

光说不练假把式，咱们来做个完整的实战案例。我会模拟一个真实的慢查询场景，演示如何用SQL监控定位问题并优化。

7.1 环境准备

首先创建测试表，注意表名要用sys_前缀，不能用pg_：

-- 创建订单表
CREATE TABLE sys_orders (
    order_id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    region_code INT
);

-- 创建用户表
CREATE TABLE sys_users (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    register_date TIMESTAMP,
    vip_level INT
);

-- 插入测试数据，模拟百万级数据量
INSERT INTO sys_users 
SELECT generate_series(1,100000),
       'user_'||generate_series(1,100000),
       now() - (random()*365||' days')::interval,
       (random()*5)::int
FROM generate_series(1,100000);

INSERT INTO sys_orders
SELECT generate_series(1,500000),
       (random()*100000)::int + 1,
       now() - (random()*30||' days')::interval,
       (random()*1000)::decimal(10,2),
       CASE (random()*3)::int 
           WHEN 0 THEN 'pending'
           WHEN 1 THEN 'completed'
           ELSE 'cancelled'
       END,
       (random()*100)::int
FROM generate_series(1,500000);

-- 创建索引，但故意漏掉关键索引
CREATE INDEX idx_orders_user_id ON sys_orders(user_id);
CREATE INDEX idx_orders_date ON sys_orders(order_date);
-- 注意：这里故意不在region_code上建索引，后面用来演示问题

7.2 模拟慢查询

执行一个复杂的分析查询：

-- 这个查询统计最近7天各地区的订单金额，按VIP等级分组
SELECT u.vip_level, o.region_code, 
       COUNT(*) as order_count,
       SUM(o.amount) as total_amount,
       AVG(o.amount) as avg_amount
FROM sys_orders o
JOIN sys_users u ON o.user_id = u.user_id
WHERE o.order_date > now() - interval '7 days'
  AND o.status = 'completed'
  AND o.region_code BETWEEN 10 AND 50
GROUP BY u.vip_level, o.region_code
ORDER BY total_amount DESC;

第一次执行，我等了30秒还没出结果，这明显有问题。

7.3 查看SQL监控

先找到这个SQL的ID：

SELECT sql_id, sql_text, elapsed_time/1000000 as seconds
FROM v$sql_monitor 
WHERE sql_text LIKE '%sys_orders%'
  AND status = 'EXECUTING'
ORDER BY elapsed_time DESC;

假设查到的sql_id是7a8b9c0d，现在生成监控报告：

\o /tmp/sys_slow_query_report.html
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
    sql_id => '7a8b9c0d',
    report_level => 'ALL',
    type => 'HTML'
) FROM DUAL;
\o

打开HTML报告，关键发现：

执行计划显示：在sys_orders表上做了全表扫描（Seq Scan），因为region_code没有索引，而且order_date的范围条件选择性不够好。
时间线视图：80%的时间花在全表扫描和过滤上，Hash Join本身很快。
IO统计：产生了超过50万次块读取，其中90%是物理读（从磁盘读），说明数据不在缓存里。
等待事件：主要是"DataFileRead"等待，确认是IO瓶颈。

7.4 优化过程

根据监控报告，问题很明确：缺少region_code索引，导致全表扫描。

-- 创建复合索引，覆盖查询条件
CREATE INDEX idx_orders_region_date_status ON sys_orders(region_code, order_date, status);

创建索引后，再次执行同样的查询，这次1.2秒就出结果了。

7.5 对比验证

再次查看SQL监控报告，对比优化前后：

执行时间：从30秒降到1.2秒，提升25倍
IO次数：从50万次降到1200次
执行计划：变成了Index Only Scan，直接走新建的组合索引
内存使用：排序操作从使用临时文件（磁盘）变成了内存排序

生成对比报告给领导看，效果拔群。

7.6 监控特定业务SQL

有时候我们需要持续监控某个关键业务SQL，可以这样做：

-- 先给SQL加个提示（Hint），强制监控
SELECT /*+ MONITOR */ 
       u.vip_level, 
       COUNT(*) as cnt
FROM sys_orders o
JOIN sys_users u ON o.user_id = u.user_id
WHERE o.order_date > now() - interval '1 hour'
GROUP BY u.vip_level;

-- 然后查询监控视图
SELECT sql_id, 
       buffer_gets, 
       disk_reads,
       elapsed_time/1000 as ms
FROM v$sql_monitor
WHERE sql_text LIKE '%MONITOR%'
ORDER BY sql_exec_start DESC;

八、高级技巧与踩坑记录

用了这么久，也踩过不少坑，这里分享几个实用的技巧。

8.1 监控数据保留策略

默认情况下，SQL监控数据只保留15分钟，对于 overnight 的批处理作业不够用。我一般会修改配置：

-- 修改参数，保留24小时（单位秒）
ALTER SYSTEM SET sys_sql_monitor.max_entries = 50000;
ALTER SYSTEM SET sys_sql_monitor.retention_time = 86400;

-- 或者定期导出重要监控数据到历史表
CREATE TABLE sys_sql_monitor_history AS 
SELECT * FROM v$sql_monitor WHERE 1=0;

-- 每天定时归档
INSERT INTO sys_sql_monitor_history
SELECT * FROM v$sql_monitor 
WHERE elapsed_time > 10000000;  -- 只保留执行时间超过10秒的

8.2 并行查询的监控

金仓支持并行查询，监控并行SQL时要注意看px_servers字段。如果发现并行度不够，可以调整：

-- 查看并行执行情况
SELECT sql_id, 
       px_servers_requested,
       px_servers_allocated,
       px_used
FROM v$sql_monitor
WHERE px_servers_allocated > 0;

如果px_servers_allocated小于px_servers_requested，说明并行资源不够，需要调整max_parallel_workers参数。

8.3 绑定变量的监控

对于使用绑定变量的SQL，监控报告里会看到:{var_name}这样的占位符。要查看实际值，需要查v$sql_bind_capture视图：

SELECT sql_id, name, value_string, datatype_string
FROM v$sql_bind_capture
WHERE sql_id = '你的SQL_ID';

这在排查"同一条SQL有时快有时慢"的问题时特别有用，往往是绑定变量窥视（Bind Peeking）导致的。

8.4 我踩过的一个大坑

有一次我在生产环境开启SQL监控后，发现内存使用率持续上升，差点导致OOM。后来查文档才知道，监控大结果集的SQL时，会保存执行计划的每个步骤的行数统计，如果SQL产生了上亿行中间结果，监控数据也会很大。

解决办法是设置sys_sql_monitor.max_plan_lines参数，限制单个SQL监控的最大行数，或者对已知的大SQL使用/*+ NO_MONITOR */提示禁用监控。

-- 对已知的大查询禁用监控
SELECT /*+ NO_MONITOR */ * FROM sys_huge_table;

九、总结

这些差不多把我这段时间在金仓KingbaseES上使用SQL监控的经验都给大家罗列出来了。其实刚开始我觉得这功能作用不大，觉得有慢查询日志就够了。但真正在线上环境用过后，才发现SQL监控对于DBA有多大的一个帮助，很多以前要靠猜的问题，现在能看得监控出来的结果更好查看了。

数据库优化说到底是个经验活。就是经验再丰富的DBA，也架不住业务复杂度的快速增长。SQL监控这种工具，其实就是把专家的经验产品化，让普通开发者也能快速定位性能瓶颈。

当然，工具只是辅助，最终还是要理解业务、理解数据分布。就像我前面那个例子，如果我不清楚region_code的分布特点，就算有监控报告，也可能建错索引。

希望这篇文章能帮到正在使用金仓的朋友们。如果你也在用KingbaseES，或者有SQL优化方面的问题，欢迎在评论区交流。