Real-time execution plan display can show the logical and physical execution plans of an SQL statement.
In OceanBase AI Database, the DBMS_XPLAN system package is used to query logical execution plans. The (G)V$OB_PLAN_CACHE_PLAN_EXPLAIN view is used to query the physical execution plan of a specific SQL statement in the plan cache. The GV$SQL_PLAN_MONITOR view provides information related to tenant-level execution plans.
Query logical execution plans
When a user executes a large SQL statement, if the current connection takes too long to complete, the user needs to understand the query execution status, such as the execution plan and process. In this case, since the current session is occupied by the large SQL statement, the user needs to open a new connection. Use the SHOW PROCESSLIST command to find the session where the large SQL statement resides, and then use the session_id and the [DISPLAY_ACTIVE_SESSION_PLAN](../../../600.pl-reference/1000.pl-system-package-mysql/20700.dbms-xplan-mysql/200.display-active-session-plan-of-mysql-mode.md) function from the DBMS_XPLAN system package to display the execution details of the large SQL statement.
This topic uses a specific example to demonstrate how to use the DBMS_XPLAN system package to query logical execution plans.
Connect to the database and execute the slow SQL statement.
obclient> SELECT COUNT(*) FROM TABLE(GENERATOR(100000)) A, TABLE(GENERATOR(10000))B; +------------+ | COUNT(*) | +------------+ | 1000000000 | +------------+ 1 row in setConnect to another session and use the
SHOW PROCESSLISTcommand to query the Session ID of the SQL statement being executed.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 display the plan details of the session.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)))) | +--------------------------------------------------------------------------------------------------------------------+
Additionally, the GV$OB_SQL_PLAN (or V$OB_SQL_PLAN) view is used to query execution plans executed by using the EXPLAIN PLAN tool. However, it is recommended that you use the DBMS_XPLAN system 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 fields in the (G)V$OB_SQL_PLAN view.
Field |
Description |
|---|---|
| TENANT_ID | Tenant ID. 1 indicates the system tenant ID, and other values indicate a user tenant or Meta tenant ID. |
| PLAN_ID | Plan ID. |
| SVR_IP | The IP address of the node where the replica resides. |
| SVR_PORT | The port number of the node where the replica resides. |
| SQL_ID | Query ID. |
| DB_ID | Query the ID of the schema to which the object belongs. |
| PLAN_HASH_VALUE | Hash value of the plan. |
| GMT_CREATE | Record the generation time. |
| OPERATOR | Operator name (for example, TABLE SCAN or SORT). |
| OPTIONS | Reserved field. |
| OBJECT_NODE | If the current operator is related to dblink, it indicates the name of the dblink. |
| OBJECT_ID | Scan object ID (for example, the physical table ID scanned by the TABLE SCAN operator). |
| OBJECT_OWNER | User of the scanned object. |
| OBJECT_NAME | Scan object name (for example, the name of the physical table scanned by the TABLE SCAN operator). |
| OBJECT_ALIAS | Scan object alias (for example, the alias of the physical table scanned by the TABLE SCAN operator). |
| OBJECT_TYPE | The type of the scanned object, such as Synonym, Dblink, or Basic Table. |
| OPTIMIZER | Index-related information (such as physical rows, logical rows, number of rows returned from the table, type of statistics used, and version number of statistics). |
| ID | Logical operator ID. |
| PARENT_ID | Parent operator ID of the logical operator. |
| DEPTH | The depth of the logical operator in the current plan, that is, which level it occupies in the binary plan tree. |
| POSITION | The current logical operator is the nth child of the parent operator. |
| SEARCH_COLUMNS | Reserved field. |
| IS_LAST_CHILD | The current logical operator is the last child node of its parent operator. |
| COST | Estimated cost by the optimizer. |
| REAL_COST | The actual cost (execution feedback information) when the plan is executed for the first time. |
| CARDINALITY | The optimizer estimates the number of output rows for the current operator. |
| REAL_CARDINALITY | The actual number of rows output by the current operator during the first execution of the plan (execution feedback information). |
| BYTES | The optimizer estimates the data width of the current operator. |
| ROWSET | The vectorization size of the current operator. |
| OTHER_TAG | The information stored in OceanBase AI Database is the hints used in the current query, which are recorded only in the first row of the plan. |
| PARTITION_START | The partition information scanned by TABLE SCAN. |
| PARTITION_STOP | Reserved field. |
| PARTITION_ID | Reserved field. |
| OTHER | The information stored in OceanBase AI Database is optimization-related, such as the plan type, parameterized information of Fast Parser, Plan Note, and constraint information hit by the plan. |
| DISTRIBUTION | Reserved field. |
| CPU_COST | The actual CPU cost of the current operator during the first execution of the plan (execution feedback information). |
| IO_COST | The actual I/O overhead of the current operator during the first execution of the plan (execution feedback information). |
| TEMP_SPACE | Reserved field. |
| ACCESS_PREDICATES | The expression information that the operator needs to access. |
| FILTER_PREDICATES | The filter condition of the current operator. |
| STARTUP_PREDICATES | The startup conditions of the current operator. |
| PROJECTION | The output expression of the current operator. |
| SPECIAL_PREDICATES | Unique expression information of the current operator, for example, JOIN condition, GROUP exprs, SORT exprs, and so on. |
| TIME | Reserved field. |
| QBLOCK_NAME | The name of the query block where the current operator is located. |
| REMARKS | The information stored in OceanBase AI Database is qb_name modification tracking information. |
| OTHER_XML | The information stored in OceanBase AI Database is outline data. |
Query physical execution plans
Using the EXPLAIN command can display the execution plan generated by the current optimizer. However, changes in statistics or user session variable settings may cause the actual plan corresponding to this SQL statement in the plan cache to differ from the result of EXPLAIN. To determine the actual execution plan used by this SQL statement in the system, you need to further analyze the physical execution plans in the plan cache.
Each server's plan cache in OceanBase AI Database is independent. You can directly access the V$OB_PLAN_CACHE_PLAN_STAT view to query the plan cache on your own server and provide the tenant_id and the SQL string to query (fuzzy match can be used) to find the plan_id corresponding to that SQL in the plan cache. Then, further access the V$OB_PLAN_CACHE_PLAN_EXPLAIN view to display the physical execution plan of that SQL in the plan cache.
The query definition for 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()'
This topic uses a specific example to demonstrate how to use the V$OB_PLAN_CACHE_PLAN_EXPLAIN view to query physical execution plans.
Query the
V$OB_PLAN_CACHE_PLAN_STATview to obtain theplan_idof the SQL 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 obtaining the
plan_id, you can use thetenant_idandplan_idto access theV$OB_PLAN_CACHE_PLAN_EXPLAINview to query execution plan-related information.Notice
The plan shown here is a physical execution plan. Operator names may differ from those in the logical execution plan displayed by
EXPLAIN.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
OceanBase AI Database supports the real-time SQL Plan Monitor feature, which tracks detailed performance metrics for each execution operator, such as first-row return time, output row count, hash conflict rate, and disk flush count. You can query the tenant-level view GV$SQL_PLAN_MONITOR to obtain execution plan-related information, including the number of rows output by an operator, the start and end times of an operator, and the execution status of an operator by each execution thread.
Enable the SQL Plan Monitor feature
Set the parameter enable_perf_event to true to enable SQL Plan Monitor.
After SQL Plan Monitor is enabled, the following SQL statements will be recorded:
- Queries explicitly specified with the
/*+ MONITOR */hint. - Queries that execute parallel data modification (PDML) statements.
- The first execution operator in a query whose execution time exceeds 3 seconds.
Obtain execution plan-related information
The OTHERSTAT_?_ID and OTHERSTAT_?_VALUE fields in the GV$SQL_PLAN_MONITOR view are used to record operator-specific performance data. Currently, there are 10 such fields. The actual name of the data recorded in each field is represented by an ID. To understand the specific meaning of the ID, you need to query the V$SQL_MONITOR_STATNAME view.
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
This topic demonstrates how to query execution plan information using the GV$SQL_PLAN_MONITOR and V$SQL_MONITOR_STATNAME views through specific examples.
Query the
GV$OB_SQL_AUDITview to obtain thetrace_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 to obtain 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 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 in theOTHERSTAT_?_IDfields ofGV$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 -- Example of one of the segments 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
SQL Plan Monitor Extended Monitoring Metric PROFILE
The PROFILE field in the [G]V$SQL_PLAN_MONITOR view displays the extended monitoring metric profile in JSON format. For more information about this view, see GV$SQL_PLAN_MONITOR.
References
DISPLAY_ACTIVE_SESSION_PLAN