Real-time execution plan display|V4.3.0| docs|Distributed Database

Real-time execution plan display

Last Updated：2024-05-29 07:17:41 Updated

OceanBase Database can display the logical and physical execution plans of SQL statements.

You can use the DBMS_XPLAN system package of OceanBase Database to query logical execution plans. You can query the (G)V$OB_PLAN_CACHE_PLAN_EXPLAIN view for the physical execution plan of an SQL statement in the plan cache, and the GV$SQL_PLAN_MONITOR view for information about tenant-level execution plans.

Query a logical execution plan

When you execute a large SQL query, if the current session is running for a long time, you may need to know the execution status of the query, such as the execution plan and execution process. In this case, because the current session is occupied by the large SQL query, you need to initiate a new session to locate the session of the large SQL query by using the SHOW PROCESSLIST statement, and then use the obtained session_id and the DISPLAY_ACTIVE_SESSION_PLAN function in the DBMS_XPLAN system package to show the execution details of the large SQL query.

The following example shows how to use the DBMS_XPLAN system package to query logical execution plans.

Connect to the database and execute a slow SQL query.

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

Initiate another session and execute the SHOW PROCESSLIST statement to obtain the ID of the session running the slow SQL query.

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 system package to show the active execution plan in the specified session.

/* Show the active execution plan in the 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))))                                                                    |
+--------------------------------------------------------------------------------------------------------------------+

/* Show the active execution plan in the 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))))                                                                    |
+--------------------------------------------------------------------------------------------------------------------+

You can also use the GV$OB_SQL_PLAN (or V$OB_SQL_PLAN) view to query the execution plans of the current tenant on all OBServer nodes (or the current OBServer node). However, we recommend that you use the DBMS_XPLAN system package to view the execution plans.

The syntax for querying 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 syntax for querying 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 fields in the (G)V$OB_SQL_PLAN view.

Field	Description
TENANT_ID	The ID of the tenant. The value `1` indicates the sys tenant ID. Other values indicate a user tenant or meta tenant ID.
PLAN_ID	The ID of the plan.
SVR_IP	The IP address of the OBServer node where the replica resides.
SVR_PORT	The port number of the OBServer node where the replica resides.
SQL_ID	The ID of the query.
DB_ID	The ID of the schema to which the query belongs.
PLAN_HASH_VALUE	The hash value of the plan.
GMT_CREATE	The time when the record was generated.
OPERATOR	The name of the operator, such as `TABLE SCAN` or `SORT`.
OPTIONS	Reserved for future use.
OBJECT_NODE	The database link (DBLink) if the current operator is associated with a DBLink.
OBJECT_ID	The ID of the scanned object, such as 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, such as the name of the physical table scanned by the `TABLE SCAN` operator.
OBJECT_ALIAS	The alias of the scanned object, such as the alias of the physical table scanned by the `TABLE SCAN` operator.
OBJECT_TYPE	The type of the scanned object, such as a synonym, DBLink, or basic table.
OPTIMIZER	The index-related information, such as the numbers of physical rows, logical rows, and rows returned after table access by index primary key, as well as the type and version of statistics.
ID	The ID of the logical operator.
PARENT_ID	The ID of the parent operator of the logical operator.
DEPTH	The depth of the logical operator in the current plan, that is, the level in the binary plan tree.
POSITION	The sequential position of the current logical operator in the parent operator.
SEARCH_COLUMNS	Reserved for future use.
IS_LAST_CHILD	Indicates whether the current logical operator is the last child node of the parent operator.
COST	The cost estimated by the optimizer.
REAL_COST	The actual cost of the plan for the first execution based on the execution feedback.
CARDINALITY	The number of output rows of the current operator estimated by the optimizer.
REAL_CARDINALITY	The actual number of output rows of the current operator for the first execution based on the execution feedback.
BYTES	The data width of the current operator estimated by the optimizer.
ROWSET	The vectorized size of the current operator.
OTHER_TAG	This field is for compatibility with Oracle. Currently, OceanBase Database records the hint used in the current query in this field, which is stored only in the first row of the plan.
PARTITION_START	The partition scanned by `TABLE SCAN`.
PARTITION_STOP	Reserved for future use.
PARTITION_ID	Reserved for future use.
OTHER	This field is for compatibility with Oracle. Currently, OceanBase Database records optimization information, such as the plan type, parameterization information of the fast parser, plan note, and constraints hit by the plan, in this field.
DISTRIBUTION	Reserved for future use.
CPU_COST	The actual CPU overhead of the current operator during the first execution of the plan based on the execution feedback.
IO_COST	The actual I/O overhead of the current operator during the first execution of the plan based on the execution feedback.
TEMP_SPACE	Reserved for future use.
ACCESS_PREDICATES	The access conditions of the current operator.
FILTER_PREDICATES	The filter conditions of the current operator.
STARTUP_PREDICATES	The startup conditions of the current operator.
PROJECTION	The output expression information of the current operator.
SPECIAL_PREDICATES	The expressions specific to the current operator, such as `JOIN condition`, `GROUP exprs`, and `SORT exprs`.
TIME	Reserved for future use.
QBLOCK_NAME	The name of the query block in which the current operator resides.
REMARKS	This field is for compatibility with Oracle. Currently, OceanBase Database records the change trace of `qb_name` in this field.
OTHER_XML	This field is for compatibility with Oracle. Currently, OceanBase Database records Outline Data in this field.

Query a physical execution plan

You can execute the EXPLAIN statement to demonstrate the execution plan generated by the current optimizer for an SQL query. However, changes in statistics and settings of user session variables may lead to differences between the return result of the EXPLAIN statement and the corresponding SQL plan in the plan cache. To determine the execution plans that are used by the SQL query, you must analyze the physical execution plans in the plan cache.

In OceanBase Database, the plan cache of each server is independent. You can access the V$OB_PLAN_CACHE_PLAN_STAT view to query the plan cache on the current server. You can provide the tenant_id and the SQL string to be queried (fuzzy matching supported) to obtain the corresponding plan_id of the SQL statement in the plan cache, and then display the physical execution plan of the SQL statement in the plan cache.

The syntax for querying 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()'

The following example shows how to query the V$OB_PLAN_CACHE_PLAN_EXPLAIN view for a physical execution plan.

Query the V$OB_PLAN_CACHE_PLAN_STAT view to obtain 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

After you obtain plan_id, you can access the V$OB_PLAN_CACHE_PLAN_EXPLAIN view to query information about the execution plan by using tenant_id and plan_id.

Notice

The plan demonstrated here is a physical execution plan. Operator names in the plan may differ from those in the logical execution plan demonstrated by executing 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 monitoring

Starting from V4.0, OceanBase Database supports real-time SQL plan monitoring. You can query the tenant-level GV$SQL_PLAN_MONITOR view to obtain information about logical and physical execution plans, including the numbers of rows returned by operators, the start and end time of operators, and the execution status of operators in each execution thread.

Starting from V4.0, OceanBase Database supports the real-time SQL plan monitor feature, which is used to track detailed performance metrics for each execution operator, such as first row return time, number of output rows, HASH conflict rate, and number of disk writes. Users can obtain execution plan-related information, including logical execution plans, physical execution plans, operator output row counts, operator start and end times, as well as the execution status of operators executed by each execution thread, by querying the tenant-level view GV$SQL_PLAN_MONITOR.

Enable SQL plan monitor

You can enable SQL plan monitor by setting enable_perf_event to true.

After SQL plan monitor is enabled, the following information will be recorded in SQL plan monitor:

Queries explicitly specified using the /*+ MONITOR */ hint.
Queries that executed parallel DML statements.
The first execution operator in a query that takes more than 3 seconds to complete.

Set the maximum memory usage for SQL plan monitor

To prevent monitoring data from consuming too much memory, you can limit the maximum memory usage of SQL plan monitor by adjusting the value of ob_sql_plan_monitor_percentage.

After SQL plan monitor is enabled, you can control the maximum number of cached data rows by changing the value of ob_sql_plan_monitor_percentage. If the memory threshold is reached, SQL plan monitor will adopt a First In, First Out (FIFO) strategy to clear old data.

The OTHERSTAT_?_ID and OTHERSTAT_?_VALUE fields in the GV$SQL_PLAN_MONITORSQL_PLAN_MONITOR view record operator-specific performance statistics. Currently, the view supports 10 such fields. The name of the statistics item that is recorded in each field is represented by an ID. You can query the V$SQL_MONITOR_STATNAME view to check the specific meaning corresponding to each ID.

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

The following example shows how to query execution plan information from the GV$SQL_PLAN_MONITOR and V$SQL_MONITOR_STATNAME views.

Query the GV$OB_SQL_AUDIT view for 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 the execution plan summary or details.

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 the execution plan details.

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 to obtain the meaning of the numbers in OTHERSTAT_?_ID fields in GV$SQL_PLAN_MONITOR.

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 sample fragment

            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