DBA Tips Archive for Oracle
by Jeff Hunter, Sr. Database Administrator
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, you will have a screen similar to the following:
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. This will bring up the following window:
We can see from the window (above) that Thread ID "7e8" 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:
7e8 (Hex) ==> 2024 (Dec)
We now have all of the information we need to kill our runaway user session using OraKill:
C:\> orakill JEFFDB 2024 Kill of thread id 2024 in instance JEFFDB 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>
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.