Can I change PLAN_SHARING_TYPE ?

[Former Member]

Hi,

We are having a system where the front end is Cognos and HANA is backend. We use jdbc connection for the communication. Now we see performance problems with some reports. I had a few questions:

1. I managed to get the queries hitting HANA from the M_SAL_PLAN_CACHE. I see for some queries the PLAN_SHARING_TYPE is 'SESSION_EXCLUSIVE_GLOBAL', which means multiple connections cannot use the plan at the same time. There is a bit of latency.
   • Why is that some queries are set to this plan share type?
   • Is there a way I can control it, like set it to GLOBAL at database level or for individual queries?
2. Is there an easier way how I can collect the queries hitting the HANA side from Cognos? SQL plan cache does not even give the entire query, it sometimes chops off the end of query.

Thanks And Regards,

Aswath

[Former Member · Accepted solution]

See SAP Note 2124112 -> "Which problems and solutions exist in the area of parsing?" -> "Multiple SQL cache entires referring to the same SQL statements" for more information related to the plan sharing type.

You can use SQL: "HANA_SQL_SQLCache" (SAP Note 1969700) to list top SQL statements based on various criteria.

In the same statement collection you find SQL: "HANA_SQL_StatementHash_SQLText" which allows to you display the complete SQL text for a particular statement hash.

See SAP Note 2000002 for more information related to SQL statement analysis.

[lbreddemann]

The plan sharing type is determined by things like current privileges, current user, current session context. There is nothing you can actually do for changing the compatibility of a plan for sharing in other contexts.

If this is actually the core performance problem for you, I recommend working with SAP HANA development on that.