If you are an Oracle DBA working on the Microsoft Windows platform, this article introduces QuickSlice, a free utility from Microsoft that can be used to obtain CPU consumption for both processes and threads.
As a DBA, you may need to track down a runaway process running in Oracle. As you may already know, all user sessions in Oracle are contained as threads within the oracle.exe process for the instance. Using utilities like Windows Task Manager, it is possible to obtain CPU consumption for processes, but this is not very helpful if you need to determine which user session(s) (threads) are consuming CPU resources. This is where QuickSlice comes in handy.
One the key uses for QuickSlice is to determine the SPID of a user session. The SPID can be used with Oracle utilities like OraKill to kill a runaway user session for example.
QuickSlice can be downloaded using the following link:
641 KB file
3 min @ 28.8 Kbps
After downloading, installing and running the QuickSlice utility, let's now run through a quick example of how to use QuickSlice to find the SPID for a user session. We can start by creating a small runaway process. Type in the following anonymous PL/SQL block and run it:
begin
loop
null;
end loop;
end;
/
After kicking off the above code, we now have a runaway process. This can be seen in QuickSlice.
OK, we know that Oracle is consuming a tremendous amount of CPU, but which thread (user session) is taking all of the CPU? Simply double-click the oracle.exe process in QuickSlice.
We can see from the window that Thread ID "XXX" is consuming all of the CPU. This is the thread (SPID) we want to kill. Notice that QuickSlice displays the Thead ID in HEX. You will need to convert this to a decimal number in order to use it as a parameter to OraKill:
XXX (Hex) ==> XXXX (Dec)
We now have all of the information we need to kill our runaway user session using OraKill:
C:\> orakill DB XXXX
Kill of thread id XXXX in instance DB successfully signalled.
Kill of thread id XXXX in instance DB successfully signalled.
That's all there is to it. Our runaway process is immediately killed as seen below:
SQL> begin
2 loop
3 null;
4 end loop;
5 end;
6 /
begin
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
No comments:
Post a Comment