--- title: "SQL 诊断 | OceanBase 文档中心" description: "SQL 诊断 docslug#/oceanbase-database/oceanbase-database/V3.2.3/sql-diagnostics-2 压测环境中的 SQL 执行效率，一定程度上决定了系统的性能，因此 SQL 诊断是性能调优过程中最重要的一个环节，本文主要介绍如何定位并优化慢 SQL。 视图统计…" image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.0.0 社区版 # SQL 诊断 更新时间：2026-06-22 10:30:26 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.0.0/zh-CN/500.administrator-guide/900.performance-tuning-guide/400.business-model-tuning/200.sql-diagnostics-2.md) 压测环境中的 SQL 执行效率，一定程度上决定了系统的性能，因此 SQL 诊断是性能调优过程中最重要的一个环节，本文主要介绍如何定位并优化慢 SQL。 ## 视图统计 根据 Plan Cache 的统计，找出高频的 SQL 类别： ```sql obclient> select plan_id, sql_id, hit_count, avg_exe_usec, substr(statement, 1, 100) from gv$ob_plan_cache_plan_stat where tenant_id=1002 order by hit_count desc limit 10; +---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+ | plan_id | sql_id | hit_count | avg_exe_usec | substr(statement, 1, 100) | +---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+ | 7020372274597948570 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 14767 | 562 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, | | 7020372274597948744 | 9CA2F8D24467EB1A28CA50EE09743A86 | 14707 | 468 | SELECT * FROM __all_acquired_snapshot WHERE tenant_id = ? | | 7020372274597948743 | C1E19F19B0677FD5875F8C7C4FF30436 | 14707 | 492 | SELECT * FROM __all_freeze_info | | 7020372274597948834 | 624F9288016A7704D6201261C0F494FF | 11823 | 110 | select * from __all_tenant_scheduler_job where tenant_id = ? and job = ? | | 7020372274597948569 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7424 | 158 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, | | 7020372274597948812 | B6E4D946D9527AB02AFBEC16F74F2E25 | 7405 | 589 | SELECT * FROM __all_freeze_info ORDER BY frozen_scn DESC LIMIT ? | | 7020372274597948741 | C1E19F19B0677FD5875F8C7C4FF30436 | 7389 | 80 | SELECT * FROM __all_freeze_info | | 7020372274597948742 | 9CA2F8D24467EB1A28CA50EE09743A86 | 7389 | 68 | SELECT * FROM __all_acquired_snapshot WHERE tenant_id = ? | | 49 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7382 | 1012 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, | | 49 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7381 | 1009 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, | +---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+ 10 rows in set ``` 根据具体的 `SQL_ID`，采样一段时间内的执行数据，具体 SQL 如下： ```sql obclient> select svr_ip, plan_type, elapsed_time, AFFECTED_ROWS, RETURN_ROWS, tx_id, usec_to_time(REQUEST_TIME), substr(query_sql, 1, 30) from gv$ob_sql_audit where sql_id='F96CE9DFB959E383828A9D91575EE97F' and request_time > time_to_usec('2021-08-25 22:00:00') and request_time < time_to_usec('2021-08-25 22:50:00') order by elapsed_time desc limit 10; +---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+ | svr_ip | plan_type | elapsed_time | AFFECTED_ROWS | RETURN_ROWS | transaction_hash | usec_to_time(REQUEST_TIME) | substr(query_sql, 1, 30) | +---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+ | xxx.xxx.xxx.1 | 1 | 465114 | 0 | 0 | 10023348016566894972 | 2021-08-25 22:44:08.533070 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.1 | 1 | 375107 | 0 | 0 | 13001988804803062059 | 2021-08-25 22:44:08.573525 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.2 | 2 | 226940 | 0 | 0 | 0 | 2021-08-25 22:44:08.722480 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.2 | 2 | 224519 | 0 | 0 | 0 | 2021-08-25 22:44:08.730139 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.1 | 1 | 220272 | 0 | 0 | 6454906702768493748 | 2021-08-25 22:44:08.745529 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.3 | 2 | 78577 | 0 | 0 | 0 | 2021-08-25 22:44:08.884916 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.3 | 2 | 49034 | 0 | 0 | 0 | 2021-08-25 22:44:08.905322 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.2 | 2 | 48885 | 0 | 0 | 0 | 2021-08-25 22:44:08.905610 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.1 | 1 | 45239 | 0 | 0 | 11958340144270554107 | 2021-08-25 22:44:08.906159 | SELECT * FROM __all_root_table | | xxx.xxx.xxx.3 | 2 | 33454 | 0 | 0 | 0 | 2021-08-25 22:44:08.920650 | SELECT * FROM __all_root_table | +---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+ 10 rows in set ``` 具体的 SQL 写法，可以结合实际需求来调整。 ## OCP 监控 OCP 目前已经有完善的慢 SQL 分析，包括：执行计划、执行频率、耗时等。通过 OCP 能大大提高效率，快速定位慢 SQL 的相关信息。 ## SQL 调优 确定慢 SQL 之后，需要从以下几个方面进行排查： - 租户资源是否充足？ - 索引或计划是否相对最优？ - 确定 affected rows 、return rows 大小，确定写入或查询涉及的分区数、行数是否较多？ - 是否存在跨城访问、跨机访问？ - 对于较为复杂的 SQL，可能有中间结果 dump 到磁盘，需要确认是否符合预期？ - 系统层面排查：确认转储、磁盘 IO 使用率等。 有关 SQL 调优的详细介绍，请参见《 SQL 调优指南》。 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线