OceanBase 数据库高效运维：调优技术深度解析

原创 shunwahⓂ️ 2025-05-24

254

作者：ShunWah

在运维管理领域，我拥有多年深厚的专业积累，兼具坚实的理论基础与广泛的实践经验。精通运维自动化流程，对于OceanBase、MySQL等多种数据库的部署与运维，具备从初始部署到后期维护的全链条管理能力。拥有OceanBase的OBCA和OBCP认证、OpenGauss社区认证结业证书，以及崖山DBCA、亚信AntDBCA、翰高HDCA、GBase 8a | 8c | 8s、Galaxybase的GBCA、Neo4j的Graph Data Science Certification、NebulaGraph的NGCI & NGCP、东方通TongTech TCPE等多项权威认证。

在OceanBase & 墨天轮的技术征文大赛中，多次荣获一、二、三等奖。同时，在OpenGauss第五届、第六届、第七届技术征文大赛，TiDB社区专栏征文大赛，金仓数据库有奖征文活动，以及YashanDB「产品体验官」征文等活动中，我也屡获殊荣。此外，我还活跃于墨天轮、CSDN、ITPUB等技术平台，经常发布原创技术文章，并多次被首页推荐。

前言

在当今数据驱动的时代，数据库性能直接关系到企业核心业务的稳定性和用户体验。OceanBase 作为一款原生分布式数据库，凭借其高可用、高扩展和高性能的特点，已成为众多企业关键业务系统的首选。然而，随着数据量增长和业务复杂度提升，性能问题也逐渐显现，如何快速定位和解决这些问题成为数据库管理员和开发者的重要课题。

本指南系统性地介绍了 OceanBase 数据库性能诊断与调优的全套方法论，从基础资源监控到高级调优技巧，覆盖 CPU、内存、I/O、磁盘等关键维度。通过大量真实案例和可落地的 SQL 示例，我们将带您深入 OceanBase 内部工作机制，掌握：

资源瓶颈的快速定位方法
常见性能问题的解决方案
SQL 执行计划的深度解析
索引设计与优化实践
全链路性能诊断技术

无论您是初次接触 OceanBase 的新手，还是经验丰富的 DBA，都能从本指南中获得实用的性能优化知识。

一、性能诊断集群概述

OceanBase数据库性能诊断主要关注CPU、内存、IO和磁盘等关键指标。当这些指标出现异常时，会直接影响性能，需要针对不同情况采取相应措施。

常见性能问题处理建议

CPU占用过高处理方案：
- 排查正在执行的SQL语句
- 对高负载SQL进行限流
- 优化SQL执行效率
内存占用过高处理方案：
- 扩大租户内存配置
- 调高转储线程数加速内存释放
- 调整租户memstore比例
- 开启写入限速机制
- 降级批处理任务
IO占用过高处理方案：
- 暂停正在进行的合并操作
- 暂停备份任务
- 暂停数据传输任务
- 调整转储线程数
- 限制高负载SQL执行
磁盘空间不足处理方案：
- 业务数据盘：通过扩容解决
- 日志数据盘：清理过期日志

1. 连接诊断入口

# 体验环境快速连接（生产环境需配置密码）
[root@iZbp14o5jye5h6fqcxuedpZ ~]# obclient -h127.0.0.1 -uroot@sys -P2881 -Doceanbase -A
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487619
Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [oceanbase]>

⚠️ 安全提示：体验账号仅限测试环境，生产环境应遵循最小权限原则创建专用诊断账号。

说明：

root用户未设置密码，仅供体验使用

sys租户仅为体验临时使用

二、资源使用情况分析

1. 查看OBServer资源概况

1.1 – 查看服务器资源分配和使用情况

obclient [oceanbase]> SELECT
    ->   svr_ip as ip,
    ->   CONCAT(cpu_capacity_max, 'C') as cpu_total,
    ->   CONCAT(cpu_assigned_max, 'C') as cpu_assigned,
    ->   CONCAT(TRUNCATE(mem_capacity/1024/1024/1024,2),'G') as mem_total,
    ->   CONCAT(TRUNCATE(mem_assigned/1024/1024/1024,2),'G') as mem_assigned,
    ->   CONCAT(TRUNCATE(data_disk_capacity/1024/1024/1024,2),'G') as total_data_size,
    ->   CONCAT(TRUNCATE(log_disk_capacity/1024/1024/1024,2),'G') as total_log_size,
    ->   CONCAT(TRUNCATE((cpu_assigned_max/cpu_capacity_max)*100,2),'%') as cpu_assigned_percent,
    ->   CONCAT(TRUNCATE((mem_assigned/mem_capacity)*100,2),'%') as mem_assigned_percent,
    ->   CONCAT(TRUNCATE((data_disk_in_use/data_disk_capacity)*100,2),'%') as used_data_percent,
    ->   CONCAT(TRUNCATE((log_disk_in_use/log_disk_capacity)*100,2),'%') as used_log_percent
    -> FROM oceanbase.GV$OB_SERVERS;
+-----------+-----------+--------------+-----------+--------------+-----------------+----------------+----------------------+----------------------+-------------------+------------------+
| ip        | cpu_total | cpu_assigned | mem_total | mem_assigned | total_data_size | total_log_size | cpu_assigned_percent | mem_assigned_percent | used_data_percent | used_log_percent |
+-----------+-----------+--------------+-----------+--------------+-----------------+----------------+----------------------+----------------------+-------------------+------------------+
| 127.0.0.1 | 16C       | 3C           | 5.00G     | 4.00G        | 15.84G          | 13.00G         | 18.75%               | 80.00%               | 0.30%             | 3.84%            |
+-----------+-----------+--------------+-----------+--------------+-----------------+----------------+----------------------+----------------------+-------------------+------------------+
1 row in set (0.005 sec)

obclient [oceanbase]>

结果解读：

该查询展示每台OBServer的CPU、内存、数据盘和日志盘的总量、已分配量和使用百分比
重点关注使用率超过80%的资源项，可能存在性能风险

2. 磁盘空间分析

2.1 业务数据盘使用情况

obclient [oceanbase]> SELECT
    ->   CONCAT(TRUNCATE(data_disk_capacity/1024/1024/1024,2),'G') as total_data_size,
    ->   CONCAT(TRUNCATE(data_disk_in_use/1024/1024/1024,2),'G') as used_data_size,
    ->   CONCAT(TRUNCATE((data_disk_in_use/data_disk_capacity)*100,2),'%') as used_data_percent,
    ->   CONCAT(TRUNCATE((data_disk_capacity-data_disk_in_use)/1024/1024/1024,2),'G') as free_data_size,
    ->   CONCAT(TRUNCATE(((data_disk_capacity-data_disk_in_use)/data_disk_capacity)*100,2),'%') as free_data_percent
    -> FROM oceanbase.GV$OB_SERVERS;
+-----------------+----------------+-------------------+----------------+-------------------+
| total_data_size | used_data_size | used_data_percent | free_data_size | free_data_percent |
+-----------------+----------------+-------------------+----------------+-------------------+
| 15.83G          | 0.04G          | 0.30%             | 15.78G         | 99.69%            |
+-----------------+----------------+-------------------+----------------+-------------------+
1 row in set (0.003 sec)

obclient [oceanbase]>

2.2 日志数据盘使用情况

obclient [oceanbase]> SELECT
    ->   CONCAT(TRUNCATE(log_disk_capacity/1024/1024/1024,2),'G') as total_log_size,
    ->   CONCAT(TRUNCATE(log_disk_in_use/1024/1024/1024,2),'G') as used_log_size,
    ->   CONCAT(TRUNCATE(log_disk_assigned/1024/1024/1024,2),'G') as assigned_log_size,
    ->   CONCAT(TRUNCATE((log_disk_in_use/log_disk_capacity)*100,2),'%') as used_log_percent,
    ->   CONCAT(TRUNCATE((log_disk_capacity-log_disk_assigned-log_disk_in_use)/1024/1024/1024,2),'G') as free_log_size,
    ->   CONCAT(TRUNCATE(((log_disk_capacity-log_disk_assigned-log_disk_in_use)/log_disk_capacity)*100,2),'%') as free_log_percent
    -> FROM oceanbase.GV$OB_SERVERS;
+----------------+---------------+-------------------+------------------+---------------+------------------+
| total_log_size | used_log_size | assigned_log_size | used_log_percent | free_log_size | free_log_percent |
+----------------+---------------+-------------------+------------------+---------------+------------------+
| 13.00G         | 0.50G         | 8.00G             | 3.84%            | 4.50G         | 34.61%           |
+----------------+---------------+-------------------+------------------+---------------+------------------+
1 row in set (0.003 sec)

obclient [oceanbase]>

诊断建议：

当业务数据盘使用率超过70%时，建议扩容
当日志盘使用率超过60%时，建议检查日志保留策略或扩容

3. 关键资源监控指标

监控维度	诊断命令	优化方向
CPU使用率	`SELECT * FROM gv$sysstat WHERE con_id>1000 AND name='cpu usage' AND value>1000;`	SQL限流/优化
内存占用	`SELECT * FROM V$OB_MEMSTORE`	调整memstore比例/转储线程数
I/O负载	`SHOW TRACE`	暂停合并/备份任务
磁盘使用	`SELECT * FROM oceanbase.GV$OB_SERVERS`	扩容存储/清理日志

3.1 – 集群资源总览（CPU/内存/磁盘）

obclient [oceanbase]> SELECT 
    ->   svr_ip,
    ->   CONCAT(cpu_assigned_max,'C') AS assigned_cpu,
    ->   CONCAT(TRUNCATE(mem_assigned/1024/1024/1024,2),'G') AS used_mem,
    ->   CONCAT(TRUNCATE((data_disk_in_use/data_disk_capacity)*100,2),'%') AS data_usage
    -> FROM oceanbase.GV$OB_SERVERS;
+-----------+--------------+----------+------------+
| svr_ip    | assigned_cpu | used_mem | data_usage |
+-----------+--------------+----------+------------+
| 127.0.0.1 | 3C           | 4.00G    | 0.30%      |
+-----------+--------------+----------+------------+
1 row in set (0.003 sec)

obclient [oceanbase]>

监控指标解读：

🔴 红色警戒：CPU持续>80%、内存Swap触发、磁盘剩余<20%
🟡 黄色预警：合并延迟>5分钟、慢查询>10%总请求

4. 查看 OBServer 运行情况

4.1 可以使用如下 SQL，查询 OBServer 运行情况。

obclient [oceanbase]> SELECT
    ->   zone,
    ->   svr_ip,
    ->   svr_port,
    ->   sql_port AS inner_port,
    ->   with_rootserver,
    ->   UPPER(`status`) AS `status`,
    ->   build_version,
    ->   stop_time * 1000000 AS stop_time,
    ->   start_service_time  * 1000000 AS start_service_time,
    ->   last_offline_time  * 1000000 AS last_offline_time,
    ->   block_migrate_in_time  * 1000000 AS block_migrate_in_time
    -> FROM
    ->   oceanbase.DBA_OB_SERVERS;
+-------+-----------+----------+------------+-----------------+--------+-------------------------------------------------------------------------------------------+-----------+-----------------------------+-------------------+-----------------------+
| zone  | svr_ip    | svr_port | inner_port | with_rootserver | status | build_version                                                                             | stop_time | start_service_time          | last_offline_time | block_migrate_in_time |
+-------+-----------+----------+------------+-----------------+--------+-------------------------------------------------------------------------------------------+-----------+-----------------------------+-------------------+-----------------------+
| zone1 | 127.0.0.1 |     2882 |       2881 | YES             | ACTIVE | 4.2.1.0_100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959(Sep 28 2023 07:25:28) |      NULL | 20250513174511009669.000000 |              NULL |                  NULL |
+-------+-----------+----------+------------+-----------------+--------+-------------------------------------------------------------------------------------------+-----------+-----------------------------+-------------------+-----------------------+
1 row in set (0.003 sec)

obclient [oceanbase]>

4.2 查询 UNIT 列表

可以使用如下 SQL，查询 UNIT 的详细情况。

obclient [oceanbase]> SELECT
    ->   /*+ QUERY_TIMEOUT(60000000) */
    ->   t1.MODIFY_TIME AS UPDATE_TIME,
    ->   t1.RESOURCE_POOL_ID,
    ->   t1.NAME,
    ->   t1.UNIT_COUNT,
    ->   t1.UNIT_CONFIG_ID,
    ->   t1.ZONE_LIST,
    ->   t1.TENANT_ID,
    ->   t1.REPLICA_TYPE,
    ->   t2.NAME AS UNIT_CONFIG_NAME,
    ->   t2.MAX_CPU,
    ->   t2.MIN_CPU,
    ->   CONCAT(TRUNCATE(t2.MEMORY_SIZE/1024/1024/1024,2),'G'),
    ->   t2.MAX_IOPS,
    ->   t2.MIN_IOPS,
    ->   CONCAT(TRUNCATE(t2.LOG_DISK_SIZE/1024/1024/1024,2),'G') 
    -> FROM
    ->   oceanbase.DBA_OB_RESOURCE_POOLS AS t1
    ->   JOIN oceanbase.DBA_OB_UNIT_CONFIGS AS t2 ON t1.UNIT_CONFIG_ID = t2.UNIT_CONFIG_ID;
+----------------------------+------------------+------------+------------+----------------+-----------+-----------+--------------+------------------+---------+---------+-------------------------------------------------------+---------------------+---------------------+---------------------------------------------------------+
| UPDATE_TIME                | RESOURCE_POOL_ID | NAME       | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | TENANT_ID | REPLICA_TYPE | UNIT_CONFIG_NAME | MAX_CPU | MIN_CPU | CONCAT(TRUNCATE(t2.MEMORY_SIZE/1024/1024/1024,2),'G') | MAX_IOPS            | MIN_IOPS            | CONCAT(TRUNCATE(t2.LOG_DISK_SIZE/1024/1024/1024,2),'G') |
+----------------------------+------------------+------------+------------+----------------+-----------+-----------+--------------+------------------+---------+---------+-------------------------------------------------------+---------------------+---------------------+---------------------------------------------------------+
| 2025-05-13 17:45:05.780282 |                1 | sys_pool   |          1 |              1 | zone1     |         1 | FULL         | sys_unit_config  |       2 |       2 | 2.00G                                                 | 9223372036854775807 | 9223372036854775807 | 2.00G                                                   |
| 2025-05-13 17:45:14.377880 |             1001 | mq_pool_01 |          1 |           1001 | zone1     |      1002 | FULL         | s1_unit_config   |       1 |       1 | 2.00G                                                 |               10000 |               10000 | 6.00G                                                   |
+----------------------------+------------------+------------+------------+----------------+-----------+-----------+--------------+------------------+---------+---------+-------------------------------------------------------+---------------------+---------------------+---------------------------------------------------------+
2 rows in set (0.004 sec)

obclient [oceanbase]>

三、性能瓶颈定位

1. CPU高负载分析

1.1 – 典型解决方案

🔍 SQL审计：ALTER SYSTEM SET enable_sql_audit=True; 定位TOP SQL
✂️ 资源管控：ALTER RESOURCE POOL pool_name UNIT_NUM=4; 限制并发
📉 负载分流：通过读写分离将分析型查询迁移至专用副本

2. 内存使用分析

2.1 MemStore使用情况

obclient [oceanbase]> SELECT
    ->   TENANT_ID,
    ->   SVR_IP,
    ->   round(ACTIVE_SPAN/1024/1024/1024,2) ACTIVE_GB,
    ->   round(MEMSTORE_USED/1024/1024/1024,2) TOTAL_GB,
    ->   round(FREEZE_TRIGGER/1024/1024/1024,2) FREEZE_TRIGGER_GB,
    ->   round(MEMSTORE_USED/FREEZE_TRIGGER*100,2) percent_trigger,
    ->   round(MEMSTORE_LIMIT/1024/1024/1024,2) MEM_LIMIT_GB
    -> FROM V$OB_MEMSTORE
    -> WHERE tenant_id > 1000 OR TENANT_ID = 1
    -> ORDER BY tenant_id, TOTAL_GB desc;
+-----------+-----------+-----------+----------+-------------------+-----------------+--------------+
| TENANT_ID | SVR_IP    | ACTIVE_GB | TOTAL_GB | FREEZE_TRIGGER_GB | percent_trigger | MEM_LIMIT_GB |
+-----------+-----------+-----------+----------+-------------------+-----------------+--------------+
|         1 | 127.0.0.1 |      0.10 |     0.10 |              0.26 |           37.95 |         1.00 |
|      1001 | 127.0.0.1 |      0.07 |     0.07 |              0.13 |           52.08 |         0.50 |
|      1002 | 127.0.0.1 |      0.06 |     0.06 |              0.13 |           46.13 |         0.50 |
+-----------+-----------+-----------+----------+-------------------+-----------------+--------------+
3 rows in set (0.005 sec)

obclient [oceanbase]>

关键指标：

percent_trigger：MemStore使用比例，接近100%时可能触发冻结
MEM_LIMIT_GB：MemStore内存上限

2.2 内存模块详情

-- 查看内存占用最高的10个模块
obclient [oceanbase]> SELECT * FROM V$OB_MEMORY ORDER BY hold DESC LIMIT 10;
+-----------+-----------+----------+-----------------+----------------+-------+-----------+-----------+
| TENANT_ID | SVR_IP    | SVR_PORT | CTX_NAME        | MOD_NAME       | COUNT | HOLD      | USED      |
+-----------+-----------+----------+-----------------+----------------+-------+-----------+-----------+
|         1 | 127.0.0.1 |     2882 | CO_STACK        | CoStack        |   211 | 108896256 | 108693696 |
|         1 | 127.0.0.1 |     2882 | MEMSTORE_CTX_ID | Memstore       |    51 | 106119168 | 106070616 |
|      1002 | 127.0.0.1 |     2882 | CO_STACK        | CoStack        |   185 |  95477760 |  95300160 |
|      1001 | 127.0.0.1 |     2882 | CO_STACK        | CoStack        |   167 |  86188032 |  86027712 |
|      1002 | 127.0.0.1 |     2882 | DEFAULT_CTX_ID  | LogGroupBuffer |     2 |  83927040 |  83886080 |
|       500 | 127.0.0.1 |     2882 | SCHEMA_SERVICE  | TenantSchemMgr |    46 |  83278720 |  83237376 |
|       500 | 127.0.0.1 |     2882 | CO_STACK        | CoStack        |   161 |  83091456 |  82936896 |
|      1001 | 127.0.0.1 |     2882 | MEMSTORE_CTX_ID | Memstore       |    35 |  72826880 |  72793560 |
|      1002 | 127.0.0.1 |     2882 | MEMSTORE_CTX_ID | Memstore       |    31 |  64503808 |  64474296 |
|       500 | 127.0.0.1 |     2882 | SCHEMA_SERVICE  | SchemaSysCache |  6457 |  51682720 |  51212358 |
+-----------+-----------+----------+-----------------+----------------+-------+-----------+-----------+
10 rows in set (0.007 sec)

obclient [oceanbase]>

2.3 查看KVCACHE使用情况

obclient [oceanbase]> SELECT
    ->   tenant_id,
    ->   svr_ip,
    ->   cache_name,
    ->   concat(round(cache_size/1024/1024/1024,2), 'G') cache_size
    -> FROM V$OB_KVCACHE
    -> ORDER BY 1,2,4 desc;
+-----------+-----------+----------------------+------------+
| tenant_id | svr_ip    | cache_name           | cache_size |
+-----------+-----------+----------------------+------------+
|         1 | 127.0.0.1 | schema_cache         | 0.01G      |
|         1 | 127.0.0.1 | vtable_cache         | 0.01G      |
|         1 | 127.0.0.1 | opt_table_stat_cache | 0.01G      |
|      1001 | 127.0.0.1 | opt_table_stat_cache | 0.01G      |
|      1002 | 127.0.0.1 | opt_table_stat_cache | 0.01G      |
|      1002 | 127.0.0.1 | index_block_cache    | 0.00G      |
|      1002 | 127.0.0.1 | user_block_cache     | 0.00G      |
+-----------+-----------+----------------------+------------+
7 rows in set (0.005 sec)

obclient [oceanbase]>

3. 内存泄漏处置

3.1 – 内存使用深度诊断

obclient [oceanbase]> SELECT 
    ->   TENANT_ID,
    ->   ROUND(MEMSTORE_USED/1024/1024/1024,2) AS used_gb,
    ->   ROUND(FREEZE_TRIGGER/1024/1024/1024,2) AS trigger_gb,
    ->   ROUND(MEMSTORE_USED/FREEZE_TRIGGER*100,2) AS usage_percent
    -> FROM V$OB_MEMSTORE
    -> WHERE TENANT_ID > 1000;
+-----------+---------+------------+---------------+
| TENANT_ID | used_gb | trigger_gb | usage_percent |
+-----------+---------+------------+---------------+
|      1001 |    0.07 |       0.13 |         52.08 |
|      1002 |    0.06 |       0.13 |         46.13 |
+-----------+---------+------------+---------------+
2 rows in set (0.003 sec)

obclient [oceanbase]>

紧急处理方案：

3.2 – 动态调参（立即生效）

ALTER SYSTEM SET memstore_limit_percentage=80; – 默认50%
ALTER SYSTEM SET freeze_trigger_percentage=70; – 默认50%

obclient [oceanbase]> ALTER SYSTEM SET memstore_limit_percentage=80;
Query OK, 0 rows affected (0.012 sec)

obclient [oceanbase]>

3.2.1 遇到的错误是由于参数顺序设置不正确

obclient [oceanbase]> ALTER SYSTEM SET freeze_trigger_percentage=70;
ERROR 1210 (HY000): Incorrect arguments to tenant freeze_trigger_percentage which should smaller than writing_throttling_trigger_percentage
obclient [oceanbase]>

您遇到的错误是由于参数顺序设置不正确导致的。在OceanBase中，内存管理相关的参数需要满足以下层级关系：

参数层级关系：

memstore_limit_percentage (80)
   ↑
writing_throttling_trigger_percentage (需 < 80)
   ↑
freeze_trigger_percentage (需 < writing_throttling_trigger_percentage)

3.2.2 解决方案步骤：

先调整 writing_throttling_trigger_percentage
您需要先设置一个比目标 freeze_trigger_percentage 更高的值：

obclient [oceanbase]> ALTER SYSTEM SET writing_throttling_trigger_percentage = 75;
Query OK, 0 rows affected (0.010 sec)

obclient [oceanbase]>

再设置 freeze_trigger_percentage
现在可以安全地将 freeze 阈值设为 70：

obclient [oceanbase]> ALTER SYSTEM SET freeze_trigger_percentage = 70;
Query OK, 0 rows affected (0.010 sec)

obclient [oceanbase]>

参数说明：

memstore_limit_percentage：内存存储区最大占用百分比（您已设为80）
writing_throttling_trigger_percentage：触发写入限流的阈值（需 < memstore_limit）
freeze_trigger_percentage：触发内存冻结的阈值（需 < 限流阈值）

3.2.3 验证当前参数值：

SHOW PARAMETERS LIKE 'memstore_limit_percentage';
SHOW PARAMETERS LIKE 'writing_throttling_trigger_percentage';
SHOW PARAMETERS LIKE 'freeze_trigger_percentage';

obclient [oceanbase]> SHOW PARAMETERS LIKE 'memstore_limit_percentage';
+-------+----------+-----------+----------+---------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone  | svr_type | svr_ip    | svr_port | name                      | data_type | value | info                                                                                                                                                                                                         | section | scope   | source  | edit_level        |
+-------+----------+-----------+----------+---------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone1 | observer | 127.0.0.1 |     2882 | memstore_limit_percentage | NULL      | 80    | used in calculating the value of MEMSTORE_LIMIT parameter: memstore_limit_percentage = memstore_limit / memory_size,memory_size, where MEMORY_SIZE is determined when the tenant is created. Range: (0, 100) | TENANT  | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------+----------+---------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
1 row in set (0.004 sec)

obclient [oceanbase]> SHOW PARAMETERS LIKE 'writing_throttling_trigger_percentage';
+-------+----------+-----------+----------+---------------------------------------+-----------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone  | svr_type | svr_ip    | svr_port | name                                  | data_type | value | info                                                                                                                                    | section | scope  | source  | edit_level        |
+-------+----------+-----------+----------+---------------------------------------+-----------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone1 | observer | 127.0.0.1 |     2882 | writing_throttling_trigger_percentage | NULL      | 75    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0,100]. setting 100 means turn off writing limit | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------+----------+---------------------------------------+-----------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
1 row in set (0.004 sec)

obclient [oceanbase]> SHOW PARAMETERS LIKE 'freeze_trigger_percentage';
+-------+----------+-----------+----------+---------------------------+-----------+-------+----------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone  | svr_type | svr_ip    | svr_port | name                      | data_type | value | info                                                                                   | section | scope  | source  | edit_level        |
+-------+----------+-----------+----------+---------------------------+-----------+-------+----------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone1 | observer | 127.0.0.1 |     2882 | freeze_trigger_percentage | NULL      | 70    | the threshold of the size of the mem store when freeze will be triggered. Rang:(0,100) | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------+----------+---------------------------+-----------+-------+----------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
1 row in set (0.003 sec)

obclient [oceanbase]>

建议配置顺序：

1. 先设置 memstore_limit_percentage（内存总上限）
2. 再设置 writing_throttling_trigger_percentage（预防性限流阈值）
3. 最后设置 freeze_trigger_percentage（最终冻结阈值）

这种层级设计是为了确保系统在内存接近上限时，先通过限流减缓写入速度，若内存继续增长到冻结阈值时才完全暂停写入，从而避免内存溢出。

四、会话与连接分析

1. 活跃会话监控

关键问题解析

列名不匹配
- 原始查询使用了 status = 'ACTIVE'，但视图实际列名为 state。
- 通过 DESCRIBE 确认后，需将条件改为 state = 'ACTIVE'。
状态值验证
- 如果 state = 'ACTIVE' 无法返回预期结果，需通过以下查询确认有效状态值：
```
SELECT DISTINCT state FROM __all_virtual_session_info;
```
- 常见状态值可能包括：ACTIVE, INACTIVE, KILLED（具体取决于 OceanBase 版本）。
空值处理
- 使用 COALESCE(cnt, 0) 替代 CASE WHEN，使代码更简洁。

1.1 查看所有会话状态

obclient [oceanbase]>    SELECT state, COUNT(*) 
 ->    FROM __all_virtual_session_info 
 ->    GROUP BY state;
+--------+----------+
| state  | COUNT(*) |
+--------+----------+
| ACTIVE |        1 |
+--------+----------+
1 row in set (0.003 sec)

obclient [oceanbase]>

1.2 查看活跃会话详细信息

obclient [oceanbase]>    SELECT * 
 ->    FROM __all_virtual_session_info 
 ->    WHERE state = 'ACTIVE' 
 ->    LIMIT 10;
+------------+------+--------+-----------------+-----------+---------+----------------------------------+------+--------+-----------------------------------------------------------------------------------------+-----------+----------+----------+--------------+---------------+----------------+-----------+----------+-----------+------------+-----------------------------------+-----------+-----------+-------------+
| id         | user | tenant | host            | db        | command | sql_id                           | time | state  | info                                                                                    | svr_ip    | svr_port | sql_port | proxy_sessid | master_sessid | user_client_ip | user_host | trans_id | thread_id | ssl_cipher | trace_id                          | ref_count | backtrace | trans_state |
+------------+------+--------+-----------------+-----------+---------+----------------------------------+------+--------+-----------------------------------------------------------------------------------------+-----------+----------+----------+--------------+---------------+----------------+-----------+----------+-----------+------------+-----------------------------------+-----------+-----------+-------------+
| 3221487619 | root | sys    | 127.0.0.1:57386 | oceanbase | Query   | 3195C32D6C458B0E8C1DC7CEAC0585A5 |    0 | ACTIVE |    SELECT * 
FROM __all_virtual_session_info 
WHERE state = 'ACTIVE' 
LIMIT 10 | 127.0.0.1 |     2882 |     2881 |         NULL |          NULL | 127.0.0.1      | %         |        0 |      1995 | NULL       | YB427F000001-00063513AE028541-0-0 |         0 |           |             |
+------------+------+--------+-----------------+-----------+---------+----------------------------------+------+--------+-----------------------------------------------------------------------------------------+-----------+----------+----------+--------------+---------------+----------------+-----------+----------+-----------+------------+-----------------------------------+-----------+-----------+-------------+
1 row in set (0.002 sec)

obclient [oceanbase]>

注意事项

权限要求：访问系统视图需要 SELECT 权限，执行 SHOW GRANTS FOR CURRENT_USER(); 确认权限。
版本差异：不同 OceanBase 版本的系统视图可能不同，建议通过 DESCRIBE 确认结构。
性能影响：频繁查询系统视图可能对性能有影响，建议在低峰时段执行。

2. 验证 tenant 列内容

obclient [oceanbase]>    SELECT DISTINCT tenant 
 ->    FROM __all_virtual_session_info 
 ->    LIMIT 10;
+--------+
| tenant |
+--------+
| sys    |
+--------+
1 row in set (0.002 sec)

obclient [oceanbase]>

注意事项

权限要求：访问系统表需要 SELECT 权限，执行 SHOW GRANTS FOR CURRENT_USER(); 确认权限。
性能影响：频繁查询系统视图可能对性能有影响，建议在低峰时段执行。
版本差异：不同 OceanBase 版本的系统视图可能不同，建议通过 DESCRIBE 确认结构。

3. 查看所有租户信息

obclient [oceanbase]> SELECT tenant_id, tenant_name FROM __all_tenant;
+-----------+-------------+
| tenant_id | tenant_name |
+-----------+-------------+
|         1 | sys         |
|      1001 | META$1002   |
|      1002 | mq_t1       |
+-----------+-------------+
3 rows in set (0.002 sec)

obclient [oceanbase]>

4. 查看活跃会话的租户标识符

obclient [oceanbase]> SELECT DISTINCT tenant FROM __all_virtual_session_info WHERE state = 'ACTIVE';
+--------+
| tenant |
+--------+
| sys    |
+--------+
1 row in set (0.002 sec)

obclient [oceanbase]>

5. 验证关联查询结果

obclient [oceanbase]>    SELECT
 ->      t.tenant_id,
 ->      t.tenant_name,
 ->      COUNT(s.tenant) AS active_sessions
 ->    FROM __all_tenant t
 ->    LEFT JOIN __all_virtual_session_info s 
 ->      ON t.tenant_name = s.tenant
 ->      AND s.state = 'ACTIVE'
 ->    GROUP BY t.tenant_id, t.tenant_name
 ->    ORDER BY active_sessions DESC;
+-----------+-------------+-----------------+
| tenant_id | tenant_name | active_sessions |
+-----------+-------------+-----------------+
|         1 | sys         |               1 |
|      1001 | META$1002   |               0 |
|      1002 | mq_t1       |               0 |
+-----------+-------------+-----------------+
3 rows in set (0.012 sec)

obclient [oceanbase]>

注意事项

权限要求：访问系统表需要 SELECT 权限，执行 SHOW GRANTS FOR CURRENT_USER(); 确认权限。
版本差异：不同 OceanBase 版本的系统表可能不同，建议通过 DESCRIBE 确认结构。
租户隔离：如果查询权限受限，可能需要以 sys 租户身份执行。

五、合并与转储监控

1. 合并状态检查

-- 查看所有租户合并状态
obclient [oceanbase]> SELECT
    -> tenant_id,
    -> global_broadcast_scn AS broadcast_scn,
    -> is_error AS error,
    -> status,
    -> frozen_scn,
    -> last_scn,
    -> is_suspended AS suspend,
    -> info,
    -> start_time,
    -> last_finish_time
    -> FROM CDB_OB_MAJOR_COMPACTION;
+-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
| tenant_id | broadcast_scn       | error | status | frozen_scn          | last_scn            | suspend | info | start_time                 | last_finish_time           |
+-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
|         1 | 1747295189643832804 | NO    | IDLE   | 1747295189643832804 | 1747295189643832804 | NO      |      | 2025-05-15 15:46:29.695460 | 2025-05-15 15:47:23.096113 |
|      1001 |                   1 | NO    | IDLE   |                   1 |                   1 | NO      |      | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |
|      1002 |                   1 | NO    | IDLE   |                   1 |                   1 | NO      |      | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |
+-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
3 rows in set (0.004 sec)

obclient [oceanbase]>

关键字段：

status：IDLE表示未在合并中
is_error：NO表示合并过程无错误
last_finish_time：上次合并完成时间

2. 合并进度查询

obclient [oceanbase]> SELECT
    ->   TENANT_ID,
    ->   COMPACTION_SCN,
    ->   CONCAT(100*(1-SUM(UNFINISHED_TABLET_COUNT)/SUM(TOTAL_TABLET_COUNT)),'%')
    -> FROM GV$OB_COMPACTION_PROGRESS;
+-----------+---------------------+--------------------------------------------------------------------------+
| TENANT_ID | COMPACTION_SCN      | CONCAT(100*(1-SUM(UNFINISHED_TABLET_COUNT)/SUM(TOTAL_TABLET_COUNT)),'%') |
+-----------+---------------------+--------------------------------------------------------------------------+
|         1 | 1747295189643832804 | 100.0000%                                                                |
+-----------+---------------------+--------------------------------------------------------------------------+
1 row in set (0.000 sec)

obclient [oceanbase]>

说明：进度接近100%表示合并即将完成

2.1 合并进度查询返回 `NULL` 没有数据

obclient [oceanbase]> SELECT
    ->   TENANT_ID,
    ->   COMPACTION_SCN,
    ->   CONCAT(100*(1-SUM(UNFINISHED_TABLET_COUNT)/SUM(TOTAL_TABLET_COUNT)),'%')
    -> FROM GV$OB_COMPACTION_PROGRESS;
+-----------+----------------+--------------------------------------------------------------------------+
| TENANT_ID | COMPACTION_SCN | CONCAT(100*(1-SUM(UNFINISHED_TABLET_COUNT)/SUM(TOTAL_TABLET_COUNT)),'%') |
+-----------+----------------+--------------------------------------------------------------------------+
|      NULL |           NULL | NULL                                                                     |
+-----------+----------------+--------------------------------------------------------------------------+
1 row in set (0.003 sec)

obclient [oceanbase]>

如果遇到的查询返回 NULL 的问题可能由以下原因导致：

2.2 视图名称或列名错误

现象：视图或列名拼写错误。
验证方法：查看视图结构：

obclient [oceanbase]> DESCRIBE GV$OB_COMPACTION_PROGRESS;
+-------------------------+---------------------+------+-----+---------+-------+
| Field                   | Type                | Null | Key | Default | Extra |
+-------------------------+---------------------+------+-----+---------+-------+
| SVR_IP                  | varchar(46)         | NO   |     | NULL    |       |
| SVR_PORT                | bigint(20)          | NO   |     | NULL    |       |
| TENANT_ID               | bigint(20)          | NO   |     | NULL    |       |
| TYPE                    | varchar(64)         | NO   |     | NULL    |       |
| ZONE                    | varchar(128)        | NO   |     | NULL    |       |
| COMPACTION_SCN          | bigint(20) unsigned | NO   |     | NULL    |       |
| STATUS                  | varchar(15)         | NO   |     | NULL    |       |
| TOTAL_TABLET_COUNT      | bigint(20)          | NO   |     | NULL    |       |
| UNFINISHED_TABLET_COUNT | bigint(20)          | NO   |     | NULL    |       |
| DATA_SIZE               | bigint(20)          | NO   |     | NULL    |       |
| UNFINISHED_DATA_SIZE    | bigint(20)          | NO   |     | NULL    |       |
| COMPRESSION_RATIO       | double              | NO   |     | NULL    |       |
| START_TIME              | timestamp(6)        | NO   |     | NULL    |       |
| ESTIMATED_FINISH_TIME   | timestamp(6)        | NO   |     | NULL    |       |
| COMMENTS                | varchar(256)        | NO   |     | NULL    |       |
+-------------------------+---------------------+------+-----+---------+-------+
15 rows in set (0.002 sec)

obclient [oceanbase]>

解决方案：
- 确认视图是否存在，OceanBase 社区版中常用视图为 GV$OB_COMPACTION_PROGRESS。
- 确认列名是否为 UNFINISHED_TABLET_COUNT 和 TOTAL_TABLET_COUNT（注意大小写）。

2.3 无活跃合并任务

现象：当前无合并任务进行，导致所有字段为 NULL。
验证方法：检查合并状态：

obclient [oceanbase]> SELECT * FROM GV$OB_COMPACTION_PROGRESS;
Empty set (0.002 sec)

obclient [oceanbase]>

解决方案：

2.4 手动触发合并（谨慎操作）：

obclient [oceanbase]> 
obclient [oceanbase]> ALTER SYSTEM MAJOR FREEZE;
Query OK, 0 rows affected (0.009 sec)

obclient [oceanbase]>

3. 全量查询查看当前合并进度

obclient [oceanbase]> SELECT * FROM GV$OB_COMPACTION_PROGRESS;
+-----------+----------+-----------+-------------+-------+---------------------+---------+--------------------+-------------------------+-----------+----------------------+-------------------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------+
| SVR_IP    | SVR_PORT | TENANT_ID | TYPE        | ZONE  | COMPACTION_SCN      | STATUS  | TOTAL_TABLET_COUNT | UNFINISHED_TABLET_COUNT | DATA_SIZE | UNFINISHED_DATA_SIZE | COMPRESSION_RATIO | START_TIME                 | ESTIMATED_FINISH_TIME      | COMMENTS                                                                                |
+-----------+----------+-----------+-------------+-------+---------------------+---------+--------------------+-------------------------+-----------+----------------------+-------------------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------+
| 127.0.0.1 |     2882 |         1 | MAJOR_MERGE | zone1 | 1747136632597114473 | INITING |                879 |                     879 |         0 |                    0 |                 1 | 2025-05-13 19:43:54.446449 | 2025-05-13 19:46:44.877357 | GET_FREEZE_INFO:new_freeze_info_cnt=1,latest_freeze_scn={val:1747136632597114473, v:0}, |
+-----------+----------+-----------+-------------+-------+---------------------+---------+--------------------+-------------------------+-----------+----------------------+-------------------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

obclient [oceanbase]>

4. 聚合查询 OceanBase 的合并进度查询

obclient [oceanbase]> SELECT
    ->     TENANT_ID,
    ->     COMPACTION_SCN,
    ->     CONCAT(100*(1-SUM(UNFINISHED_TABLET_COUNT)/SUM(TOTAL_TABLET_COUNT)),'%')
    ->     FROM GV$OB_COMPACTION_PROGRESS;
+-----------+---------------------+--------------------------------------------------------------------------+
| TENANT_ID | COMPACTION_SCN      | CONCAT(100*(1-SUM(UNFINISHED_TABLET_COUNT)/SUM(TOTAL_TABLET_COUNT)),'%') |
+-----------+---------------------+--------------------------------------------------------------------------+
|         1 | 1747136632597114473 | 100.0000%                                                                |
+-----------+---------------------+--------------------------------------------------------------------------+
1 row in set (0.002 sec)

obclient [oceanbase]>

等待后台自动合并完成。

5. 手动触发新合并（谨慎操作）后：

查询监控OceanBase数据库中各租户合并（Compaction）任务的进度

obclient [oceanbase]> SELECT
    ->   TENANT_ID,
    ->   COMPACTION_SCN,
    ->   CONCAT(
    ->     100 * (1 - 
    ->       COALESCE(SUM(UNFINISHED_TABLET_COUNT), 0) / 
    ->       COALESCE(SUM(TOTAL_TABLET_COUNT), 1)  -- 避免除以0
    ->     ), '%'
    ->   ) AS COMPACTION_PROGRESS
    -> FROM GV$OB_COMPACTION_PROGRESS
    -> GROUP BY TENANT_ID, COMPACTION_SCN;
+-----------+---------------------+---------------------+
| TENANT_ID | COMPACTION_SCN      | COMPACTION_PROGRESS |
+-----------+---------------------+---------------------+
|         1 | 1747136632597114473 | 100.0000%           |
|         1 | 1747137144301411644 | 0.0000%             |
+-----------+---------------------+---------------------+
2 rows in set (0.000 sec)

obclient [oceanbase]>

6. 全量查询监控合并进度：

obclient [oceanbase]> SELECT * FROM GV$OB_COMPACTION_PROGRESS;
+-----------+----------+-----------+-------------+-------+---------------------+--------------+--------------------+-------------------------+-----------+----------------------+-------------------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------+
| SVR_IP    | SVR_PORT | TENANT_ID | TYPE        | ZONE  | COMPACTION_SCN      | STATUS       | TOTAL_TABLET_COUNT | UNFINISHED_TABLET_COUNT | DATA_SIZE | UNFINISHED_DATA_SIZE | COMPRESSION_RATIO | START_TIME                 | ESTIMATED_FINISH_TIME      | COMMENTS                                                                                |
+-----------+----------+-----------+-------------+-------+---------------------+--------------+--------------------+-------------------------+-----------+----------------------+-------------------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------+
| 127.0.0.1 |     2882 |         1 | MAJOR_MERGE | zone1 | 1747136632597114473 | FINISH       |                879 |                       0 |  14518089 |                    0 |                 1 | 2025-05-13 19:43:54.446449 | 2025-05-13 19:47:26.787823 | EXECUTE=2.20s|(0.10)|UPDATE_TABLET=20.12s|(0.88)|total=22.78s;                          |
| 127.0.0.1 |     2882 |         1 | MAJOR_MERGE | zone1 | 1747137144301411644 | NODE_RUNNING |                879 |                       0 | 189597964 |            186029233 |                 1 | 2025-05-13 19:52:24.881227 | 2025-05-13 19:56:30.724288 | SCHEDULER_LOOP:schedule_stats={schedule_cnt:879, finish_cnt:0, wait_rs_validate_cnt:0}, |
+-----------+----------+-----------+-------------+-------+---------------------+--------------+--------------------+-------------------------+-----------+----------------------+-------------------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

obclient [oceanbase]>

7. 再次查询监控OceanBase数据库中各租户合并（Compaction）任务的进度

obclient [oceanbase]> SELECT
    ->   TENANT_ID,
    ->   COMPACTION_SCN,
    ->   CONCAT(
    ->     100 * (1 - 
    ->       COALESCE(SUM(UNFINISHED_TABLET_COUNT), 0) / 
    ->       COALESCE(SUM(TOTAL_TABLET_COUNT), 1)  -- 避免除以0
    ->     ), '%'
    ->   ) AS COMPACTION_PROGRESS
    -> FROM GV$OB_COMPACTION_PROGRESS
    -> GROUP BY TENANT_ID, COMPACTION_SCN;
+-----------+---------------------+---------------------+
| TENANT_ID | COMPACTION_SCN      | COMPACTION_PROGRESS |
+-----------+---------------------+---------------------+
|         1 | 1747136632597114473 | 100.0000%           |
|         1 | 1747137144301411644 | 100.0000%           |
+-----------+---------------------+---------------------+
2 rows in set (0.000 sec)

obclient [oceanbase]>

六、全链路诊断

1. 动态启用跟踪日志（无需重启）

obclient [oceanbase]> ALTER SYSTEM SET syslog_level = 'TRACE';
Query OK, 0 rows affected (0.018 sec)

obclient [oceanbase]>

2. 确认当前用户权限

obclient [oceanbase]> SHOW GRANTS;
+----------------------------------------------------------+
| Grants for root@%                                        |
+----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root' WITH GRANT OPTION  |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'               |
| GRANT ALL PRIVILEGES ON `oceanbase`.* TO 'root'          |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'root'              |
| GRANT ALL PRIVILEGES ON `SYS`.* TO 'root'                |
| GRANT ALL PRIVILEGES ON `ORAAUDITOR`.* TO 'root'         |
| GRANT ALL PRIVILEGES ON `LBACSYS`.* TO 'root'            |
| GRANT ALL PRIVILEGES ON `information_schema`.* TO 'root' |
| GRANT ALL PRIVILEGES ON `__recyclebin`.* TO 'root'       |
| GRANT ALL PRIVILEGES ON `__public`.* TO 'root'           |
+----------------------------------------------------------+
10 rows in set (0.002 sec)

obclient [oceanbase]>

3. 测试数据准备

3.1 – 创建测试表（若不存在）

obclient [oceanbase]> CREATE TABLE IF NOT EXISTS orders (
    ->   order_id BIGINT PRIMARY KEY,
    ->   order_date DATE,
    ->   customer_id BIGINT,
    ->   amount DECIMAL(15,2)
    -> );
Query OK, 0 rows affected (0.079 sec)

obclient [oceanbase]>

3.2 – 生成测试数据（10万行）

obclient [oceanbase]> SET ob_query_timeout = 600000000;
Query OK, 0 rows affected (0.000 sec)

obclient [oceanbase]> INSERT INTO orders 
    -> SELECT 
    ->   seq, 
    ->   DATE_SUB(NOW(), INTERVAL seq DAY), 
    ->   FLOOR(RAND()*100000), 
    ->   FLOOR(RAND()*10000)
    -> FROM (
    ->   WITH RECURSIVE seq(seq) AS (
    ->     SELECT 1
    ->     UNION ALL
    ->     SELECT seq + 1 FROM seq 
    ->     WHERE seq < 100000
    ->   )
    ->   SELECT seq FROM seq
    -> ) t;
Query OK, 100000 rows affected (1 min 36.614 sec)
Records: 100000  Duplicates: 0  Warnings: 0

obclient [oceanbase]>

3.2.1 – 问题 1：递归 CTE 迭代次数限制

错误：Recursive query aborted after 1001 iterations

解决方案

调整递归深度
在会话级别修改 cte_max_recursion_depth 参数：

   SET @@cte_max_recursion_depth = 100000;

重新执行递归 CTE 插入

obclient [oceanbase]> INSERT INTO orders 
    -> WITH RECURSIVE seq(n) AS (
    ->   SELECT 1 AS n
    ->   UNION ALL
    ->   SELECT n + 1 FROM seq WHERE n < 10000
    -> )
    -> SELECT 
    ->   n AS seq,
    ->   DATE_SUB(NOW(), INTERVAL n DAY) AS order_date,
    ->   RAND() * 100 AS amount,
    ->   RAND() * 100 AS discount
    -> FROM seq;
Query OK, 10000 rows affected (1.040 sec)
Records: 10000  Duplicates: 0  Warnings: 0

obclient [oceanbase]>

关键问题解析
递归深度限制

OceanBase 默认限制递归 CTE 的迭代次数（cte_max_recursion_depth），需手动调整。
调整后需重新连接会话或执行 SET 命令生效。

###4. 检查递归深度设置

   obclient [oceanbase]> SHOW VARIABLES LIKE 'cte_max_recursion_depth';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| cte_max_recursion_depth | 100000 |
+-------------------------+--------+
1 row in set (0.004 sec)

obclient [oceanbase]>

###5. 验证插入结果

5.1 检查订单数据

SELECT COUNT(*) FROM orders; – 应返回 10000

bclient [oceanbase]> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.012 sec)

obclient [oceanbase]>

注意事项

权限要求：修改系统变量需要 SUPER 权限，创建表需要 CREATE 权限。
性能影响：递归 CTE 在大数据量时可能较慢，建议预先生成数字辅助表。
事务控制：大数据量插入时，建议分批提交避免事务过大。

5.2 – 验证总行数

obclient [oceanbase]> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.052 sec)

obclient [oceanbase]>

5.3 --验证最新数据

obclient [oceanbase]> 
obclient [oceanbase]> SELECT * FROM orders 
    -> ORDER BY order_id DESC 
    -> LIMIT 10;
+----------+------------+-------------+---------+
| order_id | order_date | customer_id | amount  |
+----------+------------+-------------+---------+
|   100000 | 1751-07-30 |       53677 | 6057.00 |
|    99999 | 1751-07-31 |       99795 | 9551.00 |
|    99998 | 1751-08-01 |       48433 | 3899.00 |
|    99997 | 1751-08-02 |       47457 | 6648.00 |
|    99996 | 1751-08-03 |       29617 | 6447.00 |
|    99995 | 1751-08-04 |       33543 | 8530.00 |
|    99994 | 1751-08-05 |       79366 | 2067.00 |
|    99993 | 1751-08-06 |       54429 | 3935.00 |
|    99992 | 1751-08-07 |       97593 | 5870.00 |
|    99991 | 1751-08-08 |       77846 | 8472.00 |
+----------+------------+-------------+---------+
10 rows in set (0.002 sec)

obclient [oceanbase]>

5.4 – 验证日期范围

obclient [oceanbase]> SELECT 
    ->   MIN(order_date) AS earliest_date,
    ->   MAX(order_date) AS latest_date
    -> FROM orders;
+---------------+-------------+
| earliest_date | latest_date |
+---------------+-------------+
| 1751-07-30    | 2025-05-13  |
+---------------+-------------+
1 row in set (0.041 sec)

obclient [oceanbase]>

– 预期输出：earliest_date = ‘2024-12-27’（假设当前日期为2025-05-13）

5.5 – 验证金额分布

obclient [oceanbase]> SELECT 
    ->   AVG(amount) AS avg_amount,
    ->   MAX(amount) AS max_amount
    -> FROM orders;
+-------------+------------+
| avg_amount  | max_amount |
+-------------+------------+
| 5004.004430 |    9999.00 |
+-------------+------------+
1 row in set (0.039 sec)

obclient [oceanbase]>

– 预期输出：avg_amount ≈ 5000, max_amount ≈ 10000

6. 确认变量兼容性 – 预期输出（关键变量）：

obclient [oceanbase]> SHOW VARIABLES LIKE '%log%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| binlog_checksum              | CRC32    |
| binlog_format                | ROW      |
| binlog_rows_query_log_events | OFF      |
| binlog_row_image             | FULL     |
| general_log                  | OFF      |
| log_bin                      | ON       |
| log_row_value_options        |          |
| ob_log_level                 | disabled |
| sql_throttle_logical_reads   | -1       |
+------------------------------+----------+
9 rows in set (0.002 sec)

obclient [oceanbase]>

7. 使用 EXPLAIN 分析执行计划

obclient [oceanbase]> EXPLAIN SELECT * FROM orders 
    -> WHERE order_date > '2024-01-01' 
    ->   AND customer_id = 12345;
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =================================================                                                                                                          |
| |ID|OPERATOR       |NAME  |EST.ROWS|EST.TIME(us)|                                                                                                          |
| -------------------------------------------------                                                                                                          |
| |0 |TABLE FULL SCAN|orders|100     |5943        |                                                                                                          |
| =================================================                                                                                                          |
| Outputs & filters:                                                                                                                                         |
| -------------------------------------                                                                                                                      |
|   0 - output([orders.order_id], [orders.order_date], [orders.customer_id], [orders.amount]), filter([orders.customer_id = 12345], [cast(orders.order_date, |
|        DATETIME(-1, -1)) > INTERNAL_FUNCTION('2024-01-01', 115, 17)]), rowset=256                                                                          |
|       access([orders.order_id], [orders.order_date], [orders.customer_id], [orders.amount]), partitions(p0)                                                |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false,false],                                                                    |
|       range_key([orders.order_id]), range(MIN ; MAX)always true                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.002 sec)

obclient [oceanbase]>

七、索引创建与优化指南

1. 创建复合索引（MySQL 模式）

将目标列全部加入索引键

obclient [oceanbase]> CREATE INDEX idx_date_cust_amount 
    -> ON orders(order_date, customer_id, amount);
Query OK, 0 rows affected (0.339 sec)

obclient [oceanbase]>

2. 索引结构验证

2.1 – 查询索引元数据

SHOW INDEX FROM orders
WHERE Key_name = ‘idx_date_cust_amount’;

obclient [oceanbase]> SHOW INDEX FROM orders 
    -> WHERE Key_name = 'idx_date_cust_amount';
+--------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table  | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
+--------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| orders |          1 | idx_date_cust_amount |            1 | order_date  | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
| orders |          1 | idx_date_cust_amount |            2 | customer_id | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
| orders |          1 | idx_date_cust_amount |            3 | amount      | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
+--------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
3 rows in set (0.003 sec)

obclient [oceanbase]>

3. – 性能优化验证

3.1 – 强制使用新索引的查询

obclient [oceanbase]> 
obclient [oceanbase]> SELECT /*+ INDEX(orders idx_date_cust_amount) */ * 
    -> FROM orders 
    -> WHERE order_date > '2024-01-01' 
    ->   AND customer_id = 12345;
Empty set (0.003 sec)

obclient [oceanbase]>

3.2 – 执行计划分析

– 获取优化后执行计划

obclient [oceanbase]> 
obclient [oceanbase]> EXPLAIN SELECT /*+ INDEX(orders idx_date_cust_amount) */ * 
    -> FROM orders 
    -> WHERE order_date > '2024-01-01' 
    ->   AND customer_id = 12345;
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================================                                                                            |
| |ID|OPERATOR        |NAME                        |EST.ROWS|EST.TIME(us)|                                                                            |
| ------------------------------------------------------------------------                                                                            |
| |0 |TABLE RANGE SCAN|orders(idx_date_cust_amount)|5       |64          |                                                                            |
| ========================================================================                                                                            |
| Outputs & filters:                                                                                                                                  |
| -------------------------------------                                                                                                               |
|   0 - output([orders.order_id], [orders.order_date], [orders.customer_id], [orders.amount]), filter([orders.customer_id = 12345]), rowset=16        |
|       access([orders.order_id], [orders.order_date], [orders.customer_id], [orders.amount]), partitions(p0)                                         |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                   |
|       range_key([orders.order_date], [orders.customer_id], [orders.amount], [orders.order_id]), range(2024-01-01,MAX,MAX,MAX ; MAX,12345,MAX,MAX),  |
|       range_cond([cast(orders.order_date, DATETIME(-1, -1)) > INTERNAL_FUNCTION('2024-01-01', 115, 17)])                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.003 sec)

obclient [oceanbase]>

4. 索引监控

– 查询索引使用统计,确认索引包含所有必要列

obclient [oceanbase]> 
obclient [oceanbase]> SELECT 
    ->   index_name,
    ->   column_name,
    ->   seq_in_index
    -> FROM information_schema.statistics 
    -> WHERE table_name = 'orders'
    ->   AND index_name = 'idx_date_cust_amount';
+----------------------+-------------+--------------+
| index_name           | column_name | seq_in_index |
+----------------------+-------------+--------------+
| idx_date_cust_amount | order_date  |            1 |
| idx_date_cust_amount | customer_id |            2 |
| idx_date_cust_amount | amount      |            3 |
+----------------------+-------------+--------------+
3 rows in set (0.162 sec)

obclient [oceanbase]>

5. 锁类型细分-- 区分行锁、表锁、元数据锁

obclient [oceanbase]> SELECT 
    ->   EVENT,
    ->   CASE 
    ->     WHEN EVENT LIKE '%row lock%' THEN 'Row Lock'
    ->     WHEN EVENT LIKE '%table lock%' THEN 'Table Lock'
    ->     ELSE 'Other Lock'
    ->   END AS lock_type,
    ->   SUM(TOTAL_WAITS) AS total_waits
    -> FROM gv$system_event
    -> WHERE EVENT LIKE '%lock%'
    -> GROUP BY lock_type, EVENT;
+------------------------------------------------------------+------------+-------------+
| EVENT                                                      | lock_type  | total_waits |
+------------------------------------------------------------+------------+-------------+
| memstore read lock wait                                    | Other Lock |           0 |
| memstore write lock wait                                   | Other Lock |           0 |
| row lock wait                                              | Row Lock   |           0 |
| latch wait queue lock wait                                 | Other Lock |         124 |
| slog checkpoint lock wait                                  | Other Lock |           0 |
| rwlock: tenant io pool wait                                | Other Lock |           0 |
| latch: display tasks lock wait                             | Other Lock |           0 |
| tablet lock wait                                           | Other Lock |           0 |
| latch:index name cache lock wait                           | Other Lock |           0 |
| latch: latch wait queue lock wait                          | Other Lock |           0 |
| latch: default spin lock wait                              | Other Lock |           0 |
| latch: default spin rwlock wait                            | Other Lock |          12 |
| latch: default drw lock wait                               | Other Lock |           0 |
| latch: default bucket lock wait                            | Other Lock |           0 |
| latch: trans ctx bucket lock wait                          | Other Lock |           0 |
| latch: macro writer lock wait                              | Other Lock |           0 |
| latch: token bucket lock wait                              | Other Lock |           0 |
| latch: light hashmap bucket lock wait                      | Other Lock |           0 |
| latch: row callback lock wait                              | Other Lock |           0 |
| latch: switch leader lock wait                             | Other Lock |           0 |
| latch: partition freeze lock wait                          | Other Lock |           0 |
| latch: schema service lock wait                            | Other Lock |           0 |
| latch: schema service stats lock wait                      | Other Lock |           0 |
| latch: tenant lock wait                                    | Other Lock |           0 |
| latch: config lock wait                                    | Other Lock |          20 |
| latch: major freeze lock wait                              | Other Lock |           0 |
| latch: partition table updater lock wait                   | Other Lock |           0 |
| latch: multi tenant lock wait                              | Other Lock |           0 |
| latch: leader coordinator lock wait                        | Other Lock |           0 |
| latch: leader stat lock wait                               | Other Lock |           0 |
| latch: major freeze service lock wait                      | Other Lock |           0 |
| latch: rs bootstrap lock wait                              | Other Lock |           3 |
| latch: schema mgr item lock wait                           | Other Lock |           0 |
| latch: schema mgr lock wait                                | Other Lock |           0 |
| latch: super block lock wait                               | Other Lock |           0 |
| latch: frozen version lock wait                            | Other Lock |           0 |
| latch: rs broadcast lock wait                              | Other Lock |           2 |
| latch: server status lock wait                             | Other Lock |           0 |
| latch: server maintaince lock wait                         | Other Lock |           0 |
| latch: unit manager lock wait                              | Other Lock |           0 |
| latch: zone manager maintaince lock wait                   | Other Lock |           0 |
| latch: object set lock wait                                | Other Lock |           0 |
| latch: block set lock wait                                 | Other Lock |           0 |
| latch: normal trace recorder lock wait                     | Other Lock |           0 |
| latch: session trace recorder lock wait                    | Other Lock |           0 |
| latch: trans trace recorder lock wait                      | Other Lock |           0 |
| latch: election trace recorder lock wait                   | Other Lock |           0 |
| latch: alive server tracer lock wait                       | Other Lock |           0 |
| latch: allocate chunk lock wait                            | Other Lock |           0 |
| latch: allocate tenant lock wait                           | Other Lock |           0 |
| latch: io queue lock wait                                  | Other Lock |           0 |
| latch: zone infos rw lock wait                             | Other Lock |           0 |
| latch: memtable trace recorder lock wait                   | Other Lock |           0 |
| latch: bandwidth throttle lock wait                        | Other Lock |           0 |
| latch: rs event table timestamp lock wait                  | Other Lock |           0 |
| latch: clog fd cache lock wait                             | Other Lock |           0 |
| latch: migrate lock wait                                   | Other Lock |           0 |
| latch: clog cascading info lock wait                       | Other Lock |           0 |
| latch: clog locality lock wait                             | Other Lock |           0 |
| latch: group migrate lock wait                             | Other Lock |           0 |
| latch: group migrate task lock wait                        | Other Lock |           0 |
| latch: log engine env switch lock wait                     | Other Lock |           0 |
| latch: rg transfer lock wait                               | Other Lock |           0 |
| latch: table mgr lock wait                                 | Other Lock |           0 |
| latch: partition store lock wait                           | Other Lock |           0 |
| latch: partition store change lock wait                    | Other Lock |           0 |
| latch: tablet memtable lock wait                           | Table Lock |           0 |
| latch: election group trace recorder lock wait             | Other Lock |           0 |
| latch: ObCacheLineSegregatedArrayBase alloc lock wait      | Other Lock |           0 |
| latch: server object pool arena lock wait                  | Other Lock |           0 |
| latch: id map node lock wait                               | Other Lock |           0 |
| latch: extent storage manager lock wait                    | Other Lock |           0 |
| latch: block manager lock wait                             | Other Lock |           0 |
| latch: rebuild retry list lock wait                        | Other Lock |           0 |
| latch: partition audit spin lock wait                      | Other Lock |           0 |
| latch: partition group lock wait                           | Other Lock |           0 |
| latch: px worker leader lock wait                          | Other Lock |           0 |
| latch: clog idc lock wait                                  | Other Lock |           0 |
| latch: tablet bucket lock wait                             | Other Lock |          13 |
| latch: ob allocator lock wait                              | Other Lock |           0 |
| latch: block id generator lock wait                        | Other Lock |           0 |
| latch: ob context lock wait                                | Other Lock |           0 |
| latch: ob pg index lock wait                               | Other Lock |           0 |
| latch: ob log archive scheduler lock wait                  | Other Lock |           0 |
| latch: mem dump iter lock wait                             | Other Lock |           0 |
| latch: ob request timeinfo list lock wait                  | Other Lock |           0 |
| latch: trans batch rpc lock wait                           | Other Lock |           0 |
| latch: backup info mgr lock wait                           | Other Lock |           0 |
| latch: clog sw renew ms task spin lock wait                | Other Lock |           0 |
| latch: hashmap latch lock wait                             | Other Lock |           0 |
| latch: file ref latch lock wait                            | Other Lock |           0 |
| latch: timezone lock wait                                  | Other Lock |           0 |
| latch: undo status lock wait                               | Other Lock |           0 |
| latch: freeze async worker lock wait                       | Other Lock |           0 |
| latch: xa stmt lock wait                                   | Other Lock |           0 |
| latch: xa queue lock wait                                  | Other Lock |           0 |
| latch: memtable calback list lock wait                     | Other Lock |           0 |
| latch: memtable calback list mgr lock wait                 | Other Lock |           0 |
| latch: master key lock wait                                | Other Lock |           0 |
| latch: ratelimit lock wait                                 | Other Lock |           0 |
| latch: transaction execution ctx lock wait                 | Other Lock |           0 |
| latch: tenant disk usage lock wait                         | Other Lock |           0 |
| latch: tenant meta memory manager lock wait                | Other Lock |           0 |
| latch: update trans id assignable interval lock wait       | Other Lock |           0 |
| latch: ID allocator updates assignable interval lock wait  | Other Lock |           0 |
| latch: trans records audit information lock wait           | Other Lock |           0 |
| latch: tablet multi source data lock wait                  | Other Lock |           0 |
| latch: PL DEBUG lock wait                                  | Other Lock |           0 |
| latch: PL sys breakpoint lock wait                         | Other Lock |           0 |
| latch: dbms_job task lock wait                             | Other Lock |           0 |
| latch: dbms_job master lock wait                           | Other Lock |           0 |
| latch: dtl channel list lock wait                          | Other Lock |           0 |
| latch: dtl free buffer list lock wait                      | Other Lock |           0 |
| latch: plan cache evict lock wait                          | Other Lock |           0 |
| latch: shared hash join lock wait                          | Other Lock |           0 |
| latch: sql work area profile list lock wait                | Other Lock |           0 |
| latch: sql work area stat map lock wait                    | Other Lock |           0 |
| latch: sql memory manager mutex lock wait                  | Other Lock |           0 |
| latch: load data rpc asyn callback lock wait               | Other Lock |           0 |
| latch: merge dynamic sampling piece message lock wait      | Other Lock |           0 |
| latch: granule iterator task queue lock wait               | Other Lock |           0 |
| latch: dtl receive channel provider access lock wait       | Other Lock |           0 |
| latch: parralel execution tenant target lock wait          | Other Lock |           0 |
| latch: parallel execution worker stat lock wait            | Other Lock |           0 |
| latch: session query lock wait                             | Other Lock |           0 |
| latch: session thread data lock wait                       | Other Lock |           0 |
| latch: maintain session pool lock wait                     | Other Lock |           0 |
| latch: sequence value alloc lock wait                      | Other Lock |           0 |
| latch: sequence cache lock wait                            | Other Lock |           0 |
| latch: obcdc progress recycle lock wait                    | Other Lock |           0 |
| latch: obcdc metainfo lock wait                            | Other Lock |           0 |
| latch: obcdc trans ctx lock wait                           | Other Lock |           0 |
| latch: obcdc svr blacklist lock wait                       | Other Lock |           0 |
| latch: obcdc sqlserver lock wait                           | Other Lock |           0 |
| latch: obcdc timezone getter lock wait                     | Other Lock |           0 |
| latch: obcdc fetchlog arpc lock wait                       | Other Lock |           0 |
| latch: obcdc fetchstream container lock wait               | Other Lock |           0 |
| latch: inner connection pool lock wait                     | Other Lock |           0 |
| latch: tenant resource mgr list lock wait                  | Other Lock |           0 |
| latch: tc free list lock wait                              | Other Lock |           0 |
| latch: dedup queue lock wait                               | Other Lock |           0 |
| latch: tablet table store lock wait                        | Other Lock |           0 |
| latch: tmp file lock wait                                  | Other Lock |           0 |
| latch: tmp file extent lock wait                           | Other Lock |           0 |
| latch: tmp file manager lock wait                          | Other Lock |           0 |
| latch: tmp file store lock wait                            | Other Lock |           0 |
| latch: tmp file macro lock wait                            | Other Lock |           0 |
| latch: sstable index builder lock wait                     | Other Lock |           0 |
| latch: slog checkpoint lock wait                           | Other Lock |           0 |
| latch: local device lock wait                              | Other Lock |           0 |
| latch: fixed size allocator lock wait                      | Other Lock |           0 |
| latch: dbms_scheduler task lock wait                       | Other Lock |           0 |
| latch: dbms_scheuler master lock wait                      | Other Lock |           0 |
| latch: tenant worker lock wait                             | Other Lock |           0 |
| latch: server locality cache lock wait                     | Other Lock |           0 |
| latch: master rs cache lock wait                           | Other Lock |           0 |
| latch: schema refresh info lock wait                       | Other Lock |           0 |
| latch: refresh schema lock wait                            | Other Lock |           5 |
| latch: refreshed schema cache lock wait                    | Other Lock |           0 |
| latch: schema mgr cache lock wait                          | Other Lock |           1 |
| latch: rs master key respone lock wait                     | Other Lock |           0 |
| latch: rs master key request lock wait                     | Other Lock |           0 |
| latch: rs master key mgr lock wait                         | Other Lock |           0 |
| latch: thread hang checker lock wait                       | Other Lock |           0 |
| latch: create inner schema executor lock wait              | Other Lock |           0 |
| latch: weak read server version lock wait                  | Other Lock |           0 |
| latch: transaction ls log writer lock wait                 | Other Lock |           0 |
| latch: transaction descriptor lock wait                    | Other Lock |           0 |
| latch: transaction descriptor commit lock wait             | Other Lock |           0 |
| latch: weak read service cluster service lock wait         | Other Lock |           1 |
| latch: transaction stat item lock wait                     | Other Lock |           0 |
| latch: weak read service cluster version manager lock wait | Other Lock |           0 |
| latch: major freeze switch lock wait                       | Other Lock |           0 |
| latch: zone merge manager read lock wait                   | Other Lock |           0 |
| latch: zone merge manager write lock wait                  | Other Lock |           1 |
| latch: auto increment init lock wait                       | Other Lock |           0 |
| latch: auto increment alloc lock wait                      | Other Lock |           0 |
| latch: auto increment sync lock wait                       | Other Lock |           0 |
| latch: auto increment GAIS lock wait                       | Other Lock |           0 |
| latch: auto increment leader lock wait                     | Other Lock |           0 |
| latch: alloc memory dump task lock wait                    | Other Lock |           0 |
| latch: alloc expand, shrink and segment lock wait          | Other Lock |           0 |
| latch: concurrent bitset lock wait                         | Other Lock |           0 |
| latch: seg array lock wait                                 | Other Lock |           0 |
| latch: arena allocator lock wait                           | Other Lock |           0 |
| latch: cached allocator lock wait                          | Other Lock |           0 |
| latch: delay free allocator lock wait                      | Other Lock |           0 |
| latch: FIFO allocator lock wait                            | Other Lock |           0 |
| latch: page manager lock wait                              | Other Lock |           0 |
| latch: simple FIFO allocator lock wait                     | Other Lock |           0 |
| latch: DList lock wait                                     | Other Lock |           0 |
| latch: global freelist lock wait                           | Other Lock |           0 |
| latch: freeze info manager lock wait                       | Other Lock |           0 |
| latch: chunk free list lock wait                           | Other Lock |           0 |
| latch: chunk using list lock wait                          | Other Lock |           0 |
| latch: work dag lock wait                                  | Other Lock |           0 |
| latch: work dag_net lock wait                              | Other Lock |           0 |
| latch: sys task stat lock wait                             | Other Lock |           0 |
| latch: info mgr lock wait                                  | Other Lock |           0 |
| latch: merger dump lock wait                               | Other Lock |           0 |
| latch: tablet merge info lock wait                         | Other Lock |           0 |
| latch: wash out lock wait                                  | Other Lock |           0 |
| latch: kv cache inst lock wait                             | Other Lock |           0 |
| latch: kv cache list lock wait                             | Other Lock |           0 |
| latch: thread store lock wait                              | Other Lock |           0 |
| latch: global kvcache config lock wait                     | Other Lock |           0 |
| latch: backup lock wait                                    | Other Lock |           0 |
| latch: restore lock wait                                   | Other Lock |           0 |
| latch: object device lock wait                             | Other Lock |           0 |
| latch: global io config lock wait                          | Other Lock |           0 |
| latch: tenant io manage lock wait                          | Other Lock |           0 |
| latch: io fault detector lock wait                         | Other Lock |           0 |
| latch: table api interface lock wait                       | Other Lock |           0 |
| latch: server locality manager lock wait                   | Other Lock |           0 |
| latch: ddl task lock wait                                  | Other Lock |           0 |
| latch: deadlock lock wait                                  | Other Lock |           0 |
| latch: background thread monitor lock wait                 | Other Lock |           0 |
| latch: rpc stat lock wait                                  | Other Lock |           0 |
| latch: dblink lock wait                                    | Other Lock |           0 |
| latch: palf sw last submit log info lock wait              | Other Lock |           0 |
| latch: palf sw committed info lock wait                    | Other Lock |           0 |
| latch: palf sw last slide log info lock wait               | Other Lock |           0 |
| latch: palf sw fetch log info lock wait                    | Other Lock |           0 |
| latch: palf sw match lsn map lock wait                     | Other Lock |           0 |
| latch: palf sw location cache cb lock wait                 | Other Lock |           0 |
| latch: palf cm config data lock wait                       | Other Lock |           0 |
| latch: palf cm parent info lock wait                       | Other Lock |           0 |
| latch: palf cm child info lock wait                        | Other Lock |           0 |
| latch: palf handle impl lock wait                          | Other Lock |           2 |
| latch: log engine lock wait                                | Other Lock |           0 |
| latch: palf env lock wait                                  | Other Lock |           0 |
| latch: role change service lock wait                       | Other Lock |           0 |
| latch: ls archive task lock wait                           | Other Lock |           0 |
| latch: archive round mgr lock wait                         | Other Lock |           0 |
| latch: archive persist mgr lock wait                       | Other Lock |           0 |
| latch: archive task queue lock wait                        | Other Lock |           0 |
| latch: external server blacklist lock wait                 | Other Lock |           0 |
| latch: direct insert table context lock wait               | Other Lock |           0 |
| latch: direct insert tablet context lock wait              | Other Lock |           0 |
| latch: direct insert table manager lock wait               | Other Lock |           0 |
| latch: complement data context lock wait                   | Other Lock |           0 |
| latch: tablet ddl kv mgr lock wait                         | Other Lock |           0 |
| latch: tablet auto increment mgr lock wait                 | Other Lock |           0 |
| latch: tablet auto increment service lock wait             | Other Lock |           0 |
| latch: all server tracer lock wait                         | Other Lock |           0 |
| latch: upgrade storage format version lock wait            | Other Lock |           0 |
| latch: replay status lock wait                             | Other Lock |           0 |
| latch: replay status task lock wait                        | Other Lock |           0 |
| latch: max apply scn lock wait                             | Other Lock |           0 |
| latch: gc handler lock wait                                | Other Lock |           0 |
| latch: memstore allocator lock wait                        | Other Lock |           0 |
| latch: thread pool lock wait                               | Other Lock |           0 |
| latch: clog checkpoint lock wait                           | Other Lock |           0 |
| latch: ls meta lock wait                                   | Other Lock |          23 |
| latch: ls change lock wait                                 | Other Lock |           0 |
| latch: tenant memory usage lock wait                       | Other Lock |           0 |
| latch: tx table lock wait                                  | Table Lock |           0 |
| latch: metmable stat lock wait                             | Other Lock |           0 |
| latch: deadlock detect lock wait                           | Other Lock |           0 |
| latch: freeze thread pool lock wait                        | Other Lock |           0 |
| latch: das wait remote response lock wait                  | Other Lock |           0 |
| latch: clog checkpoint rwlock wait                         | Other Lock |           0 |
| latch: rewrite rule item lock wait                         | Other Lock |           0 |
| latch: srs lock wait                                       | Other Lock |           0 |
| latch: ddl execute lock wait                               | Other Lock |           0 |
| latch: tenant io config lock wait                          | Other Lock |           0 |
| latch: window function participator lock wait              | Other Lock |           0 |
| latch: arbserver config lock wait                          | Other Lock |           0 |
| latch: cdcservice clientlsctx lock wait                    | Other Lock |           0 |
| latch: major freeze diagnose lock wait                     | Other Lock |           0 |
| latch: hb responses lock wait                              | Other Lock |           0 |
| latch: all servers info in table lock wait                 | Table Lock |           0 |
| latch: optimizer stat gather stat lock wait                | Other Lock |           0 |
| latch: dup tablet lock wait                                | Other Lock |           0 |
| latch: tenant io allocator lock wait                       | Other Lock |           0 |
| latch: tmp file mem block lock wait                        | Other Lock |           0 |
| latch: log external storage handler rw lock wait           | Other Lock |           0 |
| latch: log external storage handler spin lock wait         | Other Lock |           0 |
| latch: PL DEBUG RuntimeInfo lock wait                      | Other Lock |           0 |
| latch: mds table handler lock wait                         | Other Lock |           0 |
| latch: index name cache lock wait                          | Other Lock |           0 |
+------------------------------------------------------------+------------+-------------+
281 rows in set (0.009 sec)

obclient [oceanbase]>

6. 注意事项

索引设计原则：
- 复合索引列顺序：高选择性列优先（如 customer_id）
- 索引键长度：避免过长列（建议 < 30 字节）
性能监控：
- 使用 __all_virtual_sql_audit 跟踪索引使用率
- 监控索引大小：SELECT * FROM __all_virtual_index_status

7. 清理工作

7.1 – 恢复默认日志级别

obclient [oceanbase]> ALTER SYSTEM SET syslog_level = 'INFO';
Query OK, 0 rows affected (0.008 sec)

obclient [oceanbase]>

7.2 – 查看日志级别

obclient [oceanbase]> SELECT 
    ->   svr_ip,
    ->   name,
    ->   value 
    -> FROM gv$ob_parameters 
    -> WHERE name = 'syslog_level' 
    -> ORDER BY svr_ip;
+-----------+--------------+-------+
| svr_ip    | name         | value |
+-----------+--------------+-------+
| 127.0.0.1 | syslog_level | INFO  |
+-----------+--------------+-------+
1 row in set (0.004 sec)

obclient [oceanbase]>

注意事项

生产环境操作建议：
- 在低峰时段进行索引创建和查询优化
- 通过 ALTER SYSTEM 修改配置时需评估影响范围
索引设计原则：
- 优先覆盖高频查询字段
- 避免过度索引（影响写性能）

总结

OceanBase 数据库性能优化是一项系统工程，需要从资源分配、SQL 调优、参数配置等多个维度综合考虑。通过本指南，我们系统地探讨了：

资源监控基础：掌握 CPU、内存、I/O 和磁盘等关键指标的监控方法，建立性能基线，及时发现异常。
瓶颈定位技术：
- 通过 GV$OB_SERVERS 等视图全面了解集群状态
- 利用 V$OB_MEMSTORE 分析内存使用情况
- 通过合并监控确保后台任务不影响业务
SQL 优化实践：
- 使用 EXPLAIN 分析执行计划
- 设计高效的复合索引
- 避免全表扫描等性能陷阱
全链路诊断能力：
- 动态调整日志级别追踪问题
- 分析锁竞争情况
- 监控会话状态和长事务

在实际生产环境中，建议建立完善的监控体系，定期进行性能评估，并遵循"监控-分析-优化-验证"的闭环管理流程。同时需要注意，任何调优操作都应该在测试环境充分验证，并选择业务低峰期实施。

记住，没有放之四海而皆准的优化方案，最佳的调优策略总是基于对业务特点和数据特征的深入理解。希望本指南能成为您 OceanBase 性能优化道路上的实用参考，助力构建更高效、更稳定的数据库环境。

—— 仅供参考。如果有更多具体的问题或需要进一步的帮助，请随时告知。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

OceanBase 数据库高效运维：调优技术深度解析

前言

一、性能诊断集群概述

常见性能问题处理建议

1. 连接诊断入口

二、资源使用情况分析

1. 查看OBServer资源概况

1.1 – 查看服务器资源分配和使用情况

2. 磁盘空间分析

2.1 业务数据盘使用情况

2.2 日志数据盘使用情况

3. 关键资源监控指标

3.1 – 集群资源总览（CPU/内存/磁盘）

4. 查看 OBServer 运行情况

4.1 可以使用如下 SQL，查询 OBServer 运行情况。

4.2 查询 UNIT 列表

三、性能瓶颈定位

1. CPU高负载分析

1.1 – 典型解决方案

2. 内存使用分析

2.1 MemStore使用情况

2.2 内存模块详情

2.3 查看KVCACHE使用情况

3. 内存泄漏处置

3.1 – 内存使用深度诊断

3.2 – 动态调参（立即生效）

3.2.1 遇到的错误是由于参数顺序设置不正确

3.2.2 解决方案步骤：

3.2.3 验证当前参数值：

四、会话与连接分析

1. 活跃会话监控

关键问题解析

1.1 查看所有会话状态

1.2 查看活跃会话详细信息

注意事项

2. 验证 tenant 列内容

注意事项

3. 查看所有租户信息

4. 查看活跃会话的租户标识符

5. 验证关联查询结果

注意事项

五、合并与转储监控

1. 合并状态检查

2. 合并进度查询

2.1 合并进度查询返回 NULL 没有数据

2.2 视图名称或列名错误

2.3 无活跃合并任务

2.4 手动触发合并（谨慎操作）：

3. 全量查询查看当前合并进度

4. 聚合查询 OceanBase 的合并进度查询

5. 手动触发新合并（谨慎操作）后：

6. 全量查询监控合并进度：

7. 再次查询监控OceanBase数据库中各租户合并（Compaction）任务的进度

六、全链路诊断

1. 动态启用跟踪日志（无需重启）

2. 确认当前用户权限

3. 测试数据准备

3.1 – 创建测试表（若不存在）

3.2 – 生成测试数据（10万行）

3.2.1 – 问题 1：递归 CTE 迭代次数限制

5.1 检查订单数据

注意事项

5.2 – 验证总行数

5.3 --验证最新数据

5.4 – 验证日期范围

5.5 – 验证金额分布

6. 确认变量兼容性 – 预期输出（关键变量）：

7. 使用 EXPLAIN 分析执行计划

七、索引创建与优化指南

1. 创建复合索引（MySQL 模式）

2. 索引结构验证

2.1 – 查询索引元数据

3. – 性能优化验证

3.1 – 强制使用新索引的查询

3.2 – 执行计划分析

4. 索引监控

5. 锁类型细分-- 区分行锁、表锁、元数据锁

6. 注意事项

7. 清理工作

7.1 – 恢复默认日志级别

2.1 合并进度查询返回 `NULL` 没有数据