Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Run Plans

Oracle Usage

Run plans represent the choices made by the query optimizer for accessing database data. The query optimizer generates run plans for SELECT, INSERT, UPDATE, and DELETE statements. Users and database administrators can view run plans for specific queries and DML operations.

Run plans are especially useful for performance tuning of queries. For example, determining if new indexes should be created. Run plans can be affected by data volumes, data statistics, and instance parameters such as global or session-level parameters.

Run plans are displayed as a structured tree with the following information:

Oracle 19 introduces SQL Quarantine: now queries that consume resources excessively can be automatically quarantined and prevented from running. These queries run plans are also quarantined.

Examples

Review the potential run plan for a query using the EXPLAIN PLAN statement.

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME FROM EMPLOYEES
WHERE LAST_NAME='King' AND FIRST_NAME='Steven';
Run Plan

Plan hash value: 2077747057
| Id | Operation                   | Name        | Rows | Bytes | Cost (%CPU) | Time
| 0  | SELECT STATEMENT            |             | 1    | 16    | 2 (0)       | 00:00:01
| 1  | TABLE ACCESS BY INDEX ROWID | EMPLOYEES   | 1    | 16    | 2 (0)       | 00:00:01
|* 2 | INDEX RANGE SCAN            | EMP_NAME_IX | 1    |       | 1 (0)       | 00:00:01

Predicate Information (identified by operation id):
2 - access("LAST_NAME"='King' AND "FIRST_NAME"='Steven')

SET AUTOTRACE TRACEONLY EXPLAIN instructs SQL*PLUS to show the run plan without actually running the query itself.

The EMPLOYEES table contains indexes for both the LAST_NAME and FIRST_NAME columns. Step 2 of the run plan above indicates the optimizer is performing an INDEX RANGE SCAN to retrieve the filtered employee name.

View a different run plan displaying a FULL TABLE SCAN.

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME FROM EMPLOYEES
WHERE SALARY > 10000;
Run Plan

Plan hash value: 1445457117
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU) | Time
| 0  | SELECT STATEMENT  |           | 72   | 1368  | 3 (0)       | 00:00:01
|* 1 | TABLE ACCESS FULL | EMPLOYEES | 72   | 1368  | 3 (0)       | 00:00:01

Predicate Information (identified by operation id):
1 - filter("SALARY">10000)

For more information, see Explaining and Displaying Execution Plans in the Oracle documentation.

MySQL Usage

Aurora MySQL provides the EXPLAIN/DESCRIBE statement—used with the SELECT, DELETE, INSERT, REPLACE, and UPDATE statements—to display run plans.

When you use EXPLAIN with a statement, MySQL returns the run plan generated by the query optimizer. MySQL explains how the statement will be processed including information about table joins and order.

When you use EXPLAIN with the FOR CONNECTION option, it returns the run plan for the statement running in the named connection. You can use the FORMAT option to select either a TRADITIONAL tabular format or JSON.

The EXPLAIN statement requires SELECT permissions for all tables and views accessed by the query directly or indirectly. For views, EXPLAIN requires the SHOW VIEW permission.

EXPLAIN can be extremely valuable for improving query performance when used to find missing indexes. You can also use EXPLAIN to determine if the optimizer joins tables in an optimal order.

MySQL Workbench includes an easy to read visual explain feature similar to Oracle Execution Manager (OEM) graphical run plans.

Syntax

The following example shows the simplified syntax for the EXPLAIN statement.

{EXPLAIN | DESCRIBE | DESC} [EXTENDED | FORMAT = TRADITIONAL | JSON]
[SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE
statement | FOR CONNECTION <connection id>]

Examples

View the run plan for a statement.

CREATE TABLE Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    INDEX USING BTREE(Name));

EXPLAIN SELECT * FROM Employees WHERE Name = 'Jason';

For the preceding example, the result looks as shown following.

id  select_type  table      partitions  type  possible_keys  key   key_len ref  rows   Extra
1   SIMPLE       Employees              ref   Name           Name  102          const  1

The following image demonstrates the MySQL Workbench graphical run plan.

For more information, see EXPLAIN Statement in the MySQL documentation.