If you are following along and computing these metrics, you should collect DB2 LUW performance data by following these PDF instructions.
The Average Result Set Size, or ARSS, tells us if the database is behaving like an OLTP database, or a Data Warehouse database. Depending on the type of database, certain metrics and tuning techniques will be more or less important.
The Index Read Efficiency, or IREF, metric tells us how efficiently DB2 is using indexes to qualify rows for inclusion in result sets. The lower the IREF, the better. Less than 10 is usually good. Be very concerned if your IREF is greater than 100, and maybe it is time to find a new job if IREF is greater than 1,000.
The Synchronous Read Percentage, or SRP, tells us how much random synchronous I/O DB2 is doing. High SRP values are achieved when high quality indexes are in place. When DB2 does too much prefetch I/O scanning (with asynchronous I/O), SRP will be lower. If you have an OLTP database and SRP is less than 80%, absolutely do not upgrade your hardware - you have physical design problems to fix first.
We covered several cost metrics.
Selects per Transaction (SELTX ), DML per Transaction (DMLTX), and Fetches per Transaction (FETTX) tell us about the nature and size of the workload and the composition of its transactions. It is rare that the DBA can influence these cost metrics, with a few exceptions - such as discovering that an application is reading in an entire table (evidenced by very high FETTX) and doing its own filtering and sorting instead of letting DB2 do the work, and cajoling the developers into making changes.
However, the DBA can influence, through tuning and physical design techniques, the following costs:
* The number of Sorts per Transaction (SRTTX - sorts consume CPU and large sorts may flood I/O channels to TEMPSPACE with I/Os.
* The number of Rows Read per Transaction (RRTX) - high quality indexes help DB2 find the rows it needs, and only the rows it needs, to avoid unnecessary table row reads.
* Bufferpool Logical Reads per Transaction - (BPLRTX - Logical Reads = CPU consumption. The better the physical design, the lower BPLRTX will be. Lowering BPLRTX is one of the best ways to become a Performance Hero.
* Bufferpool Logical Index Reads per Transaction - BPLITX - this cost metric can help detect the presence of index leaf page scans. By lowering BPLITX, you will lower CPU consumption and improve response times.
DATABASE FILES CLOSED
You can find "Database Files Closed" in Database snapshots, Bufferpool snapshots, and Tablespace snapshots (dbsnap2.txt, bpsnap2.txt, and tssnap2.txt respectively). Closing Files is a BIG PROBLEM. DB2 tries to be a good citizen within the operating system and not open an excessive amount of files at one time. The trouble is, the default value of 64 for the database configuration parameter MAXFILOP is WAY TOO SMALL. This value was conceived in the early 1990's when a machine had a lot of memory if it had 256MB of RAM. When DB2 closes files so that it can open other files to complete query requests, this is a HUGE waste of CPU cycles and elapsed time.
If you see that your database has closed any files (Database Files Closed > 0 ), then you need to increase the size of MAXFILOP until DB2 stops closing files. This parameter hurts performance a lot if it is set too low, but I am not aware of any case on planet earth where setting it too high has caused a problem. 32,768 is a safe bet for success.
The Shameless Marketing Moment...
I trust you can appreciate that writing these blogs takes a great deal of effort - usually about 2 hours per post. I have long believed that "A well educated consumer makes our best customer" which is the slogan for a brand name clothing store. Sometimes people don't know what they need to buy until they know what they need to know. By helping you understand what metrics are important to monitor and why, I am hopeful that your search for Database Performance Management tools will lead you to DBI for solutions that can help make you a Performance Hero in your organization. Brother-Eagle™ and Brother-Panther™ from DBI include metrics and analysis methods that will be presented here as best practices.
It seems that "being green" and energy efficiency is in fashion these days. Here's a green optimization plan for you: Get a free trial of Brother-Panther for DB2 LUW, become a Performance Hero after achieving great tuning success and avoiding hardware upgrades; avoided hardware upgrades save electricity, real estate, and air conditioning, and capital expenses, and increase your organization's profitability. Best of all, as a reward for all your "Save the Planet" good deeds, DBI will shower you with $100 USD gift certificates, mugs, T-shirts, and other awards. You win, your company wins, and the environment wins - it's a triple green play. Become a DBI Certified Performance Hero today!
Just for Fun
Check out the letter I wrote to Oprah Winfrey and had published in a major trade journal.
Until next time,
Scott Hayes
Cheers,
Scott
President & CEO, DBI
IBM GOLD Consultant
www.database-performance.info
www.database-auditing.info
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=101