My Favorite RMU SHOW STATISTICS Screens
Dr. Lilian Hobbs
Over the years, I have used RMU/SHOW Statistics extensively. Although it has many screens, I find a few extremely useful. If you would like to enjoy using this tool, my suggestion is to start with these screens first:
Summary Locking Statistics,
File IO Overview,
ALS Dashboard, and
First, a little background. I have been working with Rdb for such a long time that I can remember when the utility RMU/SHOW Statistics was first a welcome introduction into the product. Originally developed for the DBMS product, the utility was made available on Rdb Version 2. Today, it has over one hundred screens providing a wealth of information. Many people, not surprisingly, find it difficult to use, because they're not sure where to look or how to interpret the information they find. Rick Anderson, the developer who looks after this utility, tried to help resolve this problem by creating the RMU Show Statistics Handbook. It is a fantastic book, but its hardcopy version is inches thick---and so may be daunting to some. Before you dive into the entire handbook, you might want to try the screens detailed in this article.
My favorite screen has to be the
Stall Messages screen found in the
Process Information section. I visit this first, because it tells me whether the database is moving or not. How many times have I heard the cry, "The database has stopped!" But when I check this screen and see no activity, I know the problem must be elsewhere.
In the following example, we can see that we have two stalls in this database. One is waiting on the AIJ file and another is waiting on a lock conflict.
When using this utility, don't forget the menu options at the bottom. Many of the screens have a Config option. For this screen, Config would enable us to see the time the user has been waiting, so we do not have to calculate it ourselves.
Fortunately, not all databases are stalled; they are usually doing something. So where should you go next? I like to go to the Summary Locking Statistics screen to check out the total stall time for the database. This stall time can give a good indication of database throughput. A value well over a few million is a sign that you should check your database for locking problems.
When looking at this screen, it's worth mentioning which columns to read. I am usually mainly focused on the total column, although keeping an eye on the max column to see the highest value during this monitoring period. The current column is useful to see the activity at the moment. Rarely do I consult the average columns.
The default rate is 3 seconds, but 1 is a better value because most of us can relate to being told about something that occurs every second rather than every three seconds. Values less than one second can be used, but that is for special cases only.
In Rdb7, Logical Area screens were introduced. This has certainly made finding problems areas in the database easy, especially when the Logical Area Overview screen is used. So when would you use this screen? Suppose you are seeing very heavy I/O in a storage area and you don't understand why. Checking this screen will tell you which table or index is being used. Then you can try and identify the query. Usually, you find a table is being accessed that you didn't suspect---which is probably why you couldn't find the problem before. In the following example, we can see that a table called LILIAN is experiencing heavy reading and writing.
The Overview screen has only recently been introduced in Rdb7. Before this, to find this information you had to output all the screens and use DCL search utility to consolidate that information into a file that could be manually scanned. Although this wasn't a difficult procedure, now it is even easier, especially as you can use the Config option to sort the Overview screen.
Another good screen to monitor is the File IO Overview screen. This reports all I/O for every database file: root, AIJ, RUJ, storage areas, and snapshots. Once again the Config option will quickly sort the information so that you easily see where the problem lies.
Another screen well worthy of mention is the ALS Dashboard screen. Often overlooked, this screen can be a lifesaver. The Stall messages screen example showed us that we had a problem with the AIJ file and that we needed another one. We could spend ages trying to work out the syntax to do this or we could use the ALS Dashboard screen and set the Emergency AIJ value to 1. This will allow Rdb to create its own emergency AIJ file, using one of the spare slots.
The final screen that I will mention (although I assure you that I have a few more favorites) is the AIJ Information screen. This can be found in the Journaling Information section. This extremely useful screen tells you everything about the state of journaling in your system.
In the following example, three of our AIJ files need to be backed up. There is little more than 1000 blocks of free space left in the current AIJ, and we have already created two emergency AIJ files. Fortunately, we have a number of spare slots so our database shouldn't stop for some time yet. If you ever worry about the size of your AIJ files, this screen should be checked regularly.
At the top of the page is useful status information---for example, fast commit may be enabled while commit to Journal is turned off, or the AIJ Backup Server is enabled but hasn't been run yet.
I hope that this short introduction to some of the screens in RMU/SHOW STATISTICS will encourage you to look at this utility and to start exploring.