In today’s world it is impossible to limit yourself to the green screen when we are talking about IBM i performance and SQL performance in particular. For some shops the IBM i database has become the spider in the database “web”. I assume we have all heard about the Plan Cache and the Index Advisor being build into to IBM i, so this article is an attempt to get you started by taking you by the hand. Hoping that afterwards you have a better understanding of why looking at the Plan Cache when experiencing SQL performance problems is not such a bad idea.
So please have a look at the image below, where you see a SQL statement in Visual Explain using the option “Explain”:
Every action which needs to be performed in order to get the result data has an icon. All those icons are documented in the manual IBM i 7.4 Database Performance and Query Optimization. On page 15 of that document you will find the table shown below:
When you look at this table, please note the values QQRID 3000 – Table Scan for the “Database Monitor and Plan Cache record indicating use”. So when looking at the Plan Cache we should be able to find this information in there. In order to be able to query a Plan Cache, we need to go to IBM i Access Client Solutions (ACS) again and select the option “SQL Performance Center”, select the option “Plan Cache Snapshots” and select the option as show below:
In the window where I can enter the name for the snapshot I enter the value “POWERWIRE” and for schema “QRPLOBJ”. As this library is cleared by every IPL I do not need to worry about cleaning up myself. Then I click “OK” without touching the other parameters and after a while:
Please notice the difference between the Plan Cache Snapshot name and the table name here.
I you prefer to run a SQL statement to create a Plan Cache Snapshot you can use the SQL statement from below:
Please notice in the image below, that running the SQL statement creates a Plan Cache Snapshot where the name and the table name are identical:
When looking at what is behind the Plan Cache we use ACS Schemas to see what was created in QRPLOBJ using the GUI:
You can see a table was created which allows us to use SQL to get the data from the table we want. The lesson learned here is that it does make a difference if you use the GUI or a SQL statement to create the snapshot. As long as you keep an eye on the table name behind the Plan Cache Snapshot you are good to go.
Now we can query the table QZRG000277 with a SQL statement like the one below:
We can take it one level further, to discover which tables are queried using a table scan:
If you never encounter SQL performance problems, there is no need to dive into this matter in the way I have shown, but I hope you now better understand the wealth of information IBM i is giving us available to be used when needed.
As the dump of the Plan Cache Snapshot also contains the exact SQL statement executed for which a Table Scan was needed, but I must admit, how to retrieve that information using SQL is something I do not know yet. I do use the word “yet”, because the voyage to discover more within IBM i goes on. So for now I use the option Visual Explain Center, Plan Cache Snapshots and the option “Show Statements”.
Please let me know if you think this article was helpful, because there is more to a table scan which does deserve an article. If I get positive feedback I’ll be happy to write more.