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

You need to have the database account and password for the current tenant to log in to the SQL Console.

Execution plan

1. Log in to the SQL Console and 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. Select the SQL statement and click the execution plan icon . This provides a system estimation of the data to be executed before executing the SQL statement (result of the EXPLAIN PLAN operation), which may slightly differ from the actual executed data after executing the statement.

   

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 Record tab, place the mouse cursor over the prompt icon next to DB Execution Time to view the time-consuming information for SQL execution. This allows you to verify whether the time consumption at each stage matches the actual time consumption.

More information

• Access SQL Console

• SQL editing and execution

• PL compilation

• Result set editing

• Session variable management

• Functional keys in SQL Console