The orakill utility

orakill

The orakill utility is provided only with Oracle databases on Windows platforms. The executable (orakill.exe) is available to DBAs to kill Oracle sessions directly from the DOS command line without requiring any connection to the database.

In the UNIX world, a DBA can kill a shadow process by issuing the kill –9 command from the UNIX prompt. UNIX is able to provide this capability given that the UNIX operating system is based on processes that fork other processes. All processes can be listed by using the ps UNIX command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process.

Unlike the UNIX operating system, Windows systems are thread-based. For each instance, the background processes and sessions are all contained within the oracle.exe executable. These processes are not listed in the "Processes" tab of Windows Task Manager. Each session creates its own thread within oracle.exe and therefore, is not exposed to the Windows user. Killing the oracle.exe process in Windows would crash the entire database.

The orakill utility serves the same purpose as kill –9 in UNIX . The command requires the instance and the SPID of the thread to kill. The utility will display exactly how to obtain the SPID in the event the command was entered without parameters:

C:\oracle9i\bin>orakill

Usage: orakill sid thread

where sid = the Oracle instance to target
thread = the thread id of the thread to kill

The thread id should be retrieved from the spid column of a query such as:

select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr

If the statement suggested by Oracle (above) to retrieve the Thread ID is executed, the results below are displayed:

select a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.username is not null;


USERNAME OSUSER SPID
------------------------------ ------------------------------ -----
SCOTT Scott 3116
AMOORE Alex 4760
DMOORE Dave 768

With the SPID for each user listed above, the session for any user can be killed.

SQL> select a.username, a.osuser, b.spid
2 from v$session a, v$process b
3 where a.paddr = b.addr
4 and a.username is not null;


USERNAME OSUSER SPID
------------------------------ ------------------------------ -----
SCOTT Scott 3116
DMOORE Dave 768

2 rows selected.

Notice that SPID 4760, user AMOORE is gone.

Why does Oracle provide a utility to kill sessions from the DOS prompt, when a DBA could kill a user session from within Oracle? The following command will also kill the user session:

alter system kill session(sid, serial#);

The sid (session ID) and serial# above can be obtained from the v$session view. There are a couple of reasons a DBA might use orakill instead of the alter system kill session command.

1. The alter system statement will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.
2. A DBA may be unable to gain access to a SQL prompt due to a runaway query consuming all database resources. In this case, the session can be killed without ever logging in to the database.

These are good reasons to kill threads directly from the DOS prompt, but they do not address how to get the required kill information from the database if access is unavailable. How can a DBA obtain the SPID?

One way to obtain the Oracle SPID is to use a tool like QuickSlice from Microsoft (free download) that will display Windows threads and their IDs. The QuickSlice main screen (Figure 3.1) displays each executable that is active in the operating system.

The entries listed in QuickSlice match those displayed in the Windows Task Manager. QuickSlice also displays the amount of the CPU being used by an executable, as does the Task Manager.

Figure 3.1 – QuickSlice Main Screen

The DBA can use the main QuickSlice screen to determine the CPU-intensive executables, and drill down into the threads for more information.

The next QuickSlice window (Figure 3.2) displays the threads for a given executable (oracle.exe). In the example below, TID 300 is using the CPU.

Figure 3.2 – QuickSlice Thread Window

The DBA can quickly identify the most consumptive thread and decide what to do. The TID column (Thread ID) in QuickSliceis a Hex value and matches the decimal value for the spid column from v$session. In this case, the TID consuming the CPU is 300 (Hex), which equals Session spid 768 (Decimal). Therefore, the command to kill this session would be:

C:\oracle9i\bin>orakill ORCL92 768

In this example, the thread (Oracle session) was killed in the operating system without ever logging into the database. Before killing the session, the DBA may decide to view the SQL being executed by the session. This can be obtained by using the TID above (300) in the following SQL statement:

select b.username, a.sql_text from
v$sqltext_with_newlines a, v$session b, v$process c
where c.spid = to_number('300', 'xxx')
and c.addr = b.paddr
and b.sql_address = a.address;

Tips for using orakill

· The orakill utility should be used as a last resort only. If the session cannot be killed more gracefully (via alter system kill session), or the instance is inaccessible via SQL, then orakill should be used to terminate the offending session.

· Background processes should not be terminated, mainly user sessions. Killing a background process like SMON or PMON can cause serious Oracle errors and can bring the database down. To confirm that it is not a background session being killed, the following query can be used to identify the SPID for each background process:

select c.name, b.spid, a.sid
from v$session a, v$process b, v$bgprocess c
where c.paddr <> '00'
and c.paddr = b.addr
and b.addr = a.paddr;

NAME SPID SID
----- ------------ ----------
PMON 1680 1
DBW0 1828 2
LGWR 1844 3
CKPT 1852 4
SMON 1848 5
RECO 2060 6
CJQ0 2064 7
QMN0 2072 8

• Access to the Windows machine containing the database must be secure. Any user with access to the box could access orakill or the Windows Task Manager and damage database processes.
• The DBA may decide to use operating system utilities to kill sessions and monitor Oracle CPU utilization. Starting at the operating system level is an effective technique employed by many seasoned DBAs.