on 01-31-2015 8:32 AM
Dear SAP HANA experts, and HANA Gurues!
SQL Plan Cache
2040002 - Size recommendation for the HANA SQL Plan Cache
Current default plan cache size is 2GB. However, it should be adjusted based on system's characteristic or workload.
plan cache size is 2GB!!!
I didn't find any detailed information or SAP Notes or blogs about:
What does it mean SQL Plan in HANA? What is it at low level and how it works?
What's information stores in sql plan cache? Is it only metadata, or mixed (metadata and data).
Maybe you know?
The SQL plan cache contains load key figures, information about the parsed statement and some details about the actual execution plan. Unfortunately the execution plan details aren't exposed in the related view M_SQL_PLAN_CACHE, so they are only available internally.
In order to judge if the SQL cache should be resized or if some other problem exists, I would recommend you to have a look at SAP Note 2124112.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The SQL plan cache is used to keep already parsed query statements (the statements, not the data that the statements work on!) in memory and available for quick re-use.
That's a technique that every major and most nice DB products employ and the way this works is pretty similar to all of them.
For SAP HANA you can find information e.g. here
The SAP notes recommendation basically is about optimizing the size of the memory area available for SQL statement re-use.
As different application types have different degrees of re-use for single statements (e.g. transaction oriented applications often repeat the same queries over and over while analytical applications tend to have a higher share of unique queries) different recommendations can be made to get more overall performance from a SAP HANA system.
- Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
2GB of memory for storing only already parsed query statements?
Statements or execution plans?
Technically, the plan cache stores compiled execution plans of SQL statements for reuse, which gives a performance advantage over recompilation at each invocation. For monitoring reasons, the plan cache keeps statistics about each plan, for instance number of executions, min/max/total/average runtime, and lock/wait statistics.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.