Execution analysis

Background information

The ODC SQL window provides a user interface for data processing. It displays system evaluations, execution details, and full-link duration information for SQL statements.

This document uses the `employee` table as an example to demonstrate how to obtain a system evaluation for a query SQL statement.

Execution plan

1. In the SQL window, write an SQL statement to query data from the employee table.

   

   SELECT `emp_no`, `birthday`, `name`, `time`, `test_col`, `status`, `age`, `state` FROM `employee`;

2. As shown in the preceding figure, select the SQL statement and click the Execution Plan icon. This action displays the estimated execution data, which is the result of an EXPLAINPLAN operation, before the statement runs. The estimated data may differ slightly from the actual execution data.

3. On the Plan Details page, click Text View to switch to a formatted display.

Execution details

Execution Profile access points

• Access point 1: While an SQL statement is running in the SQL window, click View Execution Profile on the Log tab.

  Important

  If the execution duration of the SQL statement is less than 1 second, the option to view the execution profile may not be displayed on the Log tab.

• Access point 2: After the SQL statement finishes executing, click the Execution Profile icon in the execution results.

• Access point 3: After the SQL statement finishes executing, click the TRACE ID on the Execution History tab to open the Execution Profile interface.

Execution analysis

The execution profile provides visualization and data collection for the SQL execution plan. It lets you view the real-time status of operators, duration details, I/O, and runtime data. It also aggregates and sorts the overall execution duration.

Running a profile lets you:

• View the actual OceanBase execution plan in a graph format, which makes it easier to understand the operator execution order and connection relationships.

• Quickly identify performance bottlenecks using the execution overview, real-time I/O statistics, and the top five longest durations. ODC sorts durations by CPU time.

• Check the real-time execution status, time, and number of output rows for each operator. You can click an operator node to view its properties, duration details, I/O, and other runtime data on the right.

• Analyze both single-node and distributed execution plans. For parallel execution operators, you can sort by DB duration, I/O, memory, and the number of output rows to quickly locate data skew.

SQL execution overview

Type	Metric descriptions
SQL execution overview	CPU time: The sum of the CPU time for all operators. I/O wait time: The sum of the I/O wait time for all operators. DB time: The time consumed by the plan execution. Queue time: The waiting time of the request in the queue. Plan type: The type of execution plan. It can be LOCAL, REMOTE, or DISTRIBUTED. Is hit plan cache: Indicates whether the plan cache was hit.
Operator execution overview	CPU time: The total CPU time consumed by the current operator. For a parallel operator, this is the sum of the durations of all subthreads. I/O wait time: The I/O wait time for the current operator, including disk I/O and network time. For a parallel operator, this is the sum of the durations of all subthreads. Parallel: The degree of parallelism. This is the number of threads that run in parallel for the current operator. Skewness: The degree of skew. A higher value indicates a larger difference in the output duration among subthreads. A specific execution node or thread may be slowing down the entire operator. Process name: The thread ID. Use this value to find the IP address and port of the corresponding node. Change time: The output duration for the current thread. This is the time difference between the output of the first row and the last row of data.

I/O statistics

Type	Metric description
Operator I/O statistics	Output rows: The number of output rows. Start times: The number of times the operator is rescanned. For example, the right child node of a Nested loop join operator is scanned multiple times. Max memory: The upper limit of the workarea memory used by the operator at runtime. Max disk: The maximum disk dump space used by the operator at runtime.

Node properties

Node properties consist of the operator's output information and runtime data (Other statistics).

For more information about node output information, see the official OceanBase documentation, such as Properties of the Table Scan operator.

The meaning of runtime data varies among operators. It includes important monitoring metrics. For more information about these metrics, see the V$SQL_MONITOR_STATNAME view.

Execution plan

Unlike the Execution Plan in the SQL window, the execution plan in the Execution Profile shows the actual plan that was executed. It includes the Actual Rows and Actual Cost. ODC retrieves the current plan using the DBMS_XPLAN package.

You can also click Text View in the upper-right corner to switch views. This displays more comprehensive plan information for further SQL tuning.

Tracing Analysis

For OceanBase versions later than 4.2.0, ODC provides visualized information for SQL Tracing Analysis. If you connect through OBProxy, the OBProxy version must also be V4.2.0 or later. For more information, see Overview of OceanBase Tracing Analysis.

ODC provides two visualization views for OceanBase Tracing Analysis data: Trace View and List View.

The Trace view shows the complete timeline of the SQL execution. You can expand and collapse nodes and highlight search results. Hover the mouse over the timeline to view the execution node, start and end times, and detailed data for that node.

Click the Table View icon to switch views. This lets you filter and sort spans by different dimensions.

ODC supports exporting Tracing Analysis data in JSON format. This format is compatible with the OpenTracing protocol, and you can import the data into Jaeger for further analysis.

DB duration

On the Execution History tab, hover the mouse pointer over the tip icon next to DB Duration. The tooltip displays the full-link duration information for the SQL execution. You can use this to verify that the duration of each stage matches the actual duration.

References

• SQL editing and execution

• PL compilation and debugging

• Result editing and export

• Generate test data

• Database objects

• Manage partition plans

• SQL scheduled tasks

• Lock-free schema evolution

• Shadow table synchronization