PowerWire.eu

Independent IBM i, AIX and Linux news and tech tips for Europe and beyond

  • Home
  • News
  • Technical Articles
    • IBM i
    • AIX
    • Linux
    • VIOS
  • Subscribe
  • About Us
  • Contact Us
  • Advertise with PowerWire.eu

A Screen a Story “Old habits die very slowly”

December 3, 2019 by Rudi van Helvoirt Leave a Comment

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.

Related Posts

  • ARCAD for Compliance. Solution White Paper.ARCAD for Compliance. Solution White Paper.
  • Masterclass: Creating an Etherchannel adapter for resiliency with VIO ServerMasterclass: Creating an Etherchannel adapter for resiliency with VIO Server
  • Rumours abound about IBM’s processor sell-off Rumours abound about IBM’s processor sell-off
  • Backup the backup of your backupBackup the backup of your backup
  • I, for one, welcome our new Watson overlordsI, for one, welcome our new Watson overlords
  • Exclusive preview: How Windows 10 works with Power iExclusive preview: How Windows 10 works with Power i

Filed Under: IBM i, Technical Articles

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Free monthly newsletter signup

News

Remain Software Milestone 3

NIEUWEGEIN, Netherlands — Today Remain Software is pleased to announce the third Milestone of TD/OMS V14 and … [Read More...]

PowerWire.eu talk to i-UG’s chairman Mike Ryan about their upcoming annual conference

Hello Mike. As the official representative of COMMON Europe in the U.K, i- UG has always listened to the … [Read More...]

Dot Files on IBM i

For those who have been following my bite size BASH tutorial on You Tube, I thought it about time I wrote an … [Read More...]

More articles from this section

Quick Links

  • Advertise
  • Subscribe

Follow Us…

  • Email
  • LinkedIn
  • Twitter

Search This Website

Copyright © 2022 · Cue Communications · All Rights Reserved

Log in

This site uses cookies More info