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

Display real-time execution plans

Last Updated：2025-12-08 03:03:28 Updated

You can display the logical and physical execution plans of an SQL 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 the 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 has been running for a long time, the user may want to know the execution status of the SQL statement, such as the execution plan and 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.DISPLAY_ACTIVE_SESSION_PLAN function to display the execution plan 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 plan details 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 plan details 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 tenant, but it is recommended to 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 name	Description
TENANT_ID	Tenant ID. The value 1 indicates the system tenant, and other values indicate user tenants or meta tenants.
PLAN_ID	Plan ID.
SVR_IP	IP address of the server where the replica corresponding to the plan resides.
SVR_PORT	Port number of the server where the replica corresponding to the plan 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	The user to which the scanned object belongs.
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-related information (for example, physical rows, logical rows, number of rows to be joined, the type of statistics used, and the version of statistics).
ID	ID of the logical operator.
PARENT_ID	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 sequence number 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 of the plan when it 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 of the operator when the plan 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	An Oracle-compatible field. The OceanBase database saves in this field the hints used in the query, and this information is stored only in the first row of the plan.
PARTITION_START	The partition information scanned by the `TABLE SCAN` operator.
PARTITION_STOP	Reserved.
PARTITION_ID	Reserved.
OTHER	An Oracle-compatible field. The OceanBase database saves in this field optimization information such as the plan type, parameterization information of the Fast Parser, Plan Note, and constraints that the plan hits.
DISTRIBUTION	Reserved.
CPU_COST	The actual CPU overhead of the operator in the plan when it is executed for the first time (feedback information).
IO_COST	The actual I/O overhead of the operator in the plan when it 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 condition of the operator.
STARTUP_PREDICATES	The startup condition 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	An Oracle-compatible field. The OceanBase database saves in this field the modification tracking information of `qb_name`.
OTHER_XML	An 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 plan cache may be different from the one displayed by the EXPLAIN statement due to changes in statistics or user session variables. 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 for the plan cache information of the server. Specify 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 physical 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 execution plan information 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 physical execution plan are different from those in the logical execution plan displayed 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. It tracks detailed performance metrics of each operator, such as the first-row return time, output row count, hash collision rate, and disk write frequency. You can query the GV$SQL_PLAN_MONITOR tenant-level view for information about execution plans, including the number of rows emitted 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 monitored:

Statements that are explicitly specified by using the /*+ MONITOR */ hint.
Statements that contain a parallel data modification statement (PDML).
Statements whose first expensive operator takes more than 3 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 execution plans by 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 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

-- A snippet of the query result is shown below.

            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_PLAN_CACHE_PLAN_EXPLAIN

GV$SQL_PLAN_MONITOR

V$SQL_MONITOR_STATNAME