Display real-time execution plans|V4.2.0| docs|Distributed Database

Display real-time execution plans

Last Updated：2025-12-04 05:37:52 Updated

You can display the logical and physical execution plans of an SQL query statement in real time.

The DBMS_XPLAN package of OceanBase Database is used to query the logical execution plan, and the (G)V$OB_PLAN_CACHE_PLAN_EXPLAIN view is used to query the physical execution plan of a specific SQL statement in the plan cache. The GV$SQL_PLAN_MONITOR view contains information about tenant-level execution plans.

Query the logical execution plan

When you execute a large SQL statement, if the current connection has been running for a long time, you may want to know the execution status of the SQL statement, such as the execution plan and the execution process. In this case, you can establish a new connection, use the SHOW PROCESSLIST command to locate the session ID of the SQL statement, and use the DBMS_XPLAN package and the DISPLAY_ACTIVE_SESSION_PLAN function to display the execution plan and process of the SQL statement.

This topic will show you how to query the logical execution plan by using the DBMS_XPLAN package.

Connect to the database and execute a slow SQL statement.

obclient> SELECT COUNT(*) FROM TABLE(GENERATOR(100000)) A, TABLE(GENERATOR(10000))B;   
+------------+
| COUNT(*)   |
+------------+
| 1000000000 |
+------------+
1 row in set

Connect to another session and use the SHOW PROCESSLIST command to query the session ID of the SQL statement.

obclient> 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 |
+------------+------+--------------------+------+---------+------+--------+---------------------------------------------------------------------------+

Use the DBMS_XPLAN package to display the plan details of the session.

/* Display the execution plan of a session in MySQL mode */
obclient> 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))))                                                                    |
+--------------------------------------------------------------------------------------------------------------------+

/* Display the execution plan of a session in Oracle mode */
obclient> 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))))                                                                    |
+--------------------------------------------------------------------------------------------------------------------+

Additionally, you can query the GV$OB_SQL_PLAN (or V$OB_SQL_PLAN) view for the execution plans of all (or current) nodes of the current tenant, but it is recommended to use the DBMS_XPLAN package to view execution plans.

The query definition of the GV$OB_SQL_PLAN view is as follows:

VIEW_DEFINITION='SELECT * FROM oceanbase.GV$OB_SQL_PLAN WHERE TENANT_ID = effective_tenant_id()'

The query definition of the V$OB_SQL_PLAN view is as follows:

VIEW_DEFINITION='SELECT * FROM oceanbase.V$OB_SQL_PLAN WHERE SVR_IP=host_ip() AND SVR_PORT=rpc_port()'

The following table describes the columns of the (G)V$OB_SQL_PLAN view.

Column	Description
TENANT_ID	Tenant ID. 1 indicates the system tenant ID, and other values indicate the user tenant ID or meta tenant ID.
PLAN_ID	Plan ID.
SVR_IP	IP address of the server where the replica resides.
SVR_PORT	Port number of the server where the replica resides.
SQL_ID	Query ID.
DB_ID	ID of the schema to which the query belongs.
PLAN_HASH_VALUE	Hash value of the plan.
GMT_CREATE	Time when the record is generated.
OPERATOR	Name of the operator (for example, `TABLE SCAN`, `SORT`).
OPTIONS	Reserved.
OBJECT_NODE	If the current operator is related to a dblink, the name of the dblink.
OBJECT_ID	ID of the scanned object (for example, the ID of the physical table scanned by the `TABLE SCAN` operator).
OBJECT_OWNER	Owner of the scanned object.
OBJECT_NAME	Name of the scanned object (for example, the name of the physical table scanned by the `TABLE SCAN` operator).
OBJECT_ALIAS	Alias of the scanned object (for example, the alias of the physical table scanned by the `TABLE SCAN` operator).
OBJECT_TYPE	Type of the scanned object (for example, synonym, dblink, basic table).
OPTIMIZER	Index information such as physical rows, logical rows, inner table rows, the type of statistics used, and the statistics version.
ID	ID of the logical operator.
PARENT_ID	ID of the parent operator.
DEPTH	Depth of the operator in the current plan, that is, the layer of the operator in the binary plan tree.
POSITION	Position of the operator among the children of its parent operator.
SEARCH_COLUMNS	Reserved.
IS_LAST_CHILD	1 if the operator is the last child of its parent operator; otherwise, 0.
COST	Cost estimated by the optimizer.
REAL_COST	Actual cost measured during the first execution of the plan (feedback information).
CARDINALITY	Number of output rows estimated by the optimizer for the operator.
REAL_CARDINALITY	Number of output rows during the first execution of the plan (feedback information).
BYTES	Width of data estimated by the optimizer for the operator.
ROWSET	Vectorized size of the operator.
OTHER_TAG	Oracle-compatible field. The OceanBase database saves in this field the hints used in the query. This field is recorded only in the first row of the plan.
PARTITION_START	`TABLE SCAN` information about the partition scanned.
PARTITION_STOP	Reserved.
PARTITION_ID	Reserved.
OTHER	Oracle-compatible field. The OceanBase database saves in this field optimization information such as the plan type, the parameterization information of Fast Parser, Plan Note, and constraints hit by the plan.
DISTRIBUTION	Reserved.
CPU_COST	CPU overhead measured during the first execution of the plan (feedback information).
IO_COST	I/O overhead measured during the first execution of the plan (feedback information).
TEMP_SPACE	Reserved.
ACCESS_PREDICATES	Expressions to be accessed by the operator.
FILTER_PREDICATES	Filtering conditions of the operator.
STARTUP_PREDICATES	Startup conditions of the operator.
PROJECTION	Output expressions of the operator.
SPECIAL_PREDICATES	Special expressions of the operator, such as: `JOIN condition`, `GROUP exprs`, `SORT exprs`.
TIME	Reserved.
QBLOCK_NAME	Name of the query block to which the operator belongs.
REMARKS	Oracle-compatible field. The OceanBase database saves in this field the modification tracking information of `qb_name`.
OTHER_XML	Oracle-compatible field. The OceanBase database saves in this field outline data.

Query the physical execution plan

You can execute the EXPLAIN statement to display the execution plan generated by the optimizer. However, the physical execution plan in the cache may be different from the one displayed by the EXPLAIN statement due to changes in statistics or session variables. To determine the actual execution plan used by the SQL statement in the system, you need to analyze the physical execution plans in the cache.

The plan cache of each server of OceanBase Database is independent. You can directly query the V$OB_PLAN_CACHE_PLAN_STAT view on the server for the plan cache information of the server. Specify the tenant_id and the SQL string to be queried (fuzzy matching is supported). This view provides the plan_id corresponding to the SQL statement in the plan cache. You can further display the physical execution plan of the SQL statement in the plan cache.

The query definition of the V$OB_PLAN_CACHE_PLAN_EXPLAIN view is as follows:

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

This example shows how to use the V$OB_PLAN_CACHE_PLAN_EXPLAIN view to query the physical execution plan.

Query the V$OB_PLAN_CACHE_PLAN_STAT view for the plan_id of the SQL statement in the plan cache.

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

Use the tenant_id and the plan_id to query the execution plan information from the V$OB_PLAN_CACHE_PLAN_EXPLAIN view.

Notice

The plan displayed here is a physical execution plan. The operator names are different from those displayed in the logical execution plan generated by the EXPLAIN statement.

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

Real-time SQL Plan Monitor

Starting from OceanBase Database V4.0, the Real-time SQL Plan Monitor feature is supported. With this feature, you can query the GV$SQL_PLAN_MONITOR tenant-level view for execution plan information, including the number of rows generated by each operator, the start and end times of each operator, and the execution status of each operator on each execution thread.

The OTHERSTAT_?_ID and OTHERSTAT_?_VALUE columns in the GV$SQL_PLAN_MONITOR view record operator-specific performance data. At present, 10 such columns are provided. The actual data names recorded in these columns are identified by ID. To learn the meanings of the IDs, you can query the V$SQL_MONITOR_STATNAME view.

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

This topic describes how to query execution plans in the GV$SQL_PLAN_MONITOR and V$SQL_MONITOR_STATNAME views.

Query the trace_id from the GV$OB_SQL_AUDIT view.

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

Query the GV$SQL_PLAN_MONITOR view for a summary or details of the execution plan.

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

You can also query the GV$SQL_PLAN_MONITOR view for details of the execution plan.

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

Query the V$SQL_MONITOR_STATNAME view for the meanings of the IDs in the OTHERSTAT_?_ID column of the GV$SQL_PLAN_MONITOR view.

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

-- One sample snippet is as follows:

            SVR_IP: xx.xx.xx.xx
          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

References

DISPLAY_ACTIVE_SESSION_PLAN

(G)V$OB_SQL_PLAN

(G)V$OB_PLAN_CACHE_PLAN_EXPLAIN

GV$SQL_PLAN_MONITOR

V$SQL_MONITOR_STATNAME