OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4

The goal of database performance tuning is to minimise the response time of your queries. It is also to optimise your servers resources by minimising network traffic, disk IO, and CPU time.
This IBM Redbook helps you to understand the basics of identifying and tuning the performance of Structured Query Language (SQL) statements using IBM DB2 for i5/OS . DB2 for i5/OS provides a comprehensive set of tools that help technical analysts tune SQL queries. The SQL Performance Monitors are part of the set of tools that IBM i5/OS provides for assisting in SQL performance analysis since Version 3 Release 6. These monitors help to analyze database performance problems after SQL requests are run. In V5R4 of i5/OS iSeries Navigator provides a series of new tools to do SQL Performance analysis that we cover in this redbook. Among the new tools that we will covering are:
- Capability of visualizing the contents of the SQE Plan Cache
- SQE Plan Cache Snapshots
- The new reporting tool - Dashboard
- OnDemand Index Advisor
- Evaluators such as Index and Materialized Query Tables
This redbook also presents tips and techniques based on the SQL Performance Monitors and other tools, such as Visual Explain and all the tools provided in V5R4. You’ll find this guidance helpful in gaining the most out of both DB2 for i5/OS and query optimizer when using SQL

Chapter 1. Determining whether you have an SQL performance problem
Chapter 2. DB2 for i5/OS performance basics
Chapter 3. Overview of tools to analyze database performance
Chapter 4. Gathering SQL performance data
Chapter 5. Analyzing SQL performance data using iSeries Navigator
Chapter 6. Custom Database Monitor Analysis
Chapter 7. SQE Plan Cache and SQE Plan Cache Snapshots
Chapter 8. Analyzing database performance data with Visual Explain
Chapter 9. Index Advisor
Chapter 10. SQL Performance Analysis: A Methodology
Chapter 11. Environmental settings that affect SQL Performance
Chapter 12. Tips to pro-actively prevent SQL performance problems
Chapter 13. Using Collection Services data to identify jobs using system resources
Appendix A. Tools to check a performance problem

http://www.redbooks.ibm.com/abstracts/sg247326.html

L3 Cache Makes All The Difference For Java Apps on IBM i

Timothy Prickett Morgan is talking about the performance of the latest Power6+ system i boxes, and benchmark testing of SAP and Lawson products.

http://www.itjungle.com/tfh/tfh051809-story06.html

A couple of interesting thngs emerge...

One is that in these tests there was very little diference in performance between a 550 and a 570

A Power 520 with a single Power6+ processor with two cores running at 4.7 GHz equipped with 32 GB of memory was able to process 41,090 query navigation steps per hour on the BI-MXL test at 94 percent of CPU utilization on a data warehouse with 300 million records. (That's the smallest database used in the test, which also has 1 million and 3 million record variants.) A Power 550 with two processors and four cores activated running at 5 GHz and with 64 GB of memory was able to handle 90,635 query navigation steps per hour at 98 percent of CPU; for some reason, IBM ran the test again on this box a few weeks later and got a slightly lower 97 percent CPU utilization and only handled 90,492 query navigation steps per hour. (Go figure.) A Power 570 box with four 5 GHz cores and 96 GB of memory did slightly more work, at 93,468 query navigation steps per hour.
The message here is what I have been telling you for years: Don't buy into the Power 570 unless you have to. It is more expensive for modest workloads than a Power 550. If you don't need the expansion that the Power 570 embodies, don't do it

The other was that L3 cache is very important for java app performance

On the M3 tests, Lawson found that the initial Power 520 using that old 1.9 GHz Power5 chip could process about 75,000 invoiced order lines per hour. Moving up to the 4.2 GHz Power6 core boosted performance by 31 percent (not more than 2X as you might expect from the clock speeds because IBM radically changed the instruction pipelines with the Power6 chips), to around 98,250 invoiced order lines per hour. And while the move to the Power6+ chip only boosted the clock speed by 11.9 percent up to 4.7 GHz, the addition of the L3 cache pushed performance up 21 percent to 118,900 invoiced order lines per hour. Which makes you wonder why on Earth IBM ever cropped the L3 cache out of the boxes to artificially crimp performance on the Power 520s and the JS12 and JS22 blades in the first place. That's not a smart move if you are trying to support Java applications