Monitoring a Database Instance

On occasion, database performance becomes an issue and demands a DBA's attention. No matter how hard the DBA tries , the following bad performance scenario always happens. The telephone rings, the pager goes off, or someone walks into your cubicle because all of a sudden and without cause, database performance came to a grinding halt. Moreover, it's always the case that no one has changed anything or has issued a questionable query. They all swear it happened suddenly and for absolutely no reason. No one is ever to blame.

Fortunately TOAD has a screen for high-level monitoring exactly what's going on inside the database, and thus exposes why its performance has tanked ”it's the Monitor Database screen, shown in Figures 6.53 and 6.54, and accessible from the main menu at DBAMonitor Database. This screen graphically represents major database performance functional areas of key concern to the DBA. In essence, this screen presents a fifty-thousand- foot view of what's happening inside the database. Often one or more graphs will reveal some problem area, such as too much physical I/O or too many wait states of a specific nature. The DBA then utilizes this information to modify database conditions or parameters in order to alleviate the problems.

Figure 6.53. TOAD Database Monitor ”left side.

Figure 6.54. TOAD Database Monitor ”right side.

One really useful feature on this screen that's not readily apparent is the ability to both print and zoom in on a single graph via context menu options. For example, if the database wait states were a problem, you could zoom into that graph as shown in Figure 6.55. Also, did you notice the TOAD tray icon? It animates whenever minimum or maximum thresholds are crossed. You can set these via the TOAD Options screen category for Monitors/Database as shown in Figure 6.56. Besides thresholds, you can specify whether or not to enable alerts, scripts that run for an alert, or to use the tray icon. Plus you can specify a script to run for each threshold.

Figure 6.55. TOAD Database Monitor ”zoom in.

Figure 6.56. TOAD Monitor Options for Monitor Database.

Setting alerts to signal and scripts to automatically execute upon thresholds permits DBA's to proactively monitor and self diagnosis their databases. For example sending an email that space is running out and automatically running a clean-up script to buy the DBA time until he's received that message may well prevent unnecessary user errors or database downtime.

Finally, you may find the Monitor Database screen so useful as a DBA that you want it to automatically launch every time you start up TOAD or connect to a database. You can accomplish this by simply checking the box for Open Database Monitor Window for Each Connection field in the TOAD Options screen under the DBA category as shown in Figure 6.57.

Figure 6.57. TOAD DBA Options for Monitor Database.