Determining Resource-Intensive Sessions

No matter how hard the DBA tries , bad database performance can happen. A user walks into your cubicle looking for help because suddenly and without cause, database performance came to a grinding halt. Users always claim that no one changed anything or issued a questionable query.

Fortunately TOAD has a screen for finding out just exactly why database performance has tanked ”it's the Top Session Finder screen, shown in Figure 5.26 and located on the main menu at DBA, Top Session Finder. This screen helps the DBA find those sessions that are consuming excessive database resources. And though TOAD ships with 11 predefined resource profiles to use as your search criteria, you may create custom profiles as well. In Figure 5.26, the search criteria shown was for all those sessions making heavy database sort usage.

Figure 5.26. TOAD Top Session Finder screen.

When defining your own profiles, you can select either a single parameter or multiple parameters (controlled by this screen's options and accessed by using this screen's toolbar icon for options). When you select multiple parameters as in Figure 5.26, you can assign relative weights for each parameter. The screen will then sort the obtained results by the weighted sum. So in Figure 5.26 the disk sorts are 100 times more important than memory sorts, and thus should sort higher in the results so as to be more visible. It takes a little effort to get used to thinking this way, but this capability makes the screen very powerful and quite useful.