SQL Plan cache never cleaned
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 statistics service, there is a configuration in statisticsserver.ini which includes sql for SQL Plan cache deletion.
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: : 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 Damovsky replied
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.