作为数据库运维人员，日常最头疼的莫过于SQL性能异常、执行变慢、报错等问题——明明业务逻辑没改，SQL却突然卡顿；排查了半天，还是找不到问题根源。
其实，只要掌握OCP SQL诊断的核心方法，找准数据来源、用对查询语句，就能快速定位问题、高效优化。今天就给大家梳理一份「OCP SQL诊断基础指南」，涵盖TOP SQL、SLOW SQL、执行计划3大核心模块，新手也能轻松上手～
注意：诊断基础信息表的列都比较多，本文为了精简阅读，并不会在所有的SQL示例中将需要的列都列出来，大家可根据需要进行添加。表的定义可以在监控库中通过show create table的方式进行查询，每个列也有具体的注解。
一、TOP SQL
Top SQL是以用户SQL请求的SQL ID为聚合维度来统计各种不同形状SQL的执行统计信息，用户可以通过Top SQL功能分析用户的请求行为，可以按照不同的资源消耗进行SQL排序，展示SQL的执行历史趋势，帮助用户发现其中可能存在的异常请求，或者有针对性的对SQL进行性能调优分析。
1.1 数据来源
TOP SQL列表的数据来源以下几张表：
| 表名 |
注释 |
分区维护策略 |
数据来源 |
数据保存天数 |
| ob_hist_sql_audit_stat_0 |
以30S为时间窗口，以v$sql_audit中的SQL_ID为维度进行汇总。每一行数据代表30S内该SQL_ID的执行统计信息； |
每天会创建一个新的分区，用来存储当天的执行历史数据； |
数据来源是内核视图gv$sql_audit |
受OCP配置项：ocp.perf.sql.sql-hist-level0-retention 控制。 默认2天； |
| ob_hist_sql_audit_stat_1 |
以2M为时间窗口，以v$sql_audit中的SQL_ID为维度进行汇总。每一行数据代表2分钟内该SQL_ID的执行统计信息； |
每天会创建一个新的分区，用来存储当天的执行历史数据； |
数据来源是ob_hist_sql_audit_stat_0 |
受OCP配置项：ocp.perf.sql.sql-hist-level1-retention 控制。 默认8天； |
| ob_hist_sql_audit_stat_2 |
以10M为时间窗口，以v$sql_audit中的SQL_ID为维度进行汇总。每一行数据代表10分钟内该SQL_ID的执行统计信息； |
每天会创建一个新的分区，用来存储当天的执行历史数据； |
数据来源是ob_hist_sql_audit_stat_1 |
受OCP配置项：ocp.perf.sql.sql-hist-level2-retention 控制。 默认8天； |
| ob_hist_sqltext |
存储每个SQL_ID的元信息，包括租户名、用户名、database name、原始SQL字符串、参数化SQL文本等信息；可通过ob_cluster_id, cluster_name, ob_tenant_id,ob_db_id, sql_id字段来和上面三个视图进行关联。 |
每天会创建一个新的分区，用来存储当天的数据；但是对于每个 每天只需要记录一行记录；因此在关联上面三张表的时候需要指定ob_hist_sqltext.collect_time为当前的UTC零点； |
数据来源是内核视图gv$sql_audit, gv$plan_cache_plan_stat |
和ob_hist_sql_audit_stat_X 的最大保存天数一致； |
1.2 举个例子
想查询4号obcluster集群上1001号租户上2023-07-14 15:00:00到2023-07-14 16:00:00期间耗CPU最多的SQL：
步骤1： 找到集群的信息
ob_cluster_id =4
cluster_name ="obcluster"
ob_tenant_id=1001;
步骤2：利用time_to_usec将时间转成时间戳值
MySQL [ocp_monitor_40x_20230612]> select time_to_usec("2023-07-14 15:00:00.000000"), time_to_usec("2023-07-14 16:00:00.000000");
+--------------------------------------------+--------------------------------------------+
| time_to_usec("2023-07-14 15:00:00.000000") | time_to_usec("2023-07-14 16:00:00.000000") |
+--------------------------------------------+--------------------------------------------+
| 1689318000000000 | 1689321600000000 |
+--------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)
步骤3： 查看该区间内的SQL执行统计数据，按照SQL的CPU时间进行排序
// 为了方便阅读，这里仅查询部分统计值；具体列信息可以参考表定义；
// 旧版本表定义没有cpu_time列，可以通过：cpu_time = execute_time - total_wait_time + get_plan_time 计算
MySQL [ocp_monitor_40x_20230612]> select ob_db_id, sql_id, sum(elapsed_time) sum_elapsed_time, sum(executions) executions, sum(cpu_time) total_cpu from ob_hist_sql_audit_stat_0 where begin_interval_time >= 1689318000000000 and end_interval_time <= 1689321600000000 and begin_interval_time select ob_db_id, sql_id, sum(elapsed_time) sum_elapsed_time, sum(executions) executions, sum(execute_time - total_wait_time + get_plan_time) total_cpu from ob_hist_sql_audit_stat_0 where begin_interval_time >= 1689318000000000 and end_interval_time <= 1689321600000000 and begin_interval_time <= 1689321600000000 and ob_cluster_id = 4 and cluster_name = "obcluster" and ob_tenant_id = 1001 group by ob_db_id, sql_id order by total_cpu desc;
+------------------+----------------------------------+------------------+------------+-----------+
| ob_db_id | sql_id | sum_elapsed_time | executions | total_cpu |
+------------------+----------------------------------+------------------+------------+-----------+
| 1100611139403777 | 1B54659B953E6935647E13D8D95F97AA | 2106 | 7 | 1907 |
| 1100611139403777 | 375B52677EE6E8F88F61E2EB6F327151 | 1868 | 7 | 1670 |
| 1100611139403777 | 7758377661F32C4C2058B6D546BC872D | 1596 | 7 | 1431 |
+------------------+----------------------------------+------------------+------------+-----------+
3 rows in set (0.01 sec)
ob_hist_sql_audit_stat_0/ob_hist_sql_audit_stat_1/ob_hist_sql_audit_stat_2里面的数据分别对应30S聚合/2M聚合/10M聚合，且每个表中数据的保存天数也是逐渐变长，2天/8天/15天。一般情况，我们先按照汇总维度查出TOPSQL以后，再去看每个SQL具体的执行历史：
MySQL [ocp_monitor_40x_20230612]> select usec_to_time(begin_interval_time), usec_to_time(end_interval_time), sql_id, fail_count, executions from ob_hist_sql_audit_stat_0 where begin_interval_time >= 1689318000000000 and end_interval_time <= 1689321600000000 and begin_interval_time <= 1689321600000000 and ob_cluster_id = 4 and cluster_name = "obcluster" and ob_tenant_id = 1001 and sql_id = "1B54659B953E6935647E13D8D95F97AA";
+-----------------------------------+---------------------------------+----------------------------------+------------+------------+
| usec_to_time(begin_interval_time) | usec_to_time(end_interval_time) | sql_id | fail_count | executions |
+-----------------------------------+---------------------------------+----------------------------------+------------+------------+
| 2023-07-14 15:40:16.000000 | 2023-07-14 15:40:46.000000 | 1B54659B953E6935647E13D8D95F97AA | 0 | 1 |
| 2023-07-14 15:41:16.000000 | 2023-07-14 15:41:46.000000 | 1B54659B953E6935647E13D8D95F97AA | 0 | 2 |
| 2023-07-14 15:49:46.000000 | 2023-07-14 15:50:16.000000 | 1B54659B953E6935647E13D8D95F97AA | 0 | 1 |
| 2023-07-14 15:50:16.000000 | 2023-07-14 15:50:46.000000 | 1B54659B953E6935647E13D8D95F97AA | 0 | 1 |
| 2023-07-14 15:50:46.000000 | 2023-07-14 15:51:16.000000 | 1B54659B953E6935647E13D8D95F97AA | 0 | 1 |
| 2023-07-14 15:56:16.000000 | 2023-07-14 15:56:46.000000 | 1B54659B953E6935647E13D8D95F97AA | 0 | 1 |
+-----------------------------------+---------------------------------+----------------------------------+------------+------------+
6 rows in set (0.00 sec)
因此当你查询当天短时间的SQL，可以使用ob_hist_sql_audit_stat_0作为数据来源。
如果你查询的时间比较久，比如你要查询10天前的数据，那你只能选择ob_hist_sql_audit_stat_2作为数据来源。比如如果你想查询6天前的数据，那你可以选择用ob_hist_sql_audit_stat_1或者ob_hist_sql_audit_stat_2作为数据来源，在返回结果不再次聚合的情况下，ob_hist_sql_audit_stat_2每行记录表示10分钟的汇总，ob_hist_sql_audit_stat_1每行记录表示2分钟的汇总数据，另外使用ob_hist_sql_audit_stat_2时，查询时间范围的前10分钟和最后10分钟数据会有一定程度的失真；
ob_hist_sql_audit_stat_X表提供的信息非常丰富，比较常用的字段，比如：
- exections 执行次数，可以用来判断SQL是不是流量方式了变化；
- queue_time SQL队列排队等待时间，可以用来判断该SQL是否因为排队时间太长而导致响应时间长；
- fail_count SQL执行错误次数，可以用来判断该SQL是否存在报错，以及可以通过其他列来获取具体的错误码信息；
- ssstore_read_row_count 表示SQL的物理读行数，如果SQL的ssstore_read_row_count数值比较大，则说明从oceanbase sstable（或者是块缓存）中读取的数据量特别多，可以考虑进行索引优化来减少数据扫描量。
1.3 查看TOPQL对应的SQL文本
通过上面的查询，我们只拿到了执行消耗资源较多的SQL，但是并不知道这些SQL长什么样，也就无法从SQL文本的维度来判断业务来源、表定义等有用信息。因此这里需要去查询ob_hist_sqltext表，拿到具体的SQL长什么样。
前面内容也介绍过，对于每个我们每天只会记录一次数据，因此这里需要将查询时间设置为 SQL执行时间 的UTC 0点。
当前我的环境是北京时间东八区，因此UTC 0点对应的时间为本地时间8点；
MySQL [ocp_monitor_40x_20230612]> select time_to_usec("2023-07-14 08:00:00.000000");
+--------------------------------------------+
| time_to_usec("2023-07-14 08:00:00.000000") |
+--------------------------------------------+
| 1689292800000000 |
+--------------------------------------------+
1 row in set (0.00 sec)
MySQL [ocp_monitor_40x_20230612]> select sql_text from ob_hist_sqltext where ob_cluster_id = 4 and cluster_name = "obcluster" and ob_tenant_id = 1001 and collect_Time = 1689292800000000 and sql_id = "1B54659B953E6935647E13D8D95F97AA" and ob_db_id = 1100611139403777;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_text |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select /*+ READ_CONSISTENCY(WEAK) */ database_id, tenant_id, outline_id, name as outlineName, outline_content, sql_text, sql_id, outline_target, enabled, gmt_create from oceanbase.__all_outline_history where ADDDATE(gmt_create, 30) > CURDATE() and is_deleted = 0 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1.4 查看TOPSQL的执行计划
大多数情况下，上面拿到的信息还是不够排查问题，比如说SQL就是突然执行变慢了，那我们需要再次获取该SQL的执行计划，看他是不是执行计划发生了变化导致的变慢，因此需要查询ob_hist_plan_cache_plan/ob_hist_plan_cache_plan_stat_0 /ob_hist_plan_cache_plan_explain
这里可以直接跳转到 第三章节 执行计划，看对应视图的介绍和使用方式；
二、SLOW SQL
我们将执行时间超过一定时间（可设定，默认100ms）的SQL称之为Slow SQL，用户可根据业务场景来对Slow SQL进行不同的阈值配置，Slow SQL的影响在于：
- 用户响应时间慢，响应时间长，影响业务体验；
- 系统资源开销大，极端情况可能导致系统不可用
所以需要将这些有可能会影响系统稳定性的slowsql进行收集分析，帮忙用户提早排查问题，规避风险。OCP Slow SQL 关注单次 SQL 执行的信息，对 oceanabse gv$sql_audit 的数据进行采样保存，提供Slow SQL的资源消耗和执行详情。
2.1 数据来源
SLOWSQL列表的数据来源以下表：
| 表名 |
注释 |
分区维护策略 |
数据来源 |
数据保存天数 |
| ob_hist_sql_audit_sample |
存储从gv$sql_audit中查询到的慢SQL的明显数据；表中每个记录对应一次SQL执行详情；该表格不单给slow sql使用，同时并行SQL和事务诊断也会依赖该表格，用来存储对应的SQL信息。 我们可以通过列 cause_type来判断该行数据的用户。对于SLOWSQL： cause_type |
0x1 = 1;对于异常事务的SQL : cause_type |
0x2 = 1;对于并行SQL：cause_type |
|
| ob_hist_sqltext |
存储每个SQL_ID的元信息，包括租户名、用户名、database name、原始SQL字符串、参数化SQL文本等信息；可通过ob_cluster_id, cluster_name, ob_tenant_id,ob_db_id, sql_id字段来和上面的视图进行关联。 |
每天会创建一个新的分区，用来存储当天的数据；但是对于每个 每天只会记录一行记录；因此在关联上面三张表的时候需要指定collect_time为当前的UTC零点； |
数据来源是内核视图gv$sql_audit, gv$plan_cache_plan_stat |
和ob_hist_sql_audit_stat_X 的最大保存天数一致； |
2.2 举个例子
想查询 4 号 obcluster 集群上 1001号租户上 2023-07-10 19:00:00 到 2023-07-10 20:00:00 期间 的慢SQL列表：
步骤1：找到集群的信息
ob_cluster_id = 4
cluster_name = "obcluster"
ob_tenant_id = 1001;
步骤2：利用time_to_usec将时间转成时间戳值
MySQL [ocp_monitor_40x_20230612]> select time_to_usec("2023-07-10 19:00:00.000000"), time_to_usec("2023-07-10 20:00:00.000000");
+--------------------------------------------+--------------------------------------------+
| time_to_usec("2023-07-10 19:00:00.000000") | time_to_usec("2023-07-10 20:00:00.000000") |
+--------------------------------------------+--------------------------------------------+
| 1688986800000000 | 1688990400000000 |
+--------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)
步骤3：查看该区间内的SLOWSQL列表
MySQL [ocp_monitor_40x_20230612]> select ob_db_id, sql_id, sum(elapsed_time) sum_elapsed_time, count(*) executions, sum(cpu_time) total_cpu from ob_hist_sql_audit_sample where cause_type in (1, 3, 5, 7) and request_time >= 1688986800000000 and request_time select ob_db_id, sql_id, sum(elapsed_time) sum_elapsed_time, count(*) executions, sum(execute_time - total_wait_time + get_plan_time) total_cpu from ob_hist_sql_audit_sample where cause_type in (1, 3, 5, 7) and request_time >= 1688986800000000 and request_time select time_to_usec("2023-07-12 17:00:00") , time_to_usec("2023-07-12 19:00:00");
+-------------------------------------+-------------------------------------+
| time_to_usec("2023-07-12 17:00:00") | time_to_usec("2023-07-12 19:00:00") |
+-------------------------------------+-------------------------------------+
| 1689152400000000 | 1689159600000000 |
+-------------------------------------+-------------------------------------+
1 row in set (0.00 sec)
步骤4：查看该区间内的可疑SQL列表
MySQL [ocp_monitor_40x_20230612]> select ob_db_id, sql_id, group_concat(distinct diagnose_type), sum(executions) from ocp_perf_sql_diagnosis where ob_cluster_id = 4 and cluster_name = "obcluster" and ob_tenant_id = 1001 and first_execute_time >= 1689152400000000 and last_execute_time <= 1689159600000000 group by ob_db_id, sql_id;
+------------------+----------------------------------+--------------------------------------+-----------------+
| ob_db_id | sql_id | group_concat(distinct diagnose_type) | sum(executions) |
+------------------+----------------------------------+--------------------------------------+-----------------+
| 1100611139404849 | 32BA9824BA2E9D0261244BFD72C426E3 | INEFFECTIVE_HINT | 19182 |
| 1100611139404849 | 45CCBDC7DEBDCDA41E0F4AFC670EF446 | TABLE_SCAN_INDEX_NOT_EXISTS | 8316 |
| 1100611139404849 | 6E05C3DCE4D418C927918CDB62A01141 | TABLE_SCAN_INDEX_NOT_USED | 20760 |
| 1100611139404849 | E94582374146530E83A53E80A75AD8C4 | CPU_TIME_PROPORTION_HIGH | 6363 |
+------------------+----------------------------------+--------------------------------------+-----------------+
4 rows in set (0.01 sec)
步骤5： 查看该区间内SQL的其他信息
ocp_perf_sql_diagnosis记录了系统自动检测出来的可疑SQL。
ocp_perf_sql_diagnosis的列比较有限，如果还想进一步看该SQL的执行历史和执行计划等信息，则需要再去关联ob_hist_sql_audit_stat_X表和执行计划相关的表；
三、执行计划
执行计划是用来排查SQL性能问题的一大利器，用户能从执行计划的角度，来观察执行计划是否满足期望、是否发生了执行计划变化、执行计划中每个算子的输入输出和代价估算等信息。
由于同一个SQL的相同物理执行计划可能被多次产生，这些物理执行在树形结构上完全一样，但是会有不一样的plan_id和first_load_time来标识该执行计划；因此特定SQL的物理执行计划可能在monitordb中存储多次，需要用户自己来判断这些执行计划是否是同一个树形结构。（在OCP的较高版本，我们将这个识别工作封装到了产品层面）。
3.1 数据来源
执行计划的数据来源如下所示：
| 表名 |
注释 |
分区维护策略 |
数据来源 |
数据保存天数 |
| ob_hist_plan_cache_plan_stat_0 |
以30S为统计周期，记录每个周期内plan的累计执行详情； |
每天会创建一个新的分区，用来存储当天的采样数据； |
数据来源是内核视图gv$plan_cache_plan_stat |
受OCP配置项：ocp.perf.sql.plan-hist-level0-retention 控制。 默认8天； |
| ob_hist_plan_cache_plan |
记录每个plan的元信息，包括生成时间、plan hash、schem version等；对于每个 每天只需要记录一行记录；其他表ob_hist_plan_cache_plan进行关联查询的时候，需要指定ob_hist_plan_cache_plan.collect_time为查询范围的UTC 0点。 |
每天会创建一个新的分区，用来存储当天的数据； |
数据来源是内核视图gv$plan_cache_plan_stat |
和ob_hist_sql_audit_stat_X 的最大保存天数一致； |
| ob_hist_plan_cache_plan_explain |
以执行算子为维度记录每个plan的结构。物理执行计划的树形结构则是从该表中获取数据进行组装的。 |
每天会创建一个新的分区，用来存储当天的数据； |
数据来源是内核视图gv$plan_cache_plan_explain |
和ob_hist_plan_cache_plan_stat_0的保存天数一致； |
3.2 举个例子
假想查询 4 号 obcluster 集群上 1号租户上 2023-07-16 07:00:00 到 2023-07-16 09:00:00 期间的SQL （OB_DB_ID = 1099511627777 AND SQL_ID = 4BF478BE68AEE5F18F49B3D8C54EB902）的执行计划信息：
- 利用time_to_usec将时间转成时间戳值
MySQL [ocp_monitor_40x_20230612]> select time_to_usec("2023-07-16 08:00:00.000000"), time_to_usec("2023-07-16 10:00:00.000000");
+--------------------------------------------+--------------------------------------------+
| time_to_usec("2023-07-16 08:00:00.000000") | time_to_usec("2023-07-16 10:00:00.000000") |
+--------------------------------------------+--------------------------------------------+
| 1689465600000000 | 1689472800000000 |
+--------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)
- 查看这段时间内生成的执行计划列表
MySQL [ocp_monitor_40x_20230612]> select plan_id,first_load_time, ob_server_id from ob_hist_plan_cache_plan_stat_0 where ob_cluster_id = 4 and cluster_name = "obcluster" and ob_tenant_id = 1 and OB_DB_ID = 1099511627777 AND SQL_ID = "4BF478BE68AEE5F18F49B3D8C54EB902" and collect_time >= 1689465600000000 and collect_time select id, operator, object_name, rows,cost, property from ob_hist_plan_cache_plan_explain where ob_cluster_id = 4 and cluster_name = "obcluster" and ob_tenant_id = 1 and OB_DB_ID = 1099511627777 AND SQL_ID = "4BF478BE68AEE5F18F49B3D8C54EB902" and ob_server_id = 1 and plan_id = 108212 and first_load_time = 1689466721391288;
+----+--------------------------+----------------------------+------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | operator | object_name | rows | cost | property |
+----+--------------------------+----------------------------+------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0 | PHY_LIMIT | NULL | 1000 | 120224 | NULL |
| 1 | PHY_PX_MERGE_SORT_COORD | NULL | 2000 | 119948 | NULL |
| 2 | PHY_PX_REDUCE_TRANSMIT | NULL | 2000 | 97987 | NULL |
| 3 | PHY_SORT | NULL | 2000 | 97987 | NULL |
| 4 | PHY_GRANULE_ITERATOR | NULL | 2000 | 5391 | NULL |
| 5 | PHY_TABLE_SCAN | __all_server_event_history | 2000 | 5391 | table_rows:24398, physical_range_rows:390736, logical_range_rows:390736, index_back_rows:0, output_rows:288831, est_method:local_storage, avaiable_index_name[__all_server_event_history], estimation info[table_id:1099511627930, (table_type:1, version:0-1689444010383088-1689444010383088, logical_rc:24398, physical_rc:24398), (table_type:0, version:1689444000529193-1689444000529193-9223372036854775807, logical_rc:23, physical_rc:23)] |
+----+--------------------------+----------------------------+------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)
可以仔细观察一下上面输出结果的operator列值，从1号算子开始，每个算子名前面都有一些空格，且算子前面的空格越来越多，这个空格就是用来表示物理执行计划树的层次，当两个算子前面的空格数目一致时，说明这两个算子在同一个层级。
五、表结构定义
由于每个OCP版本对应的表结构定义会略有不同，建议直接在monitordb库通过 show create table的方式来进行查阅，表和列的定义都在DDL中有详细的说明。
掌握以上核心模块，就能应对大部分SQL诊断场景，如果你在 OceanBase 运维中遇到类似的问题，欢迎在评论区留言讨论，我们会持续分享更多实战排障干货，下次见~
