How to determine SQL Plan Cache Size?

[vivekbhoj]

Hi Everyone,

In our HANA production box, we are getting the below alert:

"96292 plan cache evictions occured on host hana, port 30003" and the recommendation HANA is showing is "To increase size of plan cache"

At present the plan cache size is default i.e. 2 GB

I found below three notes related to SQL plan cache

As per SAP Note 2124112 ,  HANA Mini Check with Check ID 1130 stands for SQL cache evictions

SAP Note "1977253 - Determines whether or not the plan cache is too small" seems relevant but is not yet released

I also checked SAP Note "2040002 - Size recommendation for the HANA SQL Plan Cache"

This note says if any of the following conditions is true in your system, please increase the plan cache capacity as outlined below:

1. "If CACHED_PLAN_SIZE is bigger than PLAN_CACHE_CAPACITY, newly compiled plans cannot be cached at all. In this case, you must increase your cache capacity"

-> In our HANA System, PLAN_CACHE_CAPACITY is greater than CACHED_PLAN_SIZE

2. "In an equilibrated system, the PLAN_CACHE_HIT_RATIO is lower than 90% (introduced in SP7)"

-> In our HANA system PLAN_CACHE_HIT_RATIO is lower than 90% for indexserver, so the second condition is satisfied

As per the Note, for Non-ABAP system,

plan_cache_size = 0.90 x (current PLAN_CACHE_CAPACITY)/(current PLAN_CACHE_HIT_RATIO)

For ABAP system,

plan_cache_size = # of SQL connections x 15 MB

We are running HANA in a Sidecar Scenario and also using HANA Accelerators so in a way, we also have a ABAP stack on top of HANA

So how should we determine the plan cache size?

Regards,

Vivek

[lbreddemann · Accepted solution]

A sidecar approach is clearly _not_ equivalent to a full blown ABAP stack on top.

Much less connections, much less different SQL statements executed.

For such a scenario I'd recommend to take a step back and check if the default setting actually gives you any issues. "Issue" in this context would be excessive re-parsing of the same statements over and over again.

Since your system didn't even use up the plan cache capacity, there's no gain in fiddling with this parameter.

So, the recommendation is: don't touch the setting and save operations effort for maintaining a  non-default parameter setting.

- Lars

[vivekbhoj · Accepted solution]

Hi Lars,

Thanks for your reply

We have been getting these alerts regularly and many times they have medium priority

As per SQL Plan Cache Overview query from Note 1969700, I got the below result

HOST:               Host name

PORT:               Port

CONF_GB:            Configured SQL cache size (GB)

USED_GB:            Used SQL cache size (GB)

USED_PCT:           Used compared to configured SQL cache size (%)

ABAP_REC_GB:        SQL cache size recommendation for ABAP environments as per SAP Note 2040002 (GB)

NON_ABAP_REC_GB:    SQL cache size recommendation for ABAP environments as per SAP Note 2040002 (GB)

CACHED_SQLS:        Number of currently cached plans

AVG_SIZE_KB:        Average size of a currently cached plan (KB)

HIT_PCT:            SQL cache hit ratio (%)

EVICT_PER_H:        Number of evictions per hour

TIME_TO_EVICT_H:    Average time between load and eviction (h)

EVICT_OVERHEAD_PCT: Response time overhead due to evictions (%)

CONNECTIONS:        Number of existing connections

As per the result, it says we are using 99.63% of the cache size and it recommends the size as 2.13 GB

As the difference between cache size and recommended cache size at present in not high, should we wait for sometime and see how the system behaves or should we increase the cache size now?

Regards,

Vivek

[lbreddemann · Accepted solution]

Hi Vivek,

first off: the statistics server alerts should always be considered as general hints.

They are based on a fictive average usage pattern for SAP HANA.

This pattern might be very different from the one on your system.

The question here of course is: will the increased sql plan cache size improve the system performance? (Whether or not it will silence the alarm is secondary at best).

To be honest, I doubt that it will.

Depending on the analytical queries and models run in this SAP HANA instance, there might not be many plans that actually could be reused. And non-reusable queries (often those are ad-hoc queries generated by reporting tools) will always take new space in the sql plan cache and eventually remove other plans.

Now, your system partly uses ABAP (as it is used in a side-car scenario) so you might think that the SQL commands from the ABAP stack get removed from the plan cache and need to be reparsed upon the subsequent execution.

This is possible, but rather unlikely, since the NetWeaver database interface will keep the prepared statements "open" (the statements, not the cursors!) which prevents the dependent plans from being evicted.

From this we can assume that very likely only other "one-off" statements (the non-reusable statements) get evicted - which is not a problem at all, since they wouldn't be reused anyhow.

If this little theory turns out to match your reality, then all you want to do is to consider ignoring the alert or changing the threshold.

But first I'd pay another look into how many statements that potentially could be reused are unnecessarily reparsed because of plan eviction.

- Lars

[vivekbhoj · Accepted solution]

Thanks a lot Lars.

I will check which statements are getting reparsed.

Regards,

Vivek

[michaelkil · Accepted solution]

This message was moderated.