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

Display real-time execution plans

Last Updated：2025-12-08 02:58:23 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 a user executes a large SQL statement and the current connection takes a long time to respond, the user may want to know the execution status of the SQL statement, such as the execution plan and the execution process. In this case, the user 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 of the large 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, the GV$OB_SQL_PLAN (or V$OB_SQL_PLAN) view displays the execution plans of all (or current) nodes of the current tenant, but we recommend that you use the DBMS_XPLAN package to view execution plans.

The query definition for 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 for 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	The tenant ID. 1 indicates the system tenant ID, and other values indicate the user tenant ID or meta tenant ID.
PLAN_ID	The plan ID.
SVR_IP	The IP address of the server where the replica resides.
SVR_PORT	The port number of the server where the replica resides.
SQL_ID	The query ID.
DB_ID	The ID of the schema to which the query belongs.
PLAN_HASH_VALUE	The hash value of the plan.
GMT_CREATE	The record creation time.
OPERATOR	The operator name (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	The ID of the scanned object (for example, the ID of the physical table scanned by the `TABLE SCAN` operator).
OBJECT_OWNER	The user who owns the scanned object.
OBJECT_NAME	The name of the scanned object (for example, the name of the physical table scanned by the `TABLE SCAN` operator).
OBJECT_ALIAS	The alias of the scanned object (for example, the alias of the physical table scanned by the `TABLE SCAN` operator).
OBJECT_TYPE	The type of the scanned object (for example, synonym, dblink, basic table).
OPTIMIZER	Index-related information (for example, physical rows, logical rows, tables to be joined, the type of statistics used, and the version of the statistics).
ID	The ID of the logical operator.
PARENT_ID	The ID of the parent operator.
DEPTH	The depth of the operator in the current plan, that is, the layer of the operator in the binary plan tree.
POSITION	The position of the operator among its siblings.
SEARCH_COLUMNS	Reserved.
IS_LAST_CHILD	1 if the operator is the last child of its parent operator, otherwise 0.
COST	The cost estimated by the optimizer.
REAL_COST	The actual cost when the plan is executed for the first time (feedback information).
CARDINALITY	The number of output rows estimated by the optimizer for the operator.
REAL_CARDINALITY	The actual number of output rows when the operator is executed for the first time (feedback information).
BYTES	The width of data estimated by the optimizer for the operator.
ROWSET	The vectorized size of the operator.
OTHER_TAG	Oracle-compatible field. The OceanBase database saves in this field the hints used in the current query. This field is recorded only in the first row of a plan.
PARTITION_START	The partition information scanned by the `TABLE SCAN` operator.
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 parameters of Fast Parser, Plan Note, and the constraints that the plan hits.
DISTRIBUTION	Reserved.
CPU_COST	The actual CPU overhead of the operator when the plan is executed for the first time (feedback information).
IO_COST	The actual I/O overhead of the operator when the plan is executed for the first time (feedback information).
TEMP_SPACE	Reserved.
ACCESS_PREDICATES	The expression information that the operator needs to access.
FILTER_PREDICATES	The filtering conditions of the operator.
STARTUP_PREDICATES	The startup conditions of the operator.
PROJECTION	The output expression information of the operator.
SPECIAL_PREDICATES	The unique expression information of the operator, such as: `JOIN condition`, `GROUP exprs`, `SORT exprs`.
TIME	Reserved.
QBLOCK_NAME	The 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 actual execution plan used by the SQL query in the system may be different from the one displayed in the result of the EXPLAIN statement, as the actual execution plan can change due to statistics changes or user session variables changes. To determine the actual execution plan used by the SQL query in the system, you need to analyze the physical execution plans in the plan 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 to query the plan cache and provide the tenant_id and the SQL string (fuzzy matching is supported) to find the plan_id of the SQL query in the plan cache. Then, you can display the physical execution plan of the SQL query in the plan cache.

You can query the V$OB_PLAN_CACHE_PLAN_EXPLAIN view for the execution plan information.

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

This topic explains how to query the physical execution plan from the V$OB_PLAN_CACHE_PLAN_EXPLAIN view.

Query the V$OB_PLAN_CACHE_PLAN_STAT view for the plan_id of the SQL query 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 physical execution plan from the V$OB_PLAN_CACHE_PLAN_EXPLAIN view.

Notice

The plans displayed in the view are physical execution plans. The operator names in the plans differ from those displayed in the result of 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. It tracks detailed performance metrics of each execution operator, such as the first-row return time, output row count, hash collision rate, and disk I/Os. You can query the GV$SQL_PLAN_MONITOR tenant-level view for the execution plan information, including the number of rows produced by each operator and the start and end times of each operator.

Enable the SQL Plan Monitor feature

Set the enable_perf_event parameter to true to enable the SQL Plan Monitor feature.

After the SQL Plan Monitor feature is enabled, the following SQL statements are recorded in the SQL Plan Monitor:

Statements with the /*+ MONITOR */ hint.
Statements that execute parallel data modification language (PDML) statements.
Statements whose first expensive operator takes more than three seconds to execute.

Obtain information about execution plans

The OTHERSTAT_?_ID and OTHERSTAT_?_VALUE columns of the GV$SQL_PLAN_MONITORSQL_PLAN_MONITOR view record performance data specific to operators. Currently, 10 such columns are provided. The actual names of the data recorded in each column are represented by an ID. To understand the meaning of the ID, 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 explains how to query information about execution plans using the GV$SQL_PLAN_MONITOR and V$SQL_MONITOR_STATNAME views.

Query the GV$OB_SQL_AUDIT view for the 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

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 meaning of the ID 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

-- A snippet of the query result 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