cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Plan in HANA - what is it

Former Member
0 Kudos

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?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

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.

lbreddemann
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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.