## 4.3.1  Analyzing SQL Execution with the Plan Explanation
You can generate a plan explanation for any SQL statement and use this to evaluate the execution plan; you may be able to use this information to optimize the query by reducing the run time or the memory consumption.
The Explain Plan can be collected in several different ways:
- By running the EXPLAIN PLAN SQL statement
- In the SQL console of SAP HANA studio
- From within DBACOCKPIT.
## Running EXPLAIN PLAN from the SQL Command Line
The SQL command offers two options: to capture the results directly from the query or capture from an existing entry in the plan cache. The results of these two options may vary because in the first case Explain Plan is based on the prepared SQL statement not on the executed statement. If, on the other hand, you run Explain Plan for a statement which has been executed and is available in the cache, then additional parameter aware optimization will already have been applied. See also SAP Note 2410208 Collect Explain Plan of a Prepared Statement .
After running Explain Plan, the plan results are saved in the EXPLAIN_PLAN_TABLE. An additional option available is to give the plan details a recognizable name (STATEMENT_NAME value) so that your results are easily identifiable, see the following example.
The SQL syntax for the two options is as follows:
```
 Code Syntax EXPLAIN PLAN [SET STATEMENT_NAME = *<statement_name>*] FOR SELECT *<subquery>* EXPLAIN PLAN [SET STATEMENT_NAME = *<statement_name>*] FOR SQL PLAN CACHE ENTRY *<sql_plan_id>*
```
&lt;statement_name>=
&lt;subquery>=
String literal. Used to identify the name of a specific execution plan in the output table for a given SQL statement. It is set to NULL if the STATEMENT_NAME is not specified. An SQL statement.
## &lt;sql_plan_id>=
## An SQL plan id (in sql plan cache).
The following illustration shows the usage of the two options and the statement name value:
```
explain plan MyStatemento1 for select from job_progress; explain plan for SQL PLAN CACHE ENTRY 16370003; select from EXPLAIN_PLAN_TABLE;
```
|    | STATEMENT_NAME   | OPERATOR_NAME      | OPERATOR_DETAILS                           | OPERATOR_PROPERTIES    | EXECUTION_ENGINE   |
|----|------------------|--------------------|--------------------------------------------|------------------------|--------------------|
|    | MyStatement01    | ROW SEARCH         | M_JOB_PROGRESS HOST, M_JOB_PROGRESS PORT,  | ATTACHED HINT LIST (NO | ROW                |
|    | MyStatement01    | MATERIALIZED UNION |                                            |                        | ROW                |
|    | MyStatement01    | MATERIALIZED UNIO  | (M_JOB_PROGRESS_ SCHEMA_NAME M_JOB_PRO..   |                        | ROW                |
|    | MyStatement01    | MONITOR SCAN       |                                            |                        | ROW                |
|    | MyStatement01    | MATERIALIZE        | M_JOB_PROGRESS_SCHEMA_NAME M_JOB_PROG      | PARALLELIZED           | ROW                |
|    | MyStatement01    | MONITOR SCAN       |                                            |                        | ROW                |
|    | MyStatement01    | AGGREGATION        | GROUPING: M_DEV_CS_DDL_STATISTICS HOST M_  |                        | ROW                |
|    | MyStatement01    | MATERIALIZED UNIO. | (M_DEV_CS_DDL_STATISTICS OPERATION; M_DEV  |                        | ROW                |
|    | MyStatement01    | MONITOR SEARCH     |                                            |                        | ROW                |
| 10 | MyStatement01    | MATERIALIZE        | M_DEV_CS_DDL_STATISTICS OPERATION M_DEV_C. | PARALLELIZED           | ROW                |
| 11 | MyStatement01    | MONITOR SEARCH     | SEARCH CONDITION: (M_DEV_CS_DDL_STATISTICS |                        | ROW                |
| 12 | MyStatement02    | ROW SEARCH         | M_DATA_VOLUME_SUPERBLOCK_STATISTICS.VOLU . | PRECOMPILED, ATTACHED  | ROW                |
| 13 | MyStatement02    | MONITOR SEARCH     | SEARCH CONDITION: (M_DATA_VOLUME_SUPERBL   |                        | ROW                |
## Explain Plan in SAP HANA Studio
Enter a statement in the SQL console of SAP HANA studio and before executing the command choose Explain Plan in the context menu. You can enter multiple statements separated by a semicolon to generate several plan explanations at once.
The plan explanation is then displayed on the Result tab and stored in the EXPLAIN_PLAN_TABLE view of the SYS schema for later examination.
## Explain Plan in DBACOCKPIT
## How to use the Explain Plan to optimize a query:
The execution plan shows important detailed information on the query execution and the background operations. Some of the key values are described briefly here and in the examples which follow, refer to the EXPLAIN_PLAN_TABLE system view in the SAP HANA SQL and System Views Reference for full details.
| Area              | Detail                                                                                                                                                                                                                                                             |
|-------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Operation details | The OPERATOR_NAME value shows the type of operation which was executed, such as joins, unions, aggregations and so on. Operations depend on the engine used - essentially row engine or column engine. Dependencies are shown by indentation - see examples below. |
| Engine            | The type of engine where an operator is executed is shown in the EXECUTION_ENGINE column: ROW, COLUMN, OLAP, HEX, ESX.                                                                                                                                             |
| Table details     | Table details include table name, type, size, tables or objects which were accessed.                                                                                                                                                                               |
| Estimated cost    | Cost values include the estimated output row count (OUTPUT_SIZE) and the estimated time in seconds (SUBTREE_COST).                                                                                                                                                 |
## Example 1
The following illustrations and commentary show how the information returned can be used.
We can see the OUTPUT_SIZE and SUBTREE_COST estimations for the various operations. The cost value is used for the cost-based optimizer to choose the best plan; generally, the smaller the subtree cost, the better the performance.
We can see that for the operations 1-13 the ROW store engine is used and that the operations 1-5 are executed sequentially (OPERATOR_NAME values indented incrementally) and that operations 6-11 are executed in parallel (left-aligned together):
Possible Optimizations
In this example, it may be possible to adjust the query so that more steps can be executed in parallel, or it may be possible to change the query operations so that the column store engine is used instead of the row store engine if a similar operator exists for the column store engine. You can use query hints to influence how a query is executed, for example, the hint use_olap_plan will force the HANA database to use the OLAP engine instead of the join engine where this is technically possible. The available hints for the SAP HANA database are described in the SAP HANA SQL and System Views Reference .
Refer also to the knowledge base article 2142945 FAQ: SAP HANA Hints .
## Example 2
In the following example lines we can see the execution engine switching between column and row store:
Where possible an engine switch should be avoided as it requires a materialization of intermediate results and this is expensive in terms of both query performance and memory usage on HANA. It may be possible therefore to optimize this query by adjusting it to avoid the engine switch.
For more information about optimization possibilities refer to:
- The examples given in the SAP HANA SQL and System Views Reference
- Knowledge base article 2000002 FAQ: SAP HANA SQL Optimization .
## Related Information
```
SAP Note 2142945 SAP Note 2410208 SAP Note 2000002
```