A Screen a Story “Old habits die very slowly”

IBM i Access Client Solutions (ACS) is an excellent tool to review IBM i SQL performance. My guess is that most system administrators hardly touch the option “SQL Performance Center” which can be found under the Database section when you start ACS. Below an example of what you see when you do so: 

The screen:

The screenshot is taken from: IBM i ACS => Database => SQL Performance Center => Plan cache

The question we can ask ourselves is why is the “Current Plan Cache Hit Ration: as low as 47 %, while the “Target Plan Cache Autosize Hit Ratio” is set to the default value of 90 %?

When you have to describe the Plan Cache I always use the terms SQL working engine. It may be incorrect but you get the picture. When SQL requests are being processed the Plan Cache is learning what is used frequently and which SQL access Plan need to be kept in memory. Also when it needs to create an index over and over again, it create a Materialized Temporary Index (MTI).

Guess what happens when you IPL your system? Yes, you are correct both the Plan Cache and the MTI are gone. So coming back top the question, why do I see 47 %? This might be because of a recent IPL of the system. In this case the screen shown was taken a few days after the weekly IPL of the system. One of the reasons why the system was IPL’ed weekly was to get all Java Virtual Machines (JVM) restarted. Which is an unofficial good practice according to some IBM i performance experts. An IPL of the system seems an easy way to get this done, but do not throw throw the baby out with the bathwater. So stopping all JVM’s by stopping the application and not IPL’ing the system does prevent the Plan Cache and MTI’s staying in place and in giving you a better performance. IPL only when you have to, for a system administrator that means when you update the system and apply PTF’s.