Guru: Understanding Database Performance Using The Performance Data Investigator, Part 2
June 28, 2021 Dawn May
This is the second in a two-part series on how you can use the Performance Data Investigator (PDI) to investigate performance of Db2 for i. While the SQL Performance Center in Access Client Solutions is more commonly known than PDI, using both tools is a good way to analyze database performance.
In Part 1, I reviewed the Database content package, focusing on the charts for I/O reads and writes, SQL CPU utilization, and database locks. In this tech tip, I continue by reviewing the charts related to SQL Cursor and Native DB Opens as well as the SQL Performance Data charts, outlined in the screen capture below.
SQL Cursor and Native DB Opens
In this section of the navigation tree, there are charts that display the counts of native database (i.e., record level access) full opens and SQL full opens. These are important metrics to review and understand as a full open is expensive in terms of performance. The charts in PDI help you understand the rate of full opens, as well as what jobs or user profiles are doing the opens. However, if you need more information, such as what programs or procedures are doing the full opens, you need Performance Explorer data.
In the screen capture below, I can see opens per second for SQL full opens, SQL pseudo opens, native full opens, as well as the number of plans built per second. I like that that SQL pseudo opens are a significant contributor, but the native full opens looks concerning.
The IBM i Performance FAQ states: “A general guideline for partition wide full open rates is less than 1000 per second on small to medium sized partitions, and low single digit thousands per second on large partitions.”
In the screen capture below, the SQL metrics are removed to provide a more detailed look at the number of native full opens; clearly there is a significant opportunity to improve performance during the early morning timeframe.
SQL Performance Data
When you open the SQL Performance Center in Access Client Solutions, you are presented with the properties of the plan cache at that point in time.
Collection Services collects SQL plan cache statistics at each interval. The charts in the Collection Services folder visualize many of these metrics over time and you can see how these statistics change throughout the day.
One of my favorite charts is the Plans Detailed chart. This chart shows the activity in the SQL plan cache in terms of plans built, plans removed, and plans pruned; it also shows the size of your plan cache as well as the plan cache size threshold. When the plan cache is automatically sized, this chart displays the plan cache size over time, which is insightful for issues regarding temporary storage utilization resulting in the plan cache size being pruned to a smaller size, potentially capped at 512Mb.
I won’t review all of these charts; but if you are tasked with understanding your database performance, these graphs can provide insight into the SQL plan cache.
SQL Plan Cache Snapshots and Event Monitors, SQL Performance Monitor
This last set of charts is based upon plan cache snapshots, event monitors, and SQL performance monitor data, so you must obtain this type of data first. While using the SQL Performance Center to study this data can be useful, PDI offers a visual summarization that may provide more insight. The SQL Overview and SQL Attribute Mix perspectives each consist of many charts, which are too numerous to review here.
The SQL Overview perspective summarizes the following:
- Query time
- Open summary (number of reusable vs nonreusable ODPs)
- Open type summary (full opens vs pseudo opens)
- Statement usage summary
- Index used summary (no index, MTI used, index used, both)
- Index create summary (full indexes created, sparse indexes created, index from index)
- Index advised (index creates advised vs no index creates advised)
- Statistics advised (advised statistics vs no advised statistics)
- MQT use (MQTs used vs MQTs not used)
- Access plan use (access plan used, access plan rebuilds, new access plan)
- Parallel degree usage (the parallel degree used by number of statements)
The SQL Attribute Mix perspective summarizes the following:
- Statement summary (select, update, insert, delete, other, data definition, call)
- Statement type summary (static SQL, dynamic, extended dynamic, system-wide cache dynamic)
- Isolation level summary (no commit, uncommitted read, cursor stability, repeatable read, read stability, cursor stability keep locks, not applicable)
- Allow copy data summary (allow copy no, allow copy optimize, allow copy yes)
- Sort sequence summary (sort sequence vs no sort sequence)
- Close cursor summary (endmod/endpgm vs endact/endjob)
- Naming summary (system vs SQL naming)
- Optimization goal (first I/O vs all I/O)
- Blocking summary (blocking enabled all read, blocking enabled read, no blocking enabled)
If you are reviewing your SQL plan cache snapshots or SQL plan cache event monitors via the SQL Performance Center, or using the Navigator for i web console, you can take the Investigate Performance Data option from these collections, which will open the SQL Overview perspective.
The performance of the database is critial on IBM i. If you haven’t been using the PDI Database charts, now is the time to start.
Dawn May worked on the AS/400 / iSeries / IBM i development team in Rochester, Minnesota, for more than 30 years. Dawn was the technical lead for a variety of projects at IBM, including development of the Performance Data Investigator. Dawn now runs her own consulting practice, specializing in troubleshooting and education. To learn more about PDI and how to use it, or for assistance with performance issues, visit dawnmayi.com.
RELATED STORIES
Understanding Database Performance using The IBM i Performance Data Investigator, Part 1
Getting Started With The IBM i Performance Data Investigator
