分布式数据库学习Note90：OceanBase社区版中，如何使用实时执行计划展示？

分布式数据库爱好者 2023-02-27

206

实时执行计划展示可以展示 SQL 的物理执行计划。

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

GV$OB_PLAN_CACHE_PLAN_EXPLAIN 视图

用户可以通过查询 GV$OB_PLAN_CACHE_PLAN_EXPLAIN 视图来展示某条 SQL 在计划缓存中的执行计划。

如下例所示：

VIEW_DEFINITION='SELECT * 
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
WHERE IP =host_ip() AND PORT = rpc_port()'

字段解释如下表所示。

字段名称	类型	描述
TENANT_ID	bigint(20)	租户 ID
SVR_IP	varchar(46)	IP 地址
SVR_PORT	bigint(20)	端口号
PLAN_ID	bigint(20)	执行计划的 ID
PLAN_DEPTH	bigint(20)	算子在展示时的深度
PLAN_LINE_ID	bigint(20)	算子的编号
OPERATOR	varchar(128)	算子的名称
NAME	varchar(256)	表的名称
ROWS	bigint(20)	预估的结果行数
COST	bigint(20)	预估的代价
PROPERTY	varchar(4096)	对应算子的信息

第一步查询 SQL 在计划缓存中的 plan_id

OceanBase 数据库每个服务器的计划缓存都是独立的。用户可以直接访问 V$OB_PLAN_CACHE_PLAN_STAT 视图查询本服务器上的计划缓存并提供 tenant_id 和需要查询的 SQL 字符串（可以使用模糊匹配），查询该条 SQL 在计划缓存中对应的 plan_id。

obclient> SELECT * FROM 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:100.xx.xxx.xx
        svr_port:15212
         plan_id: 7
          sql_id:0
            type: 1
       statement: insert into t1 values(1)
       plan_hash:1
last_active_time:2016-05-28 19:08:57.416670
    avg_exe_usec:0
slowest_exe_time:1970-01-01 08:00:00.000000
slowest_exe_usec:0
      slow_count:0
       hit_count:0
        mem_used:8192
1 rowin set

第二步使用 plan_id 展示对应执行计划

获得 plan_id 后，用户可以使用 tenant_id 和 plan_id 访问 GV$OB_PLAN_CACHE_PLAN_EXPLAIN 来展示该执行计划。

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

obclient> SELECT * FROM GV$OB_PLAN_CACHE_PLAN_EXPLAIN WHERE tenant_id = 1001 AND plan_id = 7;
                            
+-----------+---------------+----------+---------+--------------------+------+------+------+
| TENANT_ID | SVR_IP        | SVR_PORT | PLAN_ID | OPERATOR           | NAME | ROWS | COST |
+-----------+---------------+----------+---------+--------------------+------+------+------+
|      1001 | 100.xx.xxx.xx | 15212    |       7 |  PHY_ROOT_TRANSMIT | NULL |    0 |    0 |
|      1001 | 100.xx.xxx.xx | 15212    |       7 |   PHY_INSERT       | NULL |    0 |    0 |
|      1001 | 100.xx.xxx.xx | 15212    |       7 |    PHY_EXPR_VALUES | NULL |    0 |    0 |
+-----------+---------------+----------+---------+--------------------+------+------+------+
3 rows in set

注意
如果访问 GV$OB_PLAN_CACHE_PLAN_EXPLAIN，必须给定 SVR_IP、SVR_PORT、tenant_id 和 plan_id 这四列的值。
如果访问 V$OB_PLAN_CACHE_PLAN_EXPLAIN，必须给定 tenant_id 和 plan_id 的值，否则系统将返回空集。

GV$SQL_PLAN_MONITOR 视图

OceanBase 数据库 V4.0 版本支持实时 SQL Plan Monitor 功能，即用户可以通过查询 GV$SQL_PLAN_MONITOR 租户级视图获取执行计划相关信息，包括逻辑执行计划、物理执行计划、算子吐行数、算子开始/结束时间点以及各个执行线程执行算子的执行状态等。

GV$SQL_PLAN_MONITOR 字段说明

字段名称	类型	是否可以为 NULL	描述
CON_ID	bigint(20)	NO	租户 ID
REQUEST_ID	bigint(20)	NO
KEY	null	NO	外键，用于和 SQL_MONITOR 相关虚拟表做连接查询
STATUS	null	NO	算子执行状态： EXECUTING：执行中 DONE(ERROR)：执行出错 DONE(FIRST N ROWS)：执行成功 DONE(ALL ROWS)：执行成功 DONE：执行中断
SVR_IP	varchar(46)	NO	算子所在机器的 IP
SVR_PORT	bigint(20)	NO	算子所在机器的端口号
TRACE_ID	varchar(64)	NO	算子的 Trace ID
DB_TIME	bigint(20)	NO	实际消耗在本算子上的 CPU 时间
USER_IO_WAIT_TIME	bigint(20)	NO	所有 user_io 类事件的总时间
OTHER_WAIT_TIME	null	NO	其它事件的等待时间
FIRST_REFRESH_TIME	timestamp(6)	NO	算子开始监控时间
LAST_REFRESH_TIME	timestamp(6)	NO	算子结束监控时间
FIRST_CHANGE_TIME	timestamp(6)	NO	算子吐出首行数据时间
LAST_CHANGE_TIME	timestamp(6)	NO	算子吐出最后一行数据时间
REFRESH_COUNT	null	NO	统计数据刷新次数
SID	null	NO	Session 的 ID
PROCESS_NAME	bigint(20)	NO	执行线程 ID
SQL_ID	null	NO	SQL ID
SQL_EXEC_START	null	NO	SQL 开始执行时间
SQL_EXEC_ID	null	NO	SQL 执行 ID
SQL_PLAN_HASH_VALUE	null	NO	SQL 计划的 HASH 值
SQL_CHILD_ADDRESS	null	NO	默认为 NULL
PLAN_PARENT_ID	null	NO	父算子 ID
PLAN_LINE_ID	bigint(20)	NO	默认为 NULL
PLAN_OPERATION	varchar(128)	NO	算子名称
PLAN_OPTIONS	null	NO	默认为 NULL
PLAN_OBJECT_OWNER	null	NO	默认为 NULL
PLAN_OBJECT_NAME	null	NO	默认为 NULL
PLAN_OBJECT_TYPE	null	NO	默认为 NULL
PLAN_DEPTH	bigint(20)	NO	算子在计划树中的深度
PLAN_POSITION	null	NO	算子是父节点的第几个孩子
PLAN_COST	null	NO	优化器计算出的算子代价
PLAN_CARDINALITY	null	NO	优化器计算出的算子输出数据行数
PLAN_BYTES	null	NO	优化器估算出的算子输出数据字节数
PLAN_TIME	null	NO	优化器计算出的算子执行时间
PLAN_PARTITION_START	null	NO	默认为 NULL
PLAN_PARTITION_STOP	null	NO	默认为 NULL
PLAN_CPU_COST	null	NO	优化器估算出的算子 CPU 代价
PLAN_IO_COST	null	NO	优化器估算出的算子 IO 代价
PLAN_TEMP_SPACE	null	NO	优化器估算出的算子空间占用大小
STARTS	bigint(20)	NO	算子被 rescan 的次数
OUTPUT_ROWS	bigint(20)	NO	算子输出的总行数（所有本算子的执行实例行数累加值）
IO_INTERCONNECT_BYTES	null	NO	算子与储存层之间交换的数据字节数
PHYSICAL_READ_REQUESTS	null	NO	算子发出的 I/O 读请求次数
PHYSICAL_READ_BYTES	null	NO	算子发出的 I/O 读请求字节数
PHYSICAL_WRITE_REQUESTS	null	NO	算子发出的 I/O 写请求次数
PHYSICAL_WRITE_BYTES	null	NO	算子发出的 I/O 写请求字节数
WORKAREA_MEM	null	NO	算子占用的 work area 内存量
WORKAREA_MAX_MEM	null	NO	算子可占用的 work area 内存上限
WORKAREA_TEMPSEG	null	NO	算子占用的磁盘 dump 空间
WORKAREA_MAX_TEMPSEG	null	NO	算子可占用的最大磁盘 dump 空间
OTHERSTAT_GROUP_ID	null	NO	默认为 NULL
OTHERSTAT_1_ID	bigint(20)	NO	预留
OTHERSTAT_1_TYPE	null	NO	预留
OTHERSTAT_1_VALUE	bigint(20)	NO	预留
OTHERSTAT_2_ID	bigint(20)	NO	预留
OTHERSTAT_2_TYPE	null	NO	预留
OTHERSTAT_2_VALUE	bigint(20)	NO	预留
OTHERSTAT_3_ID	bigint(20)	NO	预留
OTHERSTAT_3_TYPE	null	NO	预留
OTHERSTAT_3_VALUE	bigint(20)	NO	预留
OTHERSTAT_4_ID	bigint(20)	NO	预留
OTHERSTAT_4_TYPE	null	NO	预留
OTHERSTAT_4_VALUE	bigint(20)	NO	预留
OTHERSTAT_5_ID	bigint(20)	NO	预留
OTHERSTAT_5_TYPE	null	NO	预留
OTHERSTAT_5_VALUE	bigint(20)	NO	预留
OTHERSTAT_6_ID	bigint(20)	NO	预留
OTHERSTAT_6_TYPE	null	NO	预留
OTHERSTAT_6_VALUE	bigint(20)	NO	预留
OTHERSTAT_7_ID	bigint(20)	NO	预留
OTHERSTAT_7_TYPE	null	NO	预留
OTHERSTAT_7_VALUE	bigint(20)	NO	预留
OTHERSTAT_8_ID	bigint(20)	NO	预留
OTHERSTAT_8_TYPE	null	NO	预留
OTHERSTAT_8_VALUE	bigint(20)	NO	预留
OTHERSTAT_9_ID	bigint(20)	NO	预留
OTHERSTAT_9_TYPE	null	NO	预留
OTHERSTAT_9_VALUE	bigint(20)	NO	预留
OTHERSTAT_10_ID	bigint(20)	NO	预留
OTHERSTAT_10_TYPE	null	NO	预留
OTHERSTAT_10_VALUE	bigint(20)	NO	预留
OTHER_XML	null	NO	其它无法写入预留项中，但需要提供给外部使用的结构化数据。由外部工具负责解析
PLAN_OPERATION_INACTIVE	null	NO	默认为 NULL
OUTPUT_BATCHES	bigint(20)	NO	向量化模式下，算子调用 get_next_batch 接口的次数
SKIPPED_ROWS_COUNT	bigint(20)	NO	向量化模式下，算子计算过程中，不需要计算的总行数（被过滤掉的总行数）

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

obclient> DESC V$SQL_MONITOR_STATNAME;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| CON_ID      | null         | NO   |     |         |       |
| ID          | bigint(20)   | NO   |     | NULL    |       |
| GROUP_ID    | bigint(20)   | NO   |     | NULL    |       |
| NAME        | varchar(40)  | NO   |     | NULL    |       |
| DESCRIPTION | varchar(200) | NO   |     | NULL    |       |
| TYPE        | bigint(20)   | NO   |     | NULL    |       |
| FLAGS       | bigint(1)    | NO   |     |         |       |
+-------------+--------------+------+-----+---------+-------+
7 rows in set

示例

如下为通过 GV$SQL_PLAN_MONITORSQL_PLAN_MONITOR 视图和 V$SQL_MONITOR_STATNAME 视图查询执行计划信息的示例。

--  获取 trace_id
obclient> SELECT trace_id FROM oceanbase.gv$ob_sql_audit WHERE query_sql like '%TPCH_%' ORDER BY REQUEST_TIME DESC LIMIT 1;
+-----------------------------------+
| trace_id                          |
+-----------------------------------+
| Y4C360A65A34F-0005A9BD39CF5C74    |
+-----------------------------------+
1 row in set 
-- 获取执行计划概要
obclient> SELECT
        PROCESS_NAME,
        PLAN_LINE_ID,
        PLAN_OPERATION,
        COUNT(*) PARALLEL,
        AVG(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) AVG_REFRESH_TIME,
        MAX(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) MAX_REFRESH_TIME,
        MIN(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) MIN_REFRESH_TIME,
        AVG(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) AVG_CHANGE_TIME,
        MAX(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) MAX_CHANGE_TIME,
        MIN(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) MIN_CHANGE_TIME,
        SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
        SUM(STARTS) TOTAL_RESCAN_TIMES
      FROM
        oceanbase.gv$sql_plan_monitor
      WHERE
        trace_id = 'Y4C360A65A34F-0005A9BD39CF5C74'
      GROUP BY
        PLAN_LINE_ID
      ORDER BY
        PLAN_LINE_ID ASC;
+--------------+--------------+------------------------+----------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+--------------------+
| PROCESS_NAME | PLAN_LINE_ID | PLAN_OPERATION         | PARALLEL | AVG_REFRESH_TIME | MAX_REFRESH_TIME | MIN_REFRESH_TIME | AVG_CHANGE_TIME | MAX_CHANGE_TIME | MIN_CHANGE_TIME | TOTAL_OUTPUT_ROWS | TOTAL_RESCAN_TIMES |
+--------------+--------------+------------------------+----------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+--------------------+
|        16755 |            0 | PHY_PX_FIFO_COORD      |        1 |     0.0137190000 |         0.013719 |         0.013719 |    0.0063460000 |        0.006346 |        0.006346 |               300 |                  0 |
|        16883 |            1 | PHY_PX_REDUCE_TRANSMIT |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0010606667 |        0.003182 |        0.000000 |               300 |                  0 |
|        16883 |            2 | PHY_HASH_JOIN          |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0010606667 |        0.003182 |        0.000000 |               300 |                  0 |
|        16883 |            3 | PHY_PX_FIFO_RECEIVE    |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0000000000 |        0.000000 |        0.000000 |                75 |                  0 |
|        16889 |            4 | PHY_PX_DIST_TRANSMIT   |        3 |     0.0024853333 |         0.003176 |         0.002140 |    0.0003526667 |        0.001058 |        0.000000 |                25 |                  0 |
|        16889 |            5 | PHY_GRANULE_ITERATOR   |        3 |     0.0024853333 |         0.003176 |         0.002140 |    0.0003526667 |        0.001058 |        0.000000 |                25 |                  0 |
|        16889 |            6 | PHY_TABLE_SCAN         |        3 |     0.0024853333 |         0.003176 |         0.002140 |    0.0003526667 |        0.001058 |        0.000000 |                25 |                  1 |
|        16883 |            7 | PHY_GRANULE_ITERATOR   |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0013980000 |        0.004194 |        0.000000 |               300 |                  0 |
|        16883 |            8 | PHY_TABLE_SCAN         |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0013980000 |        0.004194 |        0.000000 |               300 |                  1 |
+--------------+--------------+------------------------+----------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+--------------------+
9 rows in set 
-- 获取执行计划详情
obclient> SELECT
        SVR_IP,
        SVR_PORT,
        PROCESS_NAME,
        PLAN_LINE_ID,
        PLAN_OPERATION,
        FIRST_REFRESH_TIME,
        LAST_REFRESH_TIME,
        LAST_REFRESH_TIME - FIRST_REFRESH_TIME REFRESH_TIME,
        FIRST_CHANGE_TIME,
        LAST_CHANGE_TIME,
        LAST_CHANGE_TIME - FIRST_CHANGE_TIME CHANGE_TIME,
        OUTPUT_ROWS,
        STARTS RESCAN_TIMES
      FROM
        oceanbase.gv$sql_plan_monitor
      WHERE
        trace_id = 'Y4C360A65A34F-0005A9BD39CF5C74'
      ORDER BY
        PLAN_LINE_ID ASC, PROCESS_NAME ASC, FIRST_REFRESH_TIME ASC;
+---------------+----------+--------------+--------------+------------------------+----------------------------+----------------------------+--------------+----------------------------+----------------------------+-------------+-------------+--------------+
| SVR_IP        | SVR_PORT | PROCESS_NAME | PLAN_LINE_ID | PLAN_OPERATION         | FIRST_REFRESH_TIME         | LAST_REFRESH_TIME          | REFRESH_TIME | FIRST_CHANGE_TIME          | LAST_CHANGE_TIME           | CHANGE_TIME | OUTPUT_ROWS | RESCAN_TIMES |
+---------------+----------+--------------+--------------+------------------------+----------------------------+----------------------------+--------------+----------------------------+----------------------------+-------------+-------------+--------------+
| 10.10.1.1 |    19510 |        16755 |            0 | PHY_PX_FIFO_COORD      | 2020-07-06 11:18:34.207460 | 2020-07-06 11:18:34.221179 |     0.013719 | 2020-07-06 11:18:34.214833 | 2020-07-06 11:18:34.221179 |    0.006346 |         300 |            0 |
| 10.10.1.2 |    19510 |        16882 |            1 | PHY_PX_REDUCE_TRANSMIT | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.216951 |     0.005230 | 2020-07-06 11:18:34.213769 | 2020-07-06 11:18:34.216951 |    0.003182 |         300 |            0 |
| 10.10.1.3 |    19510 |        16883 |            1 | PHY_PX_REDUCE_TRANSMIT | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.212757 |     0.001036 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.4 |    19510 |        16891 |            1 | PHY_PX_REDUCE_TRANSMIT | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.214833 |     0.003112 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.5 |    19510 |        16882 |            2 | PHY_HASH_JOIN          | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.216951 |     0.005230 | 2020-07-06 11:18:34.213769 | 2020-07-06 11:18:34.216951 |    0.003182 |         300 |            0 |
| 10.10.1.6 |    19510 |        16883 |            2 | PHY_HASH_JOIN          | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.212757 |     0.001036 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.7 |    19510 |        16891 |            2 | PHY_HASH_JOIN          | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.214833 |     0.003112 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.8 |    19510 |        16882 |            3 | PHY_PX_FIFO_RECEIVE    | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.216951 |     0.005230 | 2020-07-06 11:18:34.212757 | 2020-07-06 11:18:34.212757 |    0.000000 |          25 |            0 |
| 10.10.1.9 |    19510 |        16883 |            3 | PHY_PX_FIFO_RECEIVE    | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.212757 |     0.001036 | 2020-07-06 11:18:34.212757 | 2020-07-06 11:18:34.212757 |    0.000000 |          25 |            0 |
| 10.10.1.10 |    19510 |        16891 |            3 | PHY_PX_FIFO_RECEIVE    | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.214833 |     0.003112 | 2020-07-06 11:18:34.213769 | 2020-07-06 11:18:34.213769 |    0.000000 |          25 |            0 |
| 10.10.1.11 |    19510 |        16888 |            4 | PHY_PX_DIST_TRANSMIT   | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.212757 |     0.003176 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.12 |    19510 |        16889 |            4 | PHY_PX_DIST_TRANSMIT   | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.211721 |     0.002140 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.13 |    19510 |        16890 |            4 | PHY_PX_DIST_TRANSMIT   | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.211721 |     0.002140 | 2020-07-06 11:18:34.210663 | 2020-07-06 11:18:34.211721 |    0.001058 |          25 |            0 |
| 10.10.1.14 |    19510 |        16888 |            5 | PHY_GRANULE_ITERATOR   | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.212757 |     0.003176 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.15 |    19510 |        16889 |            5 | PHY_GRANULE_ITERATOR   | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.211721 |     0.002140 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.16 |    19510 |        16890 |            5 | PHY_GRANULE_ITERATOR   | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.211721 |     0.002140 | 2020-07-06 11:18:34.210663 | 2020-07-06 11:18:34.211721 |    0.001058 |          25 |            0 |
| 10.10.1.17 |    19510 |        16888 |            6 | PHY_TABLE_SCAN         | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.212757 |     0.003176 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.18 |    19510 |        16889 |            6 | PHY_TABLE_SCAN         | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.211721 |     0.002140 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.19 |    19510 |        16890 |            6 | PHY_TABLE_SCAN         | 2020-07-06 11:18:34.209581 | 2020-07-06 11:18:34.211721 |     0.002140 | 2020-07-06 11:18:34.210663 | 2020-07-06 11:18:34.211721 |    0.001058 |          25 |            1 |
| 10.10.1.20 |    19510 |        16882 |            7 | PHY_GRANULE_ITERATOR   | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.216951 |     0.005230 | 2020-07-06 11:18:34.212757 | 2020-07-06 11:18:34.216951 |    0.004194 |         300 |            0 |
| 10.10.1.21 |    19510 |        16883 |            7 | PHY_GRANULE_ITERATOR   | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.212757 |     0.001036 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.22 |    19510 |        16891 |            7 | PHY_GRANULE_ITERATOR   | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.214833 |     0.003112 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.23 |    19510 |        16882 |            8 | PHY_TABLE_SCAN         | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.216951 |     0.005230 | 2020-07-06 11:18:34.212757 | 2020-07-06 11:18:34.216951 |    0.004194 |         300 |            1 |
| 10.10.1.24 |    19510 |        16883 |            8 | PHY_TABLE_SCAN         | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.212757 |     0.001036 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
| 10.10.1.25 |    19510 |        16891 |            8 | PHY_TABLE_SCAN         | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.214833 |     0.003112 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |    0.000000 |           0 |            0 |
+---------------+----------+--------------+--------------+------------------------+----------------------------+----------------------------+--------------+----------------------------+----------------------------+-------------+-------------+--------------+
25 rows in set 
obclient> SELECT * FROM oceanbase.V$SQL_MONITOR_STATNAME;     
+--------+----+----------+--------------------------+-------------------------------------------------+------+-------+     
| CON_ID | ID | GROUP_ID | NAME                     | DESCRIPTION                                     | TYPE | FLAGS |
+--------+----+----------+--------------------------+-------------------------------------------------+------+-------+
|   NULL |  1 |        0 | min hash entry count     | element count in shortest hash slot             |    0 |     0 |
|   NULL |  2 |        0 | max hash entry count     | element count in longest hash slot              |    0 |     0 |
|   NULL |  3 |        0 | total hash entry count   | total element count in all slots                |    0 |     0 |
|   NULL |  4 |        0 | slot size                | total hash bucket count                         |    0 |     0 |
|   NULL |  5 |        0 | non-empty bucket count   | non-empty hash bucket count                     |    0 |     0 |
|   NULL |  6 |        0 | total row count          | total row count building hash table             |    0 |     0 |
|   NULL |  7 |        0 | total miss count         | the total count of dtl loop miss                |    0 |     0 |
|   NULL |  8 |        0 | total miss count         | the total count of dtl loop miss after get data |    0 |     0 |
|   NULL |  9 |        0 | hash bucket init size    | init hash bucket count                          |    0 |     0 |
|   NULL | 10 |        0 | hash distinct block mode | hash distinct block mode                        |    0 |     0 |
+--------+----+----------+--------------------------+-------------------------------------------------+------+-------+
10 rows in set 
-- 其中的一个片段信息
            SVR_IP: 10.101.163.79
          SVR_PORT: 19510
      PROCESS_NAME: 49361
      PLAN_LINE_ID: 2
    PLAN_OPERATION: PHY_HASH_JOIN
FIRST_REFRESH_TIME: 2020-07-06 11:57:39.832042
 LAST_REFRESH_TIME: 2020-07-06 11:57:39.840455
      REFRESH_TIME: 0.008413
 FIRST_CHANGE_TIME: 2020-07-06 11:57:39.835199
  LAST_CHANGE_TIME: 2020-07-06 11:57:39.839398
       CHANGE_TIME: 0.004199
       OUTPUT_ROWS: 300
      RESCAN_TIMES: 0
    OTHERSTAT_1_ID: 1
 OTHERSTAT_1_VALUE: 1
    OTHERSTAT_2_ID: 2
 OTHERSTAT_2_VALUE: 2
    OTHERSTAT_3_ID: 3
 OTHERSTAT_3_VALUE: 25
    OTHERSTAT_4_ID: 4
 OTHERSTAT_4_VALUE: 64
    OTHERSTAT_5_ID: 5
 OTHERSTAT_5_VALUE: 20
    OTHERSTAT_6_ID: 6
 OTHERSTAT_6_VALUE: 25

oceanbase 分布式数据库

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

关注作者