View the performance of an SQL statement

This topic describes how to view the performance of an SQL statement in OceanBase Developer Center (ODC).

Background information

SQL is a database language that provides multiple features such as data manipulation and data definition. Most database developers manage and develop database content by using SQL statements. Therefore, the performance of SQL statements affects the performance of the database. SQL provides the EXPLAIN command, which allows you to view the specific steps for executing an SQL statement in the database. You can judge and improve the performance of the SQL statement based on the execution steps.

As a powerful enterprise-level database development platform, ODC provides a graphical user interface (GUI) for you to view the execution plans of SQL statements. ODC allows you to directly and conveniently view the execution status and analyze the performance of SQL statements.

Prerequisites

A sample table named "employees" has been created by running the following statement:

CREATE TABLE employees(
    emp_id INTEGER,
    emp_name VARCHAR(20),
    manager_id INTEGER
);

INSERT INTO employees VALUES ( 1, 'Anna', 6 ) ;
INSERT INTO employees VALUES ( 2, 'Brain', 4 ) ;
INSERT INTO employees VALUES ( 3, 'Candy', 4 ) ;
INSERT INTO employees VALUES ( 4, 'David', 5 ) ;
INSERT INTO employees VALUES ( 5, 'Eva', NULL) ;
INSERT INTO employees VALUES ( 6, 'Frank', 5 ) ;
INSERT INTO employees VALUES ( 7, 'Gary', 6 ) ;

Procedure

1. After you enter the connection, enter the following SELECT statement in the editing area of the SQL window to view the data in the table named "employees":

   SELECT * FROM employees;
   

2. Click Plan in the toolbar in the editing area.

   Before you run the statements, you can click Plan to view the estimated execution plan of the SQL statement selected or where the cursor is located in the current editing area. The estimated execution plan is actually the result of the EXPLAIN PLAN operation. The estimated plan may be slightly different from the actual plan, but you can assess the SQL statement in advance.

3. In the Plan Details panel, view the estimated execution plan before you execute the SQL statement.

   The Planned Statistics Collection tab in the Plan Details panel displays following information:

4. Run the statement.

   Click Run in the toolbar to execute the SELECT statement.

5. Click Plan in the toolbar of the result set, to view the execution plan after the statement is executed.

   The Execution Details page displays the actual execution plan.

6. View the basic information of the statement in the Basic Info section of the Execution Details panel.

   The Basic Info section displays the following information:

7. View the time statistics of the statement in the Time Spent section of the Execution Details panel.

   The Time Spent section displays the following information and corresponding proportions in a bar graph.

8. View the I/O statistics of the statement in the I/O Statistics section of the Execution Details panel.

   The I/O Statistics section displays the following information:

9. View the plan statistics of the statement on the Planned Statistics Collection tab of the Execution Details panel.

   The Planned Statistics Collection tab displays the actual execution plan in a structured manner. This tab is the same as the Plan Details tab in structure. For more information about this tab, see the information about the Planned Statistics Collection tab in Step 3.

10. View the outline of the statement in the Outline section of the Execution Details panel.

    The Outline section displays the corresponding content of OUTLINE DATA in results returned by the execution plan. The content in this section is a set of hints generated by the optimizer for the purpose of fully reproduce a plan.