Skip to Content

Need automated script to run Clear SQL Plan cache

Oct 23, 2017 at 11:22 AM


avatar image

Hello Gurus,

Here is my detailed issue

1) We observed application team authorization issues in HANA system like below when a SAP HANA node fail over happened. (This happened twice)

[167809]{865096}[226/-1] 2017-09-25 03:59:35.715593 e tempindex : Failed to find temp table. Schema: SYS, Name: _SYS_SS2_TMP_TABLE_30708106__SYS_SS_EMPTY_TABLE_VAR_RESULT_59C4E6A0E763FEFFE1000000AD258008, Host: hana-prod-07, Port: 30091
[167809]{865096}[226/-1] 2017-09-25 03:59:35.716206 e Authorization ProcedureInfoProvider.cpp(00301) : Executing filter procedure failed with error: general error

[167809]{865096}[226/-1] 2017-09-25 03:59:35.716490 e CalcEngine ceAuthorizationCheckImpl.cpp(03246) : AuthorizationCheckHandler::addAPsToSearchObject: Exception was thrown during getting AnalyticalPrivileges for NIKHGUP3 on _SYS_BIC:dise.bookings/BOOKINGS (t -1): general error

2) We then raised an SAP Case about this, and SAP gave below two workarounds

a) This issue is a known issue in HANA 122.02 (Ours is same version) and we need to upgrade our HANA to higher release to solved this issue.. --> This is not possible for us as we recently upgraded all our systems recently, and another upgrade is nearly impossible.

b) We need clear SQL Plan cache manually everytime the HANA node failovers.
When the issue occurred previous times, we did clearing of plan cache and it actually solved this issue.

We have the solution which is to clear SQL Plan cache everytime the HANA Node failover happens.
My customer asked me to write a automated script in HANA system which will clears plan cache everytime a HANA node failover happens.

I am not strong in linux scripting, So I request Gurus here to help me with a good script which detects HANA failover and triggers SQL plan cache soon it detects a Node failover event.

Our System Configuration :
Prod System with 10 Nodes, and 3 tenants and two extra standby nodes.

Request you help us on priority.

Thanks in advance..

Best Regards,


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Pratik Doshi Oct 25, 2017 at 11:35 AM

Hi Anand,

No need of Linux Scripting for these.

You can write SQL Script and you can schedule it with cron job but for these you need to have xs-engine running.

Hope these will help you.

10 |10000 characters needed characters left characters exceeded