实时执行计划诊断指南

OceanBase 提供多维度的执行计划诊断能力，帮助用户实时分析 SQL 的逻辑与物理执行计划。本文介绍如何通过 DBMS_XPLAN、GV$OB_PLAN_CACHE_PLAN_EXPLAIN 和 GV$SQL_PLAN_MONITOR 查询执行计划，并结合示例说明具体操作方法。

DBMS_XPLAN 系统包：用于查询逻辑执行计划。
GV$OB_PLAN_CACHE_PLAN_EXPLAIN 视图：用于查询某条 SQL 在计划缓存中的物理执行计划。
GV$SQL_PLAN_MONITOR 视图：用于获取租户级执行计划相关信息。

逻辑执行计划诊断

当您在执行一条大 SQL 时，如果当前连接执行过久，您就需要了解查询执行的情况，例如执行计划、执行进程等。在这种情况下，由于当前 Session 被大 SQL 占用，您需要开启一条新连接，通过 SHOW PROCESSLIST 命令找到该大 SQL 所在的 Session，使用 session_id 以及 DBMS_XPLAN 系统包的 DISPLAY_ACTIVE_SESSION_PLAN 子程序展示大 SQL 的执行详情。

实时查看 Session 的执行计划

当 SQL 执行过久且当前 Session 被占用时，可通过以下步骤诊断执行路径：

步骤 1：执行慢 SQL

-- 示例：执行一个大表笛卡尔积操作  
obclient> SELECT COUNT(*) FROM TABLE(GENERATOR(100000)) A, TABLE(GENERATOR(10000)) B;
+------------+
| COUNT(*)   |
+------------+
| 1000000000 |
+------------+
1 row in set

步骤 2：获取目标 Session ID

连接另外一个会话，通过 SHOW PROCESSLIST 命令查找正在执行的 SQL 的 Session ID：

SHOW PROCESSLIST;
+------------+------+--------------------+------+---------+------+--------+---------------------------------------------------------------------------+
| ID         | USER | HOST               | DB   | COMMAND | TIME | STATE  | INFO                                                                      |
+------------+------+--------------------+------+---------+------+--------+---------------------------------------------------------------------------+
| 3221675847 | SYS  | 11.x.x.44:57841 | SYS  | Query   |    0 | ACTIVE | show processlist                                                          |
| 3221668463 | SYS  | 11.x.x.44:57530 | SYS  | Query   |    2 | ACTIVE | select count(*) from table(generator(100000)) A, table(generator(10000))B |
+------------+------+--------------------+------+---------+------+--------+---------------------------------------------------------------------------+

步骤 3：展示 Session 的实时计划

使用 DBMS_XPLAN 系统包获取算子级执行详情：

/* MySQL 模式下展示 Session 计划详情 */
SELECT DBMS_XPLAN.DISPLAY_ACTIVE_SESSION_PLAN(3221668463);
+--------------------------------------------------------------------------------------------------------------------+
| COLUMN_VALUE                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------+
| ==============================================================================================================     |
| |ID|OPERATOR                     |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|     |
| --------------------------------------------------------------------------------------------------------------     |
| |0 |SCALAR GROUP BY              |    |1       |1794        |0        |0            |0          |0           |     |
| |1 |└─NESTED-LOOP JOIN CARTESIAN |    |39601   |1076        |0        |0            |0          |0           |     |
| |2 |  ├─FUNCTION_TABLE           |A   |199     |1           |0        |0            |0          |0           |     |
| |3 |  └─MATERIAL                 |    |199     |80          |0        |0            |0          |0           |     |
| |4 |    └─FUNCTION_TABLE         |B   |199     |1           |0        |0            |0          |0           |     |
| ==============================================================================================================     |
| Outputs & filters:                                                                                                 |
| -------------------------------------                                                                              |
|   0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                                                            |
|       group(nil), agg_func([T_FUN_COUNT(*)])                                                                       |
|   1 - output(nil), filter(nil), rowset=256                                                                         |
|       conds(nil), nl_params_(nil), use_batch=false                                                                 |
|   2 - output(nil), filter(nil)                                                                                     |
|       value(GENERATOR(cast(:0, BIGINT(-1, 0))))                                                                    |
|   3 - output(nil), filter(nil), rowset=256                                                                         |
|   4 - output(nil), filter(nil)                                                                                     |
|       value(GENERATOR(cast(:1, BIGINT(-1, 0))))                                                                    |
+--------------------------------------------------------------------------------------------------------------------+

/* Oracle 模式下展示 Session 计划详情 */
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_ACTIVE_SESSION_PLAN(3221668463));
+--------------------------------------------------------------------------------------------------------------------+
| COLUMN_VALUE                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------+
| ==============================================================================================================     |
| |ID|OPERATOR                     |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|     |
| --------------------------------------------------------------------------------------------------------------     |
| |0 |SCALAR GROUP BY              |    |1       |1794        |0        |0            |0          |0           |     |
| |1 |└─NESTED-LOOP JOIN CARTESIAN |    |39601   |1076        |0        |0            |0          |0           |     |
| |2 |  ├─FUNCTION_TABLE           |A   |199     |1           |0        |0            |0          |0           |     |
| |3 |  └─MATERIAL                 |    |199     |80          |0        |0            |0          |0           |     |
| |4 |    └─FUNCTION_TABLE         |B   |199     |1           |0        |0            |0          |0           |     |
| ==============================================================================================================     |
| Outputs & filters:                                                                                                 |
| -------------------------------------                                                                              |
|   0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                                                            |
|       group(nil), agg_func([T_FUN_COUNT(*)])                                                                       |
|   1 - output(nil), filter(nil), rowset=256                                                                         |
|       conds(nil), nl_params_(nil), use_batch=false                                                                 |
|   2 - output(nil), filter(nil)                                                                                     |
|       value(GENERATOR(cast(:0, BIGINT(-1, 0))))                                                                    |
|   3 - output(nil), filter(nil), rowset=256                                                                         |
|   4 - output(nil), filter(nil)                                                                                     |
|       value(GENERATOR(cast(:1, BIGINT(-1, 0))))                                                                    |
+--------------------------------------------------------------------------------------------------------------------+

其他逻辑计划查询方式

OceanBase 还提供了 (G)V$OB_SQL_PLAN 视图用于查询通过 EXPLAIN PLAN 工具生成的静态执行计划。例如：

obclient> SELECT * FROM oceanbase.V$OB_SQL_PLAN WHERE TENANT_ID = effective_tenant_id();


obclient> SELECT * FROM oceanbase.V$OB_SQL_PLAN WHERE SVR_IP=host_ip() AND SVR_PORT=rpc_port();

但推荐使用 DBMS_XPLAN 系统包：

DBMS_XPLAN.DISPLAY_ACTIVE_SESSION_PLAN 可直接获取正在执行 SQL 的动态计划，包含实时性能数据（如 REAL.ROWS、CPU_TIME）。 GV$OB_SQL_PLAN 仅存储 EXPLAIN 生成的静态计划，无法反映实际执行中的动态变化。

物理执行计划诊断

使用 EXPLAIN 命令可以展示出当前优化器所生成的执行计划，但由于统计信息变化、用户 Session 变量设置变化等，会造成该 SQL 在计划缓存中实际对应的计划可能与 EXPLAIN 的结果并不相同。为了确定该 SQL 在系统中实际使用的执行计划，需要进一步分析计划缓存中的物理执行计划。

通过计划缓存查询物理计划

OceanBase 数据库每个服务器的计划缓存都是独立的。物理执行计划存储在 V$OB_PLAN_CACHE_PLAN_EXPLAIN 视图中，需先通过 V$OB_PLAN_CACHE_PLAN_STAT 查询本服务器上的计划缓存并提供 tenant_id 和需要查询的 SQL 字符串（可以使用模糊匹配），查询该条 SQL 在计划缓存中对应的 plan_id，并进一步展示该 SQL 在计划缓存中的物理执行计划。

V$OB_PLAN_CACHE_PLAN_EXPLAIN 视图的查询定义如下：

SELECT * FROM oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN WHERE TENANT_ID = tenant_id() AND PLAN_ID = plan_id();

步骤 1：查询计划缓存获取 plan_id

obclient> SELECT * FROM oceanbase.V$OB_PLAN_CACHE_PLAN_STAT WHERE tenant_id= 1001 AND STATEMENT LIKE 'INSERT INTO T1 VALUES%'\G

输出结果：

***************************1. row ***************************
            TENANT_ID: 1001
               SVR_IP: xxx.xxx.xxx.xxx
             SVR_PORT: 2882
              PLAN_ID: 9228
               SQL_ID: 5AB7C2585394BAD0EE04A39099728804
                 TYPE: 1
    IS_BIND_SENSITIVE: 0
        IS_BIND_AWARE: 0
                DB_ID: 201001
            STATEMENT: insert into t1 values(?)
            QUERY_SQL: insert into t1 values(1)
       SPECIAL_PARAMS:
          PARAM_INFOS: {0,0,0,0,5}
             SYS_VARS: 45,45,12582912,2,4,1,0,0,3,1,0,1,10485760,1,0,BINARY,BINARY,AL32UTF8,AL16UTF16,BYTE,FALSE,1,100,64,200,0,13,NULL,1,1,1,1,1,0,0,0,1000,BLOOM_FILTER, RANGE,IN
              CONFIGS: 3,1,1,0,1,1,1,0,30,17180000256,
            PLAN_HASH: 3290318591324336132
      FIRST_LOAD_TIME: 2023-07-04 16:36:51.558406
       SCHEMA_VERSION: 1688459804400976
     LAST_ACTIVE_TIME: 2023-07-04 16:36:51.562434
         AVG_EXE_USEC: 179697
     SLOWEST_EXE_TIME: 2023-07-04 16:36:51.562434
     SLOWEST_EXE_USEC: 179697
           SLOW_COUNT: 0
            HIT_COUNT: 0
            PLAN_SIZE: 102616
           EXECUTIONS: 1
           DISK_READS: 9
        DIRECT_WRITES: 0
          BUFFER_GETS: 9
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
    USER_IO_WAIT_TIME: 0
       ROWS_PROCESSED: 1
         ELAPSED_TIME: 179697
             CPU_TIME: 177641
         LARGE_QUERYS: 0
 DELAYED_LARGE_QUERYS: 0
    DELAYED_PX_QUERYS: 0
      OUTLINE_VERSION: 0
           OUTLINE_ID: -1
         OUTLINE_DATA: /*+BEGIN_OUTLINE_DATA USE_DISTRIBUTED_DML(@"INS$1") OPTIMIZER_FEATURES_ENABLE('4.0.0.0') END_OUTLINE_DATA*/
         ACS_SEL_INFO:
           TABLE_SCAN: 0
            EVOLUTION: 0
       EVO_EXECUTIONS: 0
         EVO_CPU_TIME: 0
        TIMEOUT_COUNT: 0
           PS_STMT_ID: -1
               SESSID: 0
          TEMP_TABLES:
           IS_USE_JIT: 0
          OBJECT_TYPE: SQL_PLAN
           HINTS_INFO:
     HINTS_ALL_WORKED: 1
         PL_SCHEMA_ID: 0
IS_BATCHED_MULTI_STMT: 0
            RULE_NAME:
1 row in set

步骤 2：展示物理执行计划

获得 plan_id 后，用户可以使用 tenant_id 和 plan_id 访问 V$OB_PLAN_CACHE_PLAN_EXPLAIN 视图查询执行计划相关信息。

注意

这里展示的计划为物理执行计划，在算子命名上会与 EXPLAIN 所展示的逻辑执行计划有所不同。

obclient> SELECT * FROM oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN  
          WHERE tenant_id = 1001 AND plan_id = 9228;

输出示例：

+-----------+----------------+----------+---------+------------+--------------+------------------+------+------+------+----------+
| TENANT_ID | SVR_IP         | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR         | NAME | ROWS | COST | PROPERTY |
+-----------+----------------+----------+---------+------------+--------------+------------------+------+------+------+----------+
|      1001 | xxx.xxx.xxx.xxx|     2882 |    9228 |          0 |            0 | PHY_INSERT       | NULL |    1 |   12 | NULL     |
|      1001 | xxx.xxx.xxx.xxx|     2882 |    9228 |          1 |            1 |  PHY_EXPR_VALUES | NULL |    1 |    0 | NULL     |
+-----------+----------------+----------+---------+------------+--------------+------------------+------+------+------+----------+
2 rows in set

实时算子级性能监控（V4.0 及以上）

OceanBase 数据库 V4.0 版本开始支持实时 SQL Plan Monitor 功能，用于追踪每个执行算子的详细性能指标，比如首行返回时间、输出行数、HASH 冲突率和磁盘落盘次数。用户可以通过查询 GV$SQL_PLAN_MONITOR 租户级视图获取执行计划相关信息，包括算子吐行数、算子开始和结束时间点以及各个执行线程执行算子的执行状态等。

启用与配置

开启 SQL Plan Monitor 功能：

ALTER SYSTEM SET enable_perf_event = true;  -- 默认开启

开启 SQL Plan Monitor 后，以下 SQL 会被记录在 SQL Plan Monitor 中：

使用 /*+ MONITOR */ Hint 显式指定的查询。
执行了并行数据修改语句（PDML）的查询。
查询中首个耗时超过 3 秒的执行算子。

查询监控数据

GV$SQL_PLAN_MONITOR 视图的 OTHERSTAT_?_ID 和 OTHERSTAT_?_VALUE 字段用于记录算子特有的性能数据，每个字段实际记录数据的名字，通过一个 ID 表示，ID 对应的具体含义，则需要通过 V$SQL_MONITOR_STATNAME 视图查询。

obclient [SYS]> DESC V$SQL_MONITOR_STATNAME;
+-------------+---------------+------+------+---------+-------+
| FIELD       | TYPE          | NULL | KEY  | DEFAULT | EXTRA |
+-------------+---------------+------+------+---------+-------+
| CON_ID      | NUMBER        | NO   | NULL | NULL    | NULL  |
| ID          | NUMBER        | NO   | NULL | NULL    | NULL  |
| GROUP_ID    | NUMBER        | NO   | NULL | NULL    | NULL  |
| NAME        | VARCHAR2(40)  | NO   | NULL | NULL    | NULL  |
| DESCRIPTION | VARCHAR2(200) | NO   | NULL | NULL    | NULL  |
| TYPE        | NUMBER        | NO   | NULL | NULL    | NULL  |
| FLAGS       | NUMBER        | NO   | NULL | NULL    | NULL  |
+-------------+---------------+------+------+---------+-------+
7 rows in set

下文具体示例展示如何使用 GV$SQL_PLAN_MONITOR 和 V$SQL_MONITOR_STATNAME 视图查询执行计划信息。

步骤 1：获取 trace_id

obclient> SELECT trace_id FROM GV$OB_SQL_AUDIT  
          WHERE query_sql LIKE '%TPCH_%' ORDER BY REQUEST_TIME DESC LIMIT 1;