SQL Plan cache never cleaned

[Former Member]

Hi,

last two weeks I've been fighting with Out of Memory troubles with SAP Hana SPS10. Today I noticed a huge amount of data in SQL Plan cache...I run query

 ALTER SYSTEM CLEAR SQL PLAN CACHE 

and got 20 GB free RAM...wow!

Then I started with investigation why the cache is growing...even though I set up retention days to only 1 day ( see 1929538 - HANA Statistics Server - Out of Memory)

Based on note 2038937 - SAP HANA: Reduce size of historical data size in standalone statistics server and embedded..., there is a configuration in statisticsserver.ini which includes sql for SQL Plan cache deletion.

Name: delete_old_data_host_sql_plan_cache

Value: delete from _sys_statistics.host_sql_plan_cache where server_timestamp < add_days(CURRENT_TIMESTAMP,-1)

When I try to execute this sql delete manually, it fails on error:

Could not execute 'delete from _sys_statistics.host_sql_plan_cache where server_timestamp < ...'

SAP DBTech JDBC: [361]: data manipulation operation not legal on this view

The delete sql tries to delete data from View!   It will never delete any data from SQL Plan cache! It looks like a system bug...but I'm wondering why I'm the only one who noticed that issue. I found the same problem also on SPS9....

Martin

[Former Member · Accepted solution]

It looks like you mix up the current SQL cache (M_SQL_PLAN_CACHE) you are "suffering" from and the statistic server histories (HOST_SQL_PLAN_CACHE). The error you get with the DELETE is caused by the fact that you already use the embedded statistics server. But it isn't relevant, anyway.

Instead you should be able to analyze and tune the SQL cache based on SAP Note 2044468.

[Former Member · Accepted solution]

Hi Martin!

ouch, I see...my mistake. Thanks for your answer! 

the naming is really terrible and misleading 😞

If there is no issue with deleting old entries in SQL Plan Cache, then it means my SQL Plan Cache is growing very fast...if I'm not mistaken, I have only two possibilities:

1. increase the sql plan cache size
2. somehow tune up the sql cache to not store everything into cache....

I just wonder why you recommend me to tune sql plan cache based on sap note 2044468 - which explains "hana partitioning"....how is hana partitioning related to sql plan cache?

Martin

[Former Member · Accepted solution]

This time it's my mistake: I should type "parsing" when I search for a SAP Note and not "partitioning". The correct SAP Note number is 2124112. Usually there is an issue like no bind variables or long in lists. Further increases of the cache are often not useful.

[Former Member · Accepted solution]

Hi Martin,

just want to inform you, we have a resolution for my troubles....It's a known issue in rev101 and will be fixed in rev102.

Memory areas for Pool/Statistics show a huge amount of used memory. According to support, Pool/Statistics and Pool/SqlScript/Execution grows up with rev101 and the memory is released after restart.

Martin