OceanBase管理数据库SQL诊断

压测环境中的 SQL 执行效率，一定程度上决定了系统的性能，因此 SQL 诊断是性能调优过程中最重要的一个环节，本文主要介绍如何定位并优化慢 SQL。

视图统计

根据 Plan Cache 的统计，找出高频的 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 如下：

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)       |
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
| 192.x.x.x     |         1 |       465114 |             0 |           0 | 10023348016566894972 | 2021-08-25 22:44:08.533070 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         1 |       375107 |             0 |           0 | 13001988804803062059 | 2021-08-25 22:44:08.573525 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         2 |       226940 |             0 |           0 |                    0 | 2021-08-25 22:44:08.722480 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         2 |       224519 |             0 |           0 |                    0 | 2021-08-25 22:44:08.730139 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         1 |       220272 |             0 |           0 |  6454906702768493748 | 2021-08-25 22:44:08.745529 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         2 |        78577 |             0 |           0 |                    0 | 2021-08-25 22:44:08.884916 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         2 |        49034 |             0 |           0 |                    0 | 2021-08-25 22:44:08.905322 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         2 |        48885 |             0 |           0 |                    0 | 2021-08-25 22:44:08.905610 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         1 |        45239 |             0 |           0 | 11958340144270554107 | 2021-08-25 22:44:08.906159 | SELECT * FROM __all_root_table |
| 192.x.x.x     |         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 使用率等。