SHOW PLAN

Syntax

SHOW PLAN [JSON] plan_id

Remarks

• SHOW PLAN displays the same information as EXPLAIN. You can use the SHOW PLAN command as an alternative to the EXPLAIN command, if your database client does not support EXPLAIN.

• By default, the SHOW PLAN command displays the EXPLAIN information of a query in text format. You can optionally choose to display the information in JSON format using the SHOW PLAN JSON command.

• You can get the plan_id of a query from the INFORMATION_SCHEMA.PLANCACHE view. See Example for details.

• The SHOW PLAN command throws the following error if the global variable enable_disk_plan_explain is not enabled:

  Copy

  ERROR 2394 (HY000): The plan does not have associated explain information. enable_disk_plan_explain must be enabled at the time of plan generation.

  In this scenario, you need to do the following:

  1. Run the following command to drop the plan from the plancache:

     SQL

     Copy

     DROP 25 FROM PLANCACHE;

  2. Enable the global variable:

     SQL

     Copy

     SET GLOBAL enable_disk_plan_explain=ON;

  3. Rerun the query to regenerate the plan, and query the plancache again to obtain the new plan_id.

  Note: Every time a query is run, a new plan is generated along with the plan_id.

Example

The following example displays the plan statistics for the following SELECT statement:

SELECT * FROM Employee;
+------+------+
| ID   | Name |
+------+------+
|   10 | Jack |
|   30 | Ritz |
|   20 | Rob  |
|   40 | Rick |
+------+------+

Run the following command to view the plan ID of the SELECT query:

SELECT QUERY_TEXT,PLAN_ID FROM INFORMATION_SCHEMA.PLANCACHE WHERE QUERY_TEXT LIKE 'SELECT * FROM Employee';

+---------+------------------------+
| PLAN_ID | QUERY_TEXT             |
+---------+------------------------+
|      25 | SELECT * FROM Employee |
+---------+------------------------+

Note: In the SELECT statement, you can omit the WHERE clause to view the plan ID of all the queries executed in the current session.

Now run the following command to view the statistics of the plan ID 25:

SHOW PLAN 25;

+---------------------------------------------------+
| PLAN                                              |
+---------------------------------------------------+
| Gather partitions:all alias:remote_0              |
| Project [Employee.ID, Employee.Name]              |
| TableScan db.Employee table_type:sharded_rowstore |
+---------------------------------------------------+