OceanBase Database can display the physical execution plans of SQL statements.
You can run the EXPLAIN command 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 command 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.
GV$OB_PLAN_CACHE_PLAN_EXPLAIN
You can query the GV$OB_PLAN_CACHE_PLAN_EXPLAIN view to check the execution plans of an SQL statement in the plan cache.
For example:
VIEW_DEFINITION='SELECT *
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
WHERE IP =host_ip() AND PORT = rpc_port()'
The following table describes the fields in the view.
| Field | Type (MySQL mode) | Type (Oracle mode) | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | number(38) | The ID of the tenant. |
| SVR_IP | varchar(46) | varchar(46) | The IP address. |
| SVR_PORT | bigint(20) | number(38) | Port number |
| PLAN_ID | bigint(20) | number(38) | The ID of the execution plan. |
| PLAN_DEPTH | bigint(20) | number(38) | The depth of the operator during display. |
| PLAN_LINE_ID | bigint(20) | number(38) | The number of the operator. |
| OPERATOR | varchar(128) | varchar(128) | The name of the operator. |
| NAME | varchar(256) | varchar(256) | The name of the table. |
| ROWS | bigint(20) | number(38) | The estimated number of rows in the result. |
| COST | bigint(20) | number(38) | The estimated cost. |
| PROPERTY | varchar(4096) | varchar(4096) | The information about the corresponding operator. |
Step 1 Query the plan_id value of the SQL query 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 server. When you specify the tenant_id parameter and the SQL string to be queried (fuzzy match is supported), you can find the corresponding plan_id value for the SQL statement in the plan cache.
obclient> SELECT * FROM V$OB_PLAN_CACHE_PLAN_STAT WHERE tenant_id= 1001 AND STATEMENT LIKE 'INSERT INTO T1 VALUES%'\G
***************************1. row ***************************
tenant_id: 1001
svr_ip:xx.xx.xx.xx
svr_port:15212
plan_id: 7
sql_id:0
type: 1
statement: insert into t1 values(1)
plan_hash:1
last_active_time:2016-05-28 19:08:57.416670
avg_exe_usec:0
slowest_exe_time:1970-01-01 08:00:00.000000
slowest_exe_usec:0
slow_count:0
hit_count:0
mem_used:8192
1 row in set
Step 2 Use the plan_id value to demonstrate the corresponding execution plan
After you retrieve the value of the plan_id parameter, you can use the values of the tenant_id and plan_id parameters to access the GV$OB_PLAN_CACHE_PLAN_EXPLAIN view and demonstrate the execution plan.
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 running the EXPLAIN command.
obclient> SELECT * FROM GV$OB_PLAN_CACHE_PLAN_EXPLAIN WHERE tenant_id = 1001 AND plan_id = 7;
+-----------+---------------+-------+---------+--------------------+------+------+------+
| TENANT_ID | IP | PORT | PLAN_ID | OPERATOR | NAME | ROWS | COST |
+-----------+---------------+-------+---------+--------------------+------+------+------+
| 1001 | xx.xx.xx.xx | 15212 | 7 | PHY_ROOT_TRANSMIT | NULL | 0 | 0 |
| 1001 | xx.xx.xx.xx | 15212 | 7 | PHY_INSERT | NULL | 0 | 0 |
| 1001 | xx.xx.xx.xx | 15212 | 7 | PHY_EXPR_VALUES | NULL | 0 | 0 |
+-----------+---------------+-------+---------+--------------------+------+------+------+
3 rows in set
Notice
- To access the
GV$OB_PLAN_CACHE_PLAN_EXPLAINview, you must specify values for the `IP`, `port`,tenant_id, andplan_idparameters. - To access the
V$OB_PLAN_CACHE_PLAN_EXPLAINview, you must specify values for thetenant_idandplan_idparameters. Otherwise, the system returns an empty set.
GV$SQL_PLAN_MONITOR
OceanBase Database V4.0 supports the real-time SQL plan monitor feature. You can query the tenant-level GV$SQL_PLAN_MONITOR view to obtain information about execution plans, including logical execution plans, physical execution plans, the number of rows delivered by operators, the execution start and end time of operators, and the execution status of operators in each execution thread.
Fields in GV$SQL_PLAN_MONITOR
| Field | Type | Nullable | Description |
|---|---|---|---|
| CON_ID | bigint(20) | NO | The ID of the tenant. |
| REQUEST_ID | bigint(20) | NO | The ID of the request. |
| KEY | null | NO | The external key. It can be used for joining virtual tables related to SQL_MONITOR. |
| STATUS | null | NO | The status of the operator. Valid values:EXECUTING: The operator is being executed.DONE(ERROR): An error occurred during the execution of the operator.DONE(FIRST N ROWS): The execution succeeded.DONE(ALL ROWS): The execution succeeded.DONE: The execution was interrupted. |
| SVR_IP | varchar(46) | NO | The IP address of the server on which the operator is located. |
| SVR_PORT | bigint(20) | NO | The port number of the server on which the operator is located. |
| TRACE_ID | varchar(64) | NO | The trace ID of the operator. |
| DB_TIME | bigint(20) | NO | The CPU time consumed by the operator. |
| USER_IO_WAIT_TIME | bigint(20) | NO | The total amount of time spent on waiting for events of the user_io class. |
| OTHER_WAIT_TIME | null | NO | The total amount of time spent on waiting for other events. |
| FIRST_REFRESH_TIME | timestamp(6) | NO | The time when the monitoring of the operator started. |
| LAST_REFRESH_TIME | timestamp(6) | NO | The time when the monitoring of the operator ended. |
| FIRST_CHANGE_TIME | timestamp(6) | NO | The time when the operator delivered the first row of data. |
| LAST_CHANGE_TIME | timestamp(6) | NO | The time when the operator delivered the last row of data. |
| REFRESH_COUNT | null | NO | The number of times the statistics data was refreshed. |
| SID | null | NO | The ID of the session. |
| PROCESS_NAME | bigint(20) | NO | The ID of the thread that executed the operator. |
| SQL_ID | null | NO | The ID of the SQL statement. |
| SQL_EXEC_START | null | NO | The time when the execution of the SQL statement started. |
| SQL_EXEC_ID | null | NO | The ID of the SQL request execution. |
| SQL_PLAN_HASH_VALUE | null | NO | The hash value of the SQL plan. |
| SQL_CHILD_ADDRESS | null | NO | The default value of this field is NULL. |
| PLAN_PARENT_ID | null | NO | The ID of the parent operator of the plan. |
| PLAN_LINE_ID | bigint(20) | NO | The default value of this field is NULL. |
| PLAN_OPERATION | varchar(128) | NO | The name of the operator. |
| PLAN_OPTIONS | null | NO | The default value of this field is NULL. |
| PLAN_OBJECT_OWNER | null | NO | The default value of this field is NULL. |
| PLAN_OBJECT_NAME | null | NO | The default value of this field is NULL. |
| PLAN_OBJECT_TYPE | null | NO | The default value of this field is NULL. |
| PLAN_DEPTH | bigint(20) | NO | The depth of the operator in the plan tree. |
| PLAN_POSITION | null | NO | The position of the operator among the operators that share the same parent operator. |
| PLAN_COST | null | NO | The cost of executing the operator. This value is calculated by the optimizer. |
| PLAN_CARDINALITY | null | NO | The number of rows that will be returned by the execution of the operator. This value is calculated by the optimizer. |
| PLAN_BYTES | null | NO | The number of bytes that will be returned by the execution of the operator. This value is calculated by the optimizer. |
| PLAN_TIME | null | NO | The estimated time that the execution of the operator will take. This value is calculated by the optimizer. |
| PLAN_PARTITION_START | null | NO | The default value of this field is NULL. |
| PLAN_PARTITION_STOP | null | NO | The default value of this field is NULL. |
| PLAN_CPU_COST | null | NO | The estimated CPU cost of executing the operator. This value is calculated by the optimizer. |
| PLAN_IO_COST | null | NO | The estimated I/O cost of executing the operator. This value is calculated by the optimizer. |
| PLAN_TEMP_SPACE | null | NO | The space that the operator is estimated to consume. This value is calculated by the optimizer. |
| STARTS | bigint(20) | NO | The number of times the operator has been rescanned. |
| OUTPUT_ROWS | bigint(20) | NO | The total number of rows returned from all the executions of the operator. |
| IO_INTERCONNECT_BYTES | null | NO | The number of bytes exchanged between the operator and the storage layer. |
| PHYSICAL_READ_REQUESTS | null | NO | The total number of I/O read requests sent by the operator. |
| PHYSICAL_READ_BYTES | null | NO | The total number of bytes in the I/O read requests sent by the operator. |
| PHYSICAL_WRITE_REQUESTS | null | NO | The total number of I/O write requests sent by the operator. |
| PHYSICAL_WRITE_BYTES | null | NO | The total number of bytes that the operator requested to write. |
| WORKAREA_MEM | null | NO | The size of the workarea that the operator occupies in the memory. |
| WORKAREA_MAX_MEM | null | NO | The maximum size of the workarea that the operator can occupy in the memory. |
| WORKAREA_TEMPSEG | null | NO | The dump space that the operator occupies on the disk. |
| WORKAREA_MAX_TEMPSEG | null | NO | The maximum size of the dump space that the operator can occupy on the disk. |
| OTHERSTAT_GROUP_ID | null | NO | The default value of this field is NULL. |
| OTHERSTAT_1_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_1_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_1_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_2_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_2_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_2_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_3_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_3_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_3_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_4_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_4_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_4_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_5_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_5_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_5_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_6_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_6_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_6_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_7_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_7_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_7_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_8_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_8_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_8_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_9_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_9_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_9_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_10_ID | bigint(20) | NO | Reserved for future use. |
| OTHERSTAT_10_TYPE | null | NO | Reserved for future use. |
| OTHERSTAT_10_VALUE | bigint(20) | NO | Reserved for future use. |
| OTHER_XML | null | NO | Other structured data that needs to be provided to the outside but cannot be written to the existing fields. The data in this field is parsed by external tools. |
| PLAN_OPERATION_INACTIVE | null | NO | The default value of this field is NULL. |
| OUTPUT_BATCHES | bigint(20) | NO | The number of times that the operator calls the get_next_batch interface in vectorized mode. |
| SKIPPED_ROWS_COUNT | bigint(20) | NO | The total number of rows that do not need to be calculated during operator calculation in vectorized mode. It is the total number of rows skipped. |
The OTHERSTAT_?_ID and OTHERSTAT_?_VALUE fields, which are 10 fields in total, in the GV$SQL_PLAN_MONITORSQL_PLAN_MONITOR view record operator-specific performance statistics. 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
Examples
Query execution plan information from the GV$SQL_PLAN_MONITORSQL_PLAN_MONITOR and V$SQL_MONITOR_STATNAME views.
-- Obtain the trace_id value.
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
-- Obtain the execution plan summary.
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
-- Obtain 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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.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.x.x.25 | 19510 | 16891 | 8 | PHY_TABLE_SCAN | 2020-07-06 11:18:34.211721 | 2020-07-06 11:18:34.214833 | 0.003112 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 | 0.000000 | 0 | 0 |
+---------------+----------+--------------+--------------+------------------------+----------------------------+----------------------------+--------------+----------------------------+----------------------------+-------------+-------------+--------------+
25 rows in set
obclient> SELECT * FROM oceanbase.v$sql_monitor_statname;
+--------+----+----------+--------------------------+-------------------------------------------------+------+-------+
| CON_ID | ID | GROUP_ID | NAME | DESCRIPTION | TYPE | FLAGS |
+--------+----+----------+--------------------------+-------------------------------------------------+------+-------+
| NULL | 1 | 0 | min hash entry count | element count in shortest hash slot | 0 | 0 |
| NULL | 2 | 0 | max hash entry count | element count in longest hash slot | 0 | 0 |
| NULL | 3 | 0 | total hash entry count | total element count in all slots | 0 | 0 |
| NULL | 4 | 0 | slot size | total hash bucket count | 0 | 0 |
| NULL | 5 | 0 | non-empty bucket count | non-empty hash bucket count | 0 | 0 |
| NULL | 6 | 0 | total row count | total row count building hash table | 0 | 0 |
| NULL | 7 | 0 | total miss count | the total count of dtl loop miss | 0 | 0 |
| NULL | 8 | 0 | total miss count | the total count of dtl loop miss after get data | 0 | 0 |
| NULL | 9 | 0 | hash bucket init size | init hash bucket count | 0 | 0 |
| NULL | 10 | 0 | hash distinct block mode | hash distinct block mode | 0 | 0 |
+--------+----+----------+--------------------------+-------------------------------------------------+------+-------+
10 rows in set
-- A 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