Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SQL Plan cache never cleaned

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 statistics service, 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

Tags:
Former Member
Former Member replied

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

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question