Use the DBMS_XPLAN package for performance diagnostics

The DBMS_XPLAN package provides features for managing logical plans, for example, optimizing and tracing logical plans.

Overview

display_cursor

Description

OceanBase Database stores all executed query plans, including physical and logical plans, to facilitate subsequent troubleshooting. To help you interpret historical query plans, OceanBase Database provides DBMS packages for formatting plans. Unlike the plans retrieved by using the EXPLAIN statement, plans that you executed are retained in the database even if you have logged out. The database clears stored query plans only when you restart the cluster.

Syntax

-- display sql plan table`s plan
function display_cursor(plan_id      integer default 0,             -- default value: last plan
                        format		 varchar2 default 'TYPICAL',
                        svr_ip       varchar2 default null,         -- default value: server connected by client
                        svr_port     integer default 0,             -- default value: server connected by client
                        tenant_id	 integer default 0              -- default value: current tenant
                        )
return dbms_xplan_type_table;

The parameters are described as follows:

• plan_id: the ID of the plan. If this parameter is not specified, the plan executed last time is used.
• format: the format of the plan. If this parameter is not specified, the format of the plan executed last time is used.
• svr_ip and svr_port: the IP address and port number of the OBServer node where the plan is stored. By default, the IP address and port number of the OBServer node to which the session is connected are used.
• tenant_id: the ID of the tenant to which the plan belongs. By default, the ID of the tenant to which the session is connected is used.

Related data dictionaries

Plans are stored in the __all_virtual_sql_plan data dictionary. Each tenant contains corresponding system views gv$ob_sql_plan and v$ob_sql_plan. The gv$ob_sql_plan view displays the plans on all OBServer nodes of the current tenant. The v$ob_sql_plan view displays the plans on the current OBServer node of the current tenant.

+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| tenant_id          | bigint(20)          | NO   | PRI | NULL    |       |
| plan_id            | bigint(20)          | NO   | PRI | NULL    |       |
| svr_ip             | varchar(46)         | NO   | PRI | NULL    |       |
| svr_port           | bigint(20)          | NO   | PRI | NULL    |       |
| sql_id             | varchar(32)         | NO   |     | NULL    |       |
| db_id              | bigint(20)          | NO   |     | NULL    |       |
| plan_hash          | bigint(20) unsigned | NO   |     | NULL    |       |
| gmt_create         | timestamp(6)        | NO   |     | NULL    |       |
| operator           | varchar(255)        | NO   |     | NULL    |       |
| options            | varchar(255)        | NO   |     | NULL    |       |
| object_node        | varchar(40)         | NO   |     | NULL    |       |
| object_id          | bigint(20)          | NO   |     | NULL    |       |
| object_owner       | varchar(128)        | NO   |     | NULL    |       |
| object_name        | varchar(128)        | NO   |     | NULL    |       |
| object_alias       | varchar(261)        | NO   |     | NULL    |       |
| object_type        | varchar(20)         | NO   |     | NULL    |       |
| optimizer          | varchar(4000)       | NO   |     | NULL    |       |
| id                 | bigint(20)          | NO   |     | NULL    |       |
| parent_id          | bigint(20)          | NO   |     | NULL    |       |
| depth              | bigint(20)          | NO   |     | NULL    |       |
| position           | bigint(20)          | NO   |     | NULL    |       |
| search_columns     | bigint(20)          | NO   |     | NULL    |       |
| is_last_child      | bigint(20)          | NO   |     | NULL    |       |
| cost               | bigint(20)          | NO   |     | NULL    |       |
| real_cost          | bigint(20)          | NO   |     | NULL    |       |
| cardinality        | bigint(20)          | NO   |     | NULL    |       |
| real_cardinality   | bigint(20)          | NO   |     | NULL    |       |
| bytes              | bigint(20)          | NO   |     | NULL    |       |
| rowset             | bigint(20)          | NO   |     | NULL    |       |
| other_tag          | varchar(4000)       | NO   |     | NULL    |       |
| partition_start    | varchar(4000)       | NO   |     | NULL    |       |
| partition_stop     | varchar(4000)       | NO   |     | NULL    |       |
| partition_id       | bigint(20)          | NO   |     | NULL    |       |
| other              | varchar(4000)       | NO   |     | NULL    |       |
| distribution       | varchar(64)         | NO   |     | NULL    |       |
| cpu_cost           | bigint(20)          | NO   |     | NULL    |       |
| io_cost            | bigint(20)          | NO   |     | NULL    |       |
| temp_space         | bigint(20)          | NO   |     | NULL    |       |
| access_predicates  | varchar(4000)       | NO   |     | NULL    |       |
| filter_predicates  | varchar(4000)       | NO   |     | NULL    |       |
| startup_predicates | varchar(4000)       | NO   |     | NULL    |       |
| projection         | varchar(4000)       | NO   |     | NULL    |       |
| special_predicates | varchar(4000)       | NO   |     | NULL    |       |
| time               | bigint(20)          | NO   |     | NULL    |       |
| qblock_name        | varchar(128)        | NO   |     | NULL    |       |
| remarks            | varchar(4000)       | NO   |     | NULL    |       |
| other_xml          | varchar(4000)       | NO   |     | NULL    |       |
+--------------------+---------------------+------+-----+---------+-------+

display_active_session_plan

Description

Assume that the execution of a large SQL statement has lasted for a long time and you want to know the execution information, such as the execution plan and execution process, of the statement. In this scenario, the current session is occupied by the large SQL statement, and you must establish a new connection. Then, execute the SHOW FULL PROCESSLIST statement to find the session in which the large SQL statement is executed, and execute the display_active_session_plan function based on the obtained session ID to obtain the execution details of the large SQL statement.

Syntax

-- disable real time plan
function display_active_session_plan(
           session_id   integer default 0,
           format       varchar2  default  'TYPICAL',
           svr_ip       varchar2 default null,      -- default value: server connected by client
           svr_port     integer default 0           -- default value: server connected by client
           )
return dbms_xplan_type_table;

• session_id: the session ID of the user connection. Note that the value is not the session ID of OceanBase Database Proxy (ODP) but the session ID of the connected OBServer node.
• format: the format of the plan. Valid values: BASIC, TYPICAL, ALL, and ADVANCED.
• svr_ip and svr_port: the IP address and port number of the OBServer node where the session resides. By default, the IP address and port number of the OBServer node to which the session is connected are used.

enable_opt_trace

Description

The process of generating plans by the optimizer is complex. It takes a lot of time to collect information during troubleshooting for suboptimal plans. This feature provides an end-to-end tracing mechanism to collect at a time all information required by the optimizer to generate a plan. This helps analyze the cause of suboptimal plans. This feature collects the following trace information:

• env:
  • System and session information
  • User SQL statements
  • Optimizer-related variables
• transformer:
  • SQL statements before and after rewriting based on each rewrite rule
  • Detailed reasons why each rewrite rule triggers or does not trigger rewriting: For example, rewriting is controlled by a hint, or specific conditions are not met.
• optimizer:
  • Statistics used
  • Base table path generation logs, including process conditions, the number of rows, cost estimation information, and skyline pruning rules and processes
  • Detailed process of join order enumeration
  • Allocation and optimization processes of top N operators

Syntax

DBMS_XPLAN.ENABLE_OPT_TRACE

DEFAULT_INENTIFIER  constant VARCHAR2(20) := '';
DEFAULT_LEVEL       constant INT : = 1;
PROCEDURE enable_opt_trace(
    sql_id          IN VARCHAR2 DEFAULT '',
    identifier      IN VARCHAR2 DEFAULT DEFAULT_INENTIFIER,
    level           IN INT DEFAULT DEFAULT_LEVEL
);

You can use the DBMS_XPLAN.ENABLE_OPT_TRACE function to enable end-to-end tracing for the optimizer of the current session. After end-to-end tracing is enabled, the generation process of each plan in the current session is traced.

The parameters are described as follows:

• sql_id: the ID of the SQL statement to be traced. For example, you can specify this parameter if you need to run PL programs in the current test and you want to trace only a specific SQL statement in PL functions. If this parameter is not specified, all SQL statements are traced.

• level: the level of tracing. Valid values:

  • 0: Default actions are performed.
  • 1: Memory usage and running duration of each module are recorded, in addition to level-0 tracing actions.
  • 2: The SQL statement corresponding to each rewrite query block is recorded regardless of whether the SQL statement is rewritten, in addition to level-0 and level-1 tracing actions.

  Notice

  level is a database keyword and therefore must be enclosed by double quotation marks (" ") in an Oracle tenant and by grave accents (\` \`) in a MySQL tenant.

• identifier: the file name extension of the trace file, which helps you find the trace file.

DBMS_XPLAN.DISABLE_OPT_TRACE

PROCEDURE disable_opt_trace;

You can use the DBMS_XPLAN.DISABLE_OPT_TRACE function to disable end-to-end tracing for the optimizer of the current session.

DBMS_XPLAN.SET_OPT_TRACE_PARAMETER

PROCEDURE set_opt_trace_parameter(
    sql_id          IN VARCHAR2 DEFAULT '',
    identifier      IN VARCHAR2 DEFAULT DEFAULT_INENTIFIER,
    level           IN INT DEFAULT DEFAULT_LEVEL
);

You can use the DBMS_XPLAN.SET_OPT_TRACE_PARAMETER function to set end-to-end tracing parameters for the optimizer of the current session.

Typical diagnostics scenarios

Query executed with slow speed

Collect query execution details

Query executed slowly and stalled

Collect plan execution details

Plan generated slowly or unexpected plan generated due to insufficient memory

Collect query optimization information

Information interpretation

Columns of the plan table

Optimization information

Optimization Info:
-------------------------------------
  t1:
      table_rows:3
      physical_range_rows:1	
      logical_range_rows:1
      index_back_rows:0	
      output_rows:1	
      table_dop:1		
      dop_method:Table DOP
      avaiable_index_name:[idx_ct, idx_pc, t1]
      pruned_index_name:[idx_ct, idx_pc]
      stats version:0
      dynamic sampling level:0
      estimation method:[DEFAULT]
  Plan Type:
      DISTRIBUTED
  Note:
      Degree of Parallelism is 1 because of table property

Analyze the cause of poor performance of a plan

1. Locate the top N operators with the longest CPU time, excluding the EXCHANGE IN, EXCHANGE OUT, and PX COORD operators. Assume that the following operators have long CPU time:

   • TABLE SCAN: Check whether the value of the is_index_back field is true in the Outputs & filters section. If yes, check the value of the index_back_rows field in the Optimization Info section. If the value is large, optimize the index. If the value of the REAL.ROWS field is much greater than that of the EST.ROWS field, check whether statistics have been collected, or check the value of the stats version field in the Optimization Info section to see if statistics have expired. If statistics have been collected and not expired, check for complex filtering conditions such as CASE WHEN and LIKE. If complex filtering conditions exist, you can enable dynamic sampling by using the /+dynamic_sampling(1)/ hint to improve the accuracy of row estimation. If this operator is on the right side of the NESTED-LOOP JOIN or SUBPLAN FILTER operator, high overheads are caused by too many rescans.
   • NESTED-LOOP JOIN: Check the estimated number of rows to be returned by the operator on the left side. If the number of rows is inaccurate, check the status of the statistics, or enable dynamic sampling by using the /*+dynamic_sampling(1)*/ hint to improve the accuracy of row estimation. If the performance is still poor, use the /*+use_hash(xxx)*/ hint to bind another plan. If the estimated number of rows is approximate but the performance is still poor, check the information in the Outputs & filters section to determine whether a batch join is performed.
   • SUBPLAN FILTER: Check the estimated number of rows to be returned by the operator on the left side. If the number of rows is inaccurate, check the status of the statistics, or enable dynamic sampling by using the /*+dynamic_sampling(1)*/ hint to improve the accuracy of row estimation. If the estimated number of rows is approximate but the performance is still poor, check the information in the Outputs & filters section to determine whether a batch operation is performed. If the poor performance is not caused by the preceding factors, find the corresponding subquery in the SQL statement and attempt to rewrite and optimize the query by using the /*+unnest*/ hint.

   If other operators run slowly, check the estimated number of rows and the status of statistics. If performance is still poor after you collect statistics or enable dynamic sampling, the amount of data is excessively large. In this case, you must enable and execute the /*+parallel(xxx)*/ hint. For the INSERT, UPDATE, DELETE, and MERGE operators, you must enable parallel DML (PDML) by using the /*+parallel(xxx) enable_parallel_dml*/ hint to optimize the query.

2. If the HASH DISTINCT, SORT, HASH GROUP BY, and HASH JOIN operators consume I/O time, results of these operators are flushed to the disk. In this case, you can modify the sql_work_area_size parameter as needed.

About the opt_trace log file

To understand the opt_trace log file, you must familiarize yourself with basic working principles of the OceanBase optimizer, including the iterative query rewriting process, index optimization, join enumeration, and distributed plan optimization.

The opt_trace log file records the following information:

transformer:

• SQL statements before and after rewriting based on each rewrite rule
• Detailed reasons why each rewrite rule triggers or does not trigger rewriting: For example, rewriting is controlled by a hint, or specific conditions are not met.

optimizer:

• Statistics used
• Base table path generation logs, including process conditions, the number of rows, cost estimation information, and skyline pruning rules and processes
• Detailed process of join order enumeration
• Allocation and optimization processes of top N operators

The log file also records the time and memory used at the end of each module.

SECTION TIME USAGE: 233135 us
TOTAL TIME USAGE: 233135 us
SECTION MEM USAGE: 48744 KB
TOTAL MEM USAGE: 62961 KB

• SECTION TIME USAGE indicates the time elapsed from the end of the last optimization step to the end of the current optimization step.
• TOTAL TIME USAGE indicates the time elapsed from the beginning of query optimization to the end of the current optimization step.
• SECTION MEM USAGE indicates the tenant memory used from the end of the last optimization step to the end of the current optimization step.
• TOTAL MEM USAGE indicates the tenant memory used from the beginning of query optimization to the end of the current optimization step.

The preceding information helps you quickly identify time-consuming and memory-consuming optimization steps during plan generation. You can use a hint to disable the corresponding optimization feature, instead of using the NO_REWRITE hint to disable rewriting to reduce plan generation overheads.

References

• For more information about the DBMS_XPLAN package, see DBMS_XPLAN (MySQL compatible mode) and DBMS_XPLAN (Oracle compatible mode).