SQL Plan Cache Size

[michaelkil]

Hello togehter,

arround one week, i have increase the plan_cache_size (indexserver.ini/sql) from default to a custom Value (almost doubled).

Now, one Week later the CACHED_PLAN_SIZE_GB is equal the PLAN_CACHE_CAPACITY_GB.

Wow

In the following Discussion (http://scn.sap.com/thread/3713659) from last year, Lars Breddemann describes very accurately the Function from the SQL Plan Cache, but i ask me furthermore what happend here.

I'm not really sure, if i should increase the SQL Plan Cache again or not.

If only non-reusable queries take new space, then it make no sense, then i could increase the SQL Plan Cache again and again, increase the threshold so that no alerts are generated or configure the non-reusable queries to take no space?

Really?

...but what if there reusable queries take that space?

How do i know this and how can i check, which Statements are getting reparsed?

Which Statements are in the Cache?

What are usable and what are non-usable Statements?

Will improve the Cache the Performance optimization?

Which Statements are getting reparsed and how?

Regards,

Michael

[lucas_oliveira · Accepted solution]

Hi Michael,

No need to increase your SQL plan cache whenever you see the cached size reaching the plan size. Evictions will take place and clear the cache so new queries can jump in. Normal behavior here.

Now, when that happens frequently it could be a problem indeed but unlikely to cause real harm to performance.This should be tracked by Alert 58 ('X number of cache evictions in the last hour'). However, just because you see that alert, does not mean you have a problem.

I believe this should be a real problem when the front-end tools and users are negatively impacted.

A couple of scenarios I can think of that can affect the cache in that sense are: (1) large number of queries (mostly lack of prepared statements); (2) queries with large size in the cache.

Both scenarios can be checked with the suggestions of SAP Note 2124112 . In fact, that note will walk you through most of your questions.

Now for your questions:

Which Statements are in the Cache?

Whatever is on m_sql_plan_cache.

What are usable and what are non-usable Statements?

In essence they're all reusable. Although prepared statements are definitely made for reuse, efficiency and security.

For instance you can run statement "SELECT COL1, COL2 from MYTAB where COL3 = 'avalue'"  many times reusing what was prepared in the cache, but as soon as you change the conditional to "SELECT COL1, COL2 from MYTAB where COL3 = 'another_value'", then you'll have another entry compiled on your cache. Change that again to yet another value: another entry in the cache..

That's one of the reasons why using prepared statements is generally a best practice: as soon as you use a prep statement like "SELECT COL1, COL2 from MYTAB where COL3 = ?" you'll hold only one entry on your cache for that query.

Will improve the Cache the Performance optimization?

   There should not be that big difference unless you have an outrageously bad sized cache an something that demands lots CPU for parsing (dependent on the query of course). Otherwise, the performance drawback is generally negligible.

Which Statements are getting reparsed and how?

Check the Note and play around with the queries from 1969700.

I hope that helps.

BRs,

Lucas de Oliveira