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 setInitiate another session and execute the
SHOW PROCESSLISTstatement 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_XPLANsystem 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 columns in the (G)V$OB_SQL_PLAN view.
Column |
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 a physical table scanned by the TABLE SCAN operator. |
| OBJECT_OWNER | The user of the scanned object. |
| OBJECT_NAME | The name of the scanned object, such as the name of a physical table scanned by the TABLE SCAN operator. |
| OBJECT_ALIAS | The alias of the scanned object, such as the alias of a physical table scanned by the TABLE SCAN operator. |
| OBJECT_ALIAS | 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, namely the level in the binary plan tree. |
| POSITION | Indicates which child node of the parent operator the current logical operator is. |
| 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 | Indicates whether the current logical operator is the last child node of its parent operator. |
| 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 column is for compatibility with Oracle. Currently, OceanBase Database records the hint used in the current query in this column, 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 column 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 column. |
| 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 column is for compatibility with Oracle. Currently, OceanBase Database records the change trace of qb_name in this column. |
| OTHER_XML | This column is for compatibility with Oracle. Currently, OceanBase Database records Outline Data in this column. |
Query a physical execution plan
You can execute the EXPLAIN statement to demonstrate the current execution plan generated by the 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 plan that is 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_STATview to obtain theplan_idof 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 setAfter you obtain
plan_id, you can access theV$OB_PLAN_CACHE_PLAN_EXPLAINview to query information about the execution plan by usingtenant_idandplan_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
EXPLAINstatement.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 the real-time SQL plan monitoring feature. This feature tracks detailed performance metrics for each execution operator, such as the return time of the first row, number of output rows, HASH conflict rate, and number of disk writes. You can query the tenant-level view GV$SQL_PLAN_MONITOR for execution plan-related information, including the logical execution plans, physical execution plans, number of operator output rows, start time and end time of operator execution, as well as the status of operators executed by each execution thread.
Enable SQL plan monitoring
You can enable SQL plan monitoring by setting enable_perf_event to true.
After SQL plan monitoring is enabled, the following information is recorded:
- Queries explicitly specified using the
/*+ MONITOR */hint. - Queries that executed parallel DML statements.
- The first operator in a query that takes more than 3 seconds to complete the execution.
Obtain execution plan-related information
The OTHERSTAT_?_ID and OTHERSTAT_?_VALUE columns in the GV$SQL_PLAN_MONITORSQL_PLAN_MONITOR view record operator-specific performance statistics. Currently, the view supports 10 such columns. The name of the statistics item that is recorded in each column is represented by an ID. You can query the V$SQL_MONITOR_STATNAME view to check the specific meaning corresponding to each ID.
obclient [SYS]> DESC V$SQL_MONITOR_STATNAME;
+-------------+---------------+------+------+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------------+---------------+------+------+---------+-------+
| CON_ID | NUMBER | NO | NULL | NULL | NULL |
| ID | NUMBER | NO | NULL | NULL | NULL |
| GROUP_ID | NUMBER | NO | NULL | NULL | NULL |
| NAME | VARCHAR2(40) | NO | NULL | NULL | NULL |
| DESCRIPTION | VARCHAR2(200) | NO | NULL | NULL | NULL |
| TYPE | NUMBER | NO | NULL | NULL | NULL |
| FLAGS | NUMBER | NO | NULL | NULL | NULL |
+-------------+---------------+------+------+---------+-------+
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_AUDITview fortrace_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 setQuery the
GV$SQL_PLAN_MONITORview 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 setYou can also query the
GV$SQL_PLAN_MONITORview 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 setQuery the
V$SQL_MONITOR_STATNAMEview to obtain the meaning of the numbers inOTHERSTAT_?_IDcolumns inGV$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