## 3.14.2.1  SQL Plan Cache Analysis
The SAP HANA SQL plan cache can be evaluated in detail for a particular statement hash.
Various options are available for analyzing the plan cache for a statement. In SAP HANA cockpit the link for SQL Plan Cache is available in the Monitoring tile. The two system views associated with the SQL plan cache are m_SQL_PLAN_CACHE_OVERVIEW and m_SQL_PLAN_CACHE.
Alternatively, SQL statements for this are available in SAP Note 1969700 - SQL Statement Collection for SAP HANA . Y ou can use the 'HANA_SQL_StatementHash_KeyFigures' script to check for the SQL plan cache details of a specific SQL statement (the related STATEMENT_HASH has to be maintained as input parameter).
SQL PLAN Cache Example Output
| STAT_NAME                 | [VALVE                                  | IVALUE_PER_EXEC | MAX_VAL_PER_EXECIVALUE_PER_ROW[   |
|---------------------------|-----------------------------------------|-----------------------------------------------------|
| Statement Hash Table type | ICOLUMN 2014/03/09 02-58-49             |                                                     |
| Preparation timestamp     |                                         |                                                     |
| Last                      | execution timestamp|2014/03/09 22-59-44 |                                                     |
| Last connection ID        | 1710962 102920                          |                                                     |
| Executions                |                                         |                                                     |
| Records                   | 5107486                                 | 49. 62                                              |
| Preparations              | 2                                       | 0.00                                                |
| [Metadata cache Misses    |                                         |                                                     |
| Cursor duration           | 3.93 h|                                 | 137 67 Ms 997.22 Ms 2-77 Ms                         |
| Execution time            | 3.75 h|                                 | 131 47 Ms 772.86 Ms 2.64 Ms                         |
| Table time                | 0.00 h                                  | Ms 2.02 Ms 0.00 Ms                                  |
| [Preparation time         | 00 h]                                   | 0 Ms 159.46 Ms Ms                                   |
| [Open time                | 0.00 h                                  | 0 10 Ms 1 .64 Ms 0.00 Ms                            |
|                           | 3.75 h]                                 | 131 35 Ms 772.68 Ms 2.64 Ms                         |
| [close time               | 0 00 h]                                 | 0 Ms 0.54 Ms 0.00 Ms                                |
| Lock                      |                                         | 0 Ms 0                                              |
|                           |                                         | 00 Ms                                               |
The historic execution details for a particular SQL statement can be determined with the SQL statement: 'HANA_SQL_SQLCache' . Also here the appropriate STATEMENT_HASH has to be specified as input parameter.
## Statement Hash Example output
Based on the results of this evaluation you can distinguish the following situations:
- If the value for Executions is unexpectedly high, further analysis should be done on the application side in order to check if it is possible to reduce the number of executions.
- If the value for Records is unexpectedly high, further analysis should be done on the application side in order to check if it is possible to reduce the number of selected records.
- If the value for Cursor duration is very high and at the same time significantly higher than the value for Execution time , you have to check which processing steps are executed on the application side between the individual fetches. A high value for Cursor duration can negatively impact the database in general because open changes may impact the MVCC mechanism.
- If the value for Preparation time is responsible for a significant part of the Execution time value you have to focus on optimizing the parsing (for example, sufficient SQL plan cache size, reuse of already parsed SQL statements).
- If Execution time is much higher than expected (that can be based on the statement complexity and the number of processed rows), the SQL statement has to be checked more in detail on technical layer to understand the reasons for the high runtime. See section Query Plan Analysis for more information.
## Related Information
SAP Note 1969700 Query Plan Analysis [page 248] Analyzing SQL Execution with the SQL Plan Cache [page 247]