SQL optimization is time-consuming. To avoid repeated SQL optimization, plans generated are stored in the plan cache and retrieved from the plan cache when the related SQL statement is executed again. On each node, a separate plan cache is allocated for each tenant to cache the SQL plans processed on this node.
Plan cache views display plan cache statistics, execution statistics, and plan information. The data displayed in these views is collected from plan caches. OceanBase Database provides the following plan cache views:
GV$OB_PLAN_CACHE_STAT: displays the statistics of each plan cache. On each node, a separate plan cache is allocated for each tenant to cache the SQL plans processed on this node.GV$OB_PLAN_CACHE_PLAN_EXPLAIN: displays the information about operators in the cached plans.GV$OB_PLAN_CACHE_PLAN_STAT: displays the details of each cached plan and its execution statistics.
The GV$OB_PLAN_CACHE_PLAN_EXPLAIN view displays the details of each execution plan in the following main fields:
TENANT_ID: the ID of the tenant.SVR_IP: the IP address of the node.PLAN_ID: the ID of the execution plan.PLAN_DEPTH: the depth of the operator.OPERATOR: the name of the operator.NAME: the name of the table corresponding to the operator.ROWS: the estimated number of rows in the result.COST: the estimated cost.PROPERTY: the details of the operator.
Notice
- To query the
GV$OB_PLAN_CACHE_PLAN_EXPLAINview, you must specify theTENANT_ID,SVR_IP,SVR_PORT, andPLAN_IDfields. Otherwise, no result is returned. - To query the
V$OB_PLAN_CACHE_PLAN_EXPLAINview, you must specify theTENANT_IDandPLAN_IDfields. Otherwise, no result is returned.
The following example shows an execution plan:
obclient> select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where tenant_id=1 and svr_ip='xx.xx.xx.xx' and svr_port=2882 and plan_id=349;
+-----------+-------------+----------+---------+------------+--------------+----------------------+--------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+-------------+----------+---------+------------+--------------+----------------------+--------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | xx.xx.xx.xx | 2882 | 349 | 0 | 0 | PHY_DIRECT_RECEIVE | NULL | 1 | 6 | NULL |
| 1 | xx.xx.xx.xx | 2882 | 349 | 1 | 1 | PHY_DIRECT_TRANSMIT | NULL | 1 | 5 | NULL |
| 1 | xx.xx.xx.xx | 2882 | 349 | 2 | 2 | PHY_TABLE_SCAN | __all_server | 1 | 2 | table_rows:6, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, avaiable_index_name[__all_server] |
+-----------+-------------+----------+---------+------------+--------------+----------------------+--------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.04 sec)
The GV$OB_PLAN_CACHE_PLAN_STAT view displays the details of each cached plan and its execution statistics in the following fields:
TENANT_ID: the ID of the tenant.SVR_IP: the IP address of the node.PLAN_ID: the ID of the execution plan, which corresponds to thePLAN_IDfield in theGV$OB_SQL_AUDITview.SQL_ID: the ID of the SQL statement, which corresponds to theSQL_IDfield in theGV$OB_SQL_AUDITview.TYPE: the type of the execution plan.1indicates a local plan.2indicates a remote plan.3indicates a distributed plan.STATEMENT: the parameterized SQL statement.QUERY_SQL: the original SQL statement queried when the plan was loaded for the first time.FIRST_LOAD_TIME: the time when the plan was loaded for the first time.SCHEMA_VERSION: the version of the schema.LAST_ACTIVE_TIME: the time of the last execution.AVG_EXE_USEC: the average execution duration.SLOWEST_EXE_TIME: the timestamp when the slowest execution ended.SLOWEST_EXE_USEC: the amount of time consumed by the slowest execution.SLOW_COUNT: the number of times that the current plan was identified as a slow query. The query execution time threshold is specified by the cluster parametertrace_log_slow_query_watermark.HIT_COUNT: the number of hits.LARGE_QUERYS: the number of times that the current plan was identified as a large query. The query execution time threshold is specified by the cluster parameterlarge_query_threshold.DELAYED_LARGE_QUERYS: the number of times that the current plan was identified as a large query and dropped to the large query queue.TIMEOUT_COUNT: the number of timeouts.EXECUTIONS: the number of times the plan has been executed.DISK_READS: the number of physical reads.DIRECT_WRITES: the number of disk writes.BUFFER_GETS: the number of logical reads.APPLICATION_WAIT_TIME: the total amount of time spent waiting for events of theapplicationclass.CONCURRENCY_WAIT_TIME: the total amount of time spent waiting for events of theconcurrencyclass.USER_IO_WAIT_TIME: the total amount of time spent waiting for the events of theuser_ioclass.ROWS_PROCESSED: the total number of rows in the results selected for all executions or the number of rows modified by executing the ALTER TABLE statement.ELAPSED_TIME: the total time consumed by all executions, where the time consumed by each execution is defined as the time elapsed from when the execution request was received to when the execution was completed.CPU_TIME: the total amount of CPU time consumed by all executions.OUTLINE_ID: the ID of the outline. The value-1indicates that the plan is not generated based on a bound outline.OUTLINE_DATA: the information about the outline corresponding to the plan.TABLE_SCAN: indicates whether the query is a primary key scan.EVOLUTION: indicates whether the execution plan is evolving.EVO_EXECUTIONS: the number of evolutions.PS_STMT_ID: the ID of the PREPARE statement.