Execution analysis

The SQL window provides a user interface for handling data. In the SQL window, you can perform a systematic evaluation of SQL statements and view detailed execution information along with time-consuming details from end to end.

This topic uses the table employee as an example to demonstrate how to perform a systematic evaluation of query SQL statements.

Prerequisites

• A super account has been created in the OceanBase Cloud console for connecting to the analytical database.

• You have permission to manage accounts under the analytical cluster instance.

• You have created or joined a workspace.

Execution plan

1. Log in to the OceanBase Cloud console and enter the workspace. Edit the SQL statement in the SQL window 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 image above, select the SQL statement and click the execution plan icon . This allows you to estimate the execution data before running the SQL statement (EXPLAINPLAN). Note that the estimated execution data may differ slightly from the actual results after execution.

   

3. In the Plan Details page, click View Formatting Info to switch to the formatted display page.

Execution details

Accessing the execution profile

• Option 1: While the SQL statement is executing in the SQL window, click View Execution Profile in the Logs tab.

  Notice

  If the SQL statement execution time is less than one second, the option to view the execution profile may not be available in the Logs tab.

• Option 2: After the SQL statement has executed, click the Execution Profile icon in the results.

• Option 3: After the SQL statement has executed, click the TRACE ID in the Execution Records tab to access the execution profile.

Execution analysis

The Execution Details, or Execution Profile, provides a visual representation and data collection for SQL execution plans. It allows real-time monitoring of operator execution status, time details, I/O, and runtime data, while summarizing and ranking the overall execution time.

Using the execution profile, you can:

• View the actual execution plan of OceanBase in a graphical format, making it easier to understand the execution sequence and relationships of operators.

• Quickly identify execution bottlenecks using execution overviews, real-time I/O statistics, and the top 5 time-consuming operations. SQL window sorts these based on CPU time.

• Monitor the execution status, timing, and output rows of operators in real-time. By clicking on an operator node, you can access operator attributes, time details, and I/O and other runtime data on the right side of the screen.

  

• Analyze standalone and distributed execution plans. Parallel operators can be sorted by DB time, I/O memory, and output rows to quickly locate data skew.

SQL execution overview

Type	Description
SQL Execution Overview	CPU time: Total CPU time for all operators. I/O wait time: Total I/O wait time for all operators. DB time: Total time taken to execute the plan. Queue time: Time spent waiting in the queue. Plan type: Type of execution plan, which could be Local (LOCAL), Remote (REMOTE), or Distributed (DISTRIBUTED). Is hit plan cache: Indicates whether the plan cache was hit.
Operator Execution Overview	CPU time: Total CPU time consumed by the current operator. For parallel operators, this is the aggregate time for all threads. I/O wait time: I/O wait time for the current operator, including disk I/O and network wait time. For parallel operators, this is the aggregate time for all threads. Parallel: The degree of parallelism, indicating the number of threads running in parallel for the current operator. Skewness: Skewness indicates the disparity in output time among parallel threads. A higher value suggests that one node or thread may be slowing down the entire operator. Process name: Thread number, which can be used to locate the corresponding node's IP and port. Change time: The time taken by the current thread to output rows, calculated as the time difference between the first and last row output.

I/O statistics

Node attributes

Node attributes consist of the output information and runtime data (other statistics) of an operator.

For more details on node output information, refer to the official OceanBase documentation, for example, Table Scan operator's attributes.

Runtime data vary across different operators and represent important monitoring metrics. For more information on these metrics, refer to the V$SQL_MONITOR_STATNAME view.

Execution plan

Unlike the Execution Plan in the SQL window, the Execution Profile displays the actual execution plan, including Actual Rows and Actual Costs. SQL window retrieves the current plan using the DBMS_XPLAN package.

You can also click Text View in the top right corner to switch views and access more detailed plan information, facilitating further SQL optimization.

End-to-end trace diagnostics

For OceanBase versions above 4.2.0 (with OBProxy version 4.2.0 or later if connected via OBProxy), SQL window supports viewing the information on end-to-end trace diagnostics for SQL. For more details, refer to OceanBase End-to-end Tracing Overview.

SQL window offers both Trace View and Table View visualizations for OceanBase's end-to-end trace diagnostic data.

The Trace View shows the overall SQL execution timeline, with support for node expansion and collapse, as well as search highlighting. Hovering over the timeline allows you to view the execution node, start and end times, and detailed data.

Clicking Table View allows you to switch views, enabling filtering and sorting of spans across various dimensions.

SQL window also supports exporting end-to-end trace diagnostic data in JSON format, compatible with the OpenTracing protocol. You can import this data into Jaeger for further analysis.

DB time

In the Execution Records tab, hover over the tooltip icon next to DB Time to view end-to-end timing information for SQL execution. This helps verify if the time spent in each stage matches the actual execution.

More information

• Overview

• SQL editing and execution

• Result set editing

• Session variable management