Feature
 

My Favorite RMU SHOW STATISTICS Screens

Dr. Lilian Hobbs
Senior Principal Consultant
Oracle Rdb Engineering

Introduction

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: Stall Messages, Summary Locking Statistics, Logical Areas, File IO Overview, ALS Dashboard, and AIJ Information.

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.

 

Stall Messages

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.

                                     
                                        Node: UKAA31 (1/1/1)    Oracle Rdb V7.0-13 Perf. Monitor  1-OCT-1998 15:58:55.27
                                        Rate: 3.00 Seconds               Stall Messages             Elapsed: 03:58:22.20
                                        Page: 1 of 1           DKA100:[BANKING]BANKING_LOG.RDB;1          Mode: Online 

                                        ------------------------------------------------------------------------------- 
                                        Process.ID Since...... T Stall.reason.............................Lock.ID. 
                                        000000A6:1 15:58:50.75 W waiting for logical area 50 (PR)          01000928 
                                        000000A4:1s15:58:54.19 - waiting for 1400-block unmodified AIJ (59 minutes)
                                       
-------------------------------------------------------------------------------- Alarm Bell Config Exit Filter Help LockID Menu >next_page prev_page PageInfo Se

Click here to view a larger version to view a larger version.

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.

 

Summary Locking Statistics

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.

                                     
                                        Node: UKAA31 (1/1/1)    Oracle Rdb V7.0-13 Perf. Monitor  1-OCT-1998 16:09:16.85
                                        Rate: 1.00 Second          Summary Locking Statistics       Elapsed: 04:08:43.78
                                        Page: 1 of 1           DKA100:[BANKING]BANKING_LOG.RDB;1           Mode: Online

                                        --------------------------------------------------------------------------------
                                        statistic.........      rate.per.second............. total....... average......
                                        name..............      max..... cur..... avg....... count....... per.trans....
                                       
locks requested 377 66 0.5 7504 750.4 rqsts not queued 23 0 0.0 32 3.2 rqsts stalled 0 0 0.0 7 0.7 rqst timeouts 0 0 0.0 0 0.0 rqst deadlocks 0 0 0.0 0 0.0 locks promoted 123 71 0.3 4970 497.0 proms not queued 0 0 0.0 0 0.0 proms stalled 3 3 0.0 99 9.9 prom timeouts 0 0 0.0 0 0.0 prom deadlocks 0 0 0.0 0 0.0 locks demoted 81 81 0.2 3923 392.3 locks released 353 66 0.4 6690 669.0 blocking ASTs 33 33 0.1 1586 158.6 stall time x100 59265 0 4.1 61641 6164.1 invalid lock block 0 0 0.0 0 0.0.0 -------------------------------------------------------------------------------- Exit Graph Help Menu Options Pause Reset Set_rate Time_plot Write X_plot Yank !

 

 

 

Click here to view a larger version to view a larger version.

 

Logical Areas

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.

                                     
                                        Node: UKAA31 (1/1/1)    Oracle Rdb V7.0-13 Perf. Monitor  1-OCT-1998 16:20:13.92
                                        Rate: 3.00 Seconds       Logical Area Overview (Tables)     Elapsed: 04:19:40.84
                                        Page: 1 of 1           DKA100:[BANKING]BANKING_LOG.RDB;1            Mode: Online
                                        --------------------------------------------------------------------------------
                                        Logical.Area.Name... record fetch record store record erase   discarded CurTot
                                        LILIAN.RDB$SYSTEM           33707        24576            0          0
                                       
-------------------------------------------------------------------------------- Config Exit Help Menu >next_page prev_page Options Pause Reset Set_rate Write

Click here to view a larger version to view a larger version.

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.

 

File IO Overview

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.

                                     

                                        Node: UKAA31 (1/1/1)    Oracle Rdb V7.0-13 Perf. Monitor  1-OCT-1998 16:24:30.63
                                        Rate: 3.00 Seconds   File IO Overview (Unsorted total I/O)  Elapsed: 04:23:57.56
                                        Page: 1 of 1           DKA100:[BANKING]BANKING_LOG.RDB;1            Mode: Online
                                        --------------------------------------------------------------------------------
                                        File/Storage.Area.Name........ Sync.Reads SyncWrites AsyncReads AsyncWrits PgDis
                                        Database Root                          40         11          0       40     0
                                        AIJ (After-Image Journal)              14         40          0        0     0
                                        RUJ (Recovery-Unit Journal)          1092          4          0      111     0
                                        ACE (AIJ Cache Electronic)              0          0          0        0     0
                                        All data/snap files                   131         22         92      368     4
                                        data BANK_SYSTEM_LOG                  131         22         81      367     0
                                        data BANK_CUST_LOG                      0          0          0        0     0
                                        data BANK_ACCT_LOG                      0          0          0        0     0
                                        data BANK_TRANS_LOG                     0          0         11        1     4
                                        data MIXED_55000_LOG                    0          0          0        0     0
                                        data MY_DEFAULT_LOG                     0          0          0        0     0
                                        snap BANK_SYSTEM_LOG                    0          0          0        0     0
                                        snap BANK_CUST_LOG                      0          0          0        0     0
                                        snap BANK_ACCT_LOG                      0          0          0        0     0

                                        snap BANK_TRANS_LOG                     0          0          0        0     0
                                        snap MIXED_55000_LOG                    0          0          0        0     0
                                        snap MY_DEFAULT_LOG                     0          0          0        0     0
                                        --------------------------------------------------------------------------------
                                        Config Exit Filter Help Menu >next_page prev_page Options Reset Set_rate Write
                                    
                                  

 

Click here to view a larger version to view a larger version.

ALS Dashboard

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.

                                     

                                        Node: UKAA31 (1/1/1)    Oracle Rdb V7.0-13 Perf. Monitor  1-OCT-1998 16:06:56.57
                                        Rate: 3.00 Seconds               ALS Dashboard              Elapsed: 04:06:23.50
                                        Page: 1 of 1           DKA100:[BANKING]BANKING_LOG.RDB;1           Mode: Online
                                        --------------------------------------------------------------------------------
                                        Database.......... Current... Previous.. Lowest.... Highest...Original.. Chng
                                        Attribute.Name.... Value..... Value..... Value..... Value.....Value..... Cnt.
                                       
AIJ Hiber Time 50 50 50 50 50 0 Switch Global Ckpt 1 1 1 1 1 0 Check Control Recs 1 1 1 1 1 0 Emergency AIJ 1 0 0 1 0 1
-------------------------------------------------------------------------------- Config Exit Help Menu Options Set_rate Update Write !

 

Click here to view a larger version to view a larger version.

AIJ Information

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.

                                     
                                        Node: UKAA31 (1/1/1)    Oracle Rdb V7.0-13 Perf. Monitor  1-OCT-1998 16:32:56.81
                                        Rate: 3.00 Seconds          AIJ Journal Information         Elapsed: 04:32:23.73
                                        Page: 1 of 2           DKA100:[BANKING]BANKING_LOG.RDB;1           Mode: Online
                                        --------------------------------------------------------------------------------
                                        Journaling: enabled   Shutdown:   60  Notify: enabled   State: Accessible

                                        ALS: Running    ABS: enabled   ACE: disabled  FC: enabled   CTJ: disabled
                                        ARB.Count:  300 ARB.Avail:  300
                                        After-Image.Journal.Name....... SeqNum AIJsize CurrEOF Status. State.......
                                        BANK1                                1 *BACKUP NEEDED* Written Accessible
                                        BANK2                                2 *BACKUP NEEDED* Written Accessible
                                        EMERGENCY_009CD0DDF2B99793           3 *BACKUP NEEDED* Written Accessible
                                        EMERGENCY_009CD0DE4D6B47C2           4    1536     253 Current Accessible
                                        Available AIJ slot 1
                                        Available AIJ slot 2
                                        Available AIJ slot 3
                                        Available AIJ slot 4
                                        Available AIJ slot 5
                                        Available AIJ slot 6
                                        Available AIJ slot 7
                                        Available AIJ slot 8
                                        Available AIJ slot 9
                                        Available AIJ slot 10
                                        --------------------------------------------------------------------------------
                                        Bell Exit Help Menu >next_page prev_page Refresh Set_rate Write Zoom !
                                    
                                  

Click here to view a larger version to view a larger version.

Conclusion

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.

 

Top

In-Memory Replay Banner