Monitoring and Killing User Sessions

The Oracle process model is very complex and prone to problems. In the client/server world, process disconnects between client and server processes can leave Oracle in a questionable state. These disconnects can occur for many reasons, including network problems, abnormal client program terminations, client operating system crashes, client operating system reboots, and a host of other reasons. And although Oracle's SMON and PMON background server processes are supposed to detect and handle all of these situations, often database sessions and resources can reach a problem state when these server processes fail to detect and address such conditions. So the DBA must become involved.

But these connectivity issues are not the only kind of Oracle process problems that can occur. Sessions may issue statements that compete for resources, such as temporary space, rollback segments, or data file space. Moreover, sessions may also have statements that compete for access to the same rows of data. And in the worst-case scenario, this competition may cause a deadly embrace or deadlock. Again, Oracle is supposed to automatically handle or resolve many of these issues as well. And once again, the reality is that often the DBA must step in to assist Oracle.

When dealing with such session-related issues, probably the most utilized, practical, and indispensable feature within TOAD is the Kill/Trace Session screen, shown in Figure 5.27 and located on the main menu at DBA, Kill/Trace Session. This do-it-all screen is the central console from which to see or do anything related to sessions. Not only can you see all the sessions, their SQL statements, cursors , explain plans, and DML progress as shown in Figure 5.27, but you can see the database locks, blocking locks (shown in Figure 5.28), database objects being accessed, and all activity related to rollback segment usage (shown in Figure 5.29). This one screen truly shows it all.

Figure 5.27. TOAD Kill/Trace Session screen.

Figure 5.28. TOAD Database Blocking Locks.

Figure 5.29. TOAD Rollback Segment Usage.

Of course, the Kill/Trace Session screen provides much more capability than to simply view all this detailed session data. The screen's toolbar icons enable you to start or stop a trace file for a session and to kill a session. The trace files are critical when either working with Oracle support to debug RDBMS problems, using TOAD's PL/SQL Profiler, or using TOAD's TKPROF interface. And killing a session is often necessary to circumvent the problems encountered with Oracle processes.

The command TOAD issues to kill the process is of the form:

ALTER SYSTEM DISCONNECT SESSION 'SID#, SERIAL#' IMMEDIATE

Note that killing Oracle server processes in this manner is not guaranteed to always work instantaneously. Sometimes Oracle will kill the server process immediately so that the operating system's command to see the process (for example, the ps command on UNIX) will show it as killed right away. Sometimes Oracle may internally mark the process as deleted, but yet not actually kill the OS process until much later. In fact, Oracle support documents that this can take hours depending on what the process was doing and which requires being rolled back. This is not a TOAD problem, but an Oracle process behavior beyond TOAD's control. Thus it's not unusual for people to kill the processes by OS commands instead of through Oracle. For manually killing processes on UNIX, you should use TOAD's telnet interface on the Network Utilities screen, located on the main menu at File, Network Utilities.

Remember that an important and routine task any DBA does is to monitor and kill sessions. So this is most likely one of the first things a DBA would want to do when they start their daily, morning routine and throughout the work day. Therefore, wouldn't it be useful if TOAD would automatically open this screen when it launched? It can do so if you use the TOAD Options category for Startup as shown in Figure 5.30. You just need to check the box for Kill/Trace Within Startup Windows Per Connection. Now TOAD will automatically launch this window at startup.

Figure 5.30. TOAD Startup Options for Kill/Trace Session.