 |
|
Oracle Tips by Burleson |
High CPU Utilization
A user calls and reports that the system is
slow. First, attempt to narrow the scope of the problem by
investigating whether this is impacting all users of the
application, all portions of the application, or all applications.
Log in and analyze the status of this database, the database server,
and either the application server or the middleware server.
Figure 6.1 is an example of the output from
the UNIX operating system command top followed by the ps command.
There is a process that is using almost 98% of the CPU time. This
process is running against the TOY instance on this server. A
second ps command is used to learn that the parent process that
launched this process is sqlplus, and its process id is 22013. The
process id (PID) for the high CPU process is 22014. This
information will be used Now this information will be used to dig
into the database to learn more about this process.
Figure 6.1 – top of Database Server
Use the process id (PID) obtained in the
above manner to discover which user in the database is making the
system work so hard. The following script, find_db_user.sql, will
prompt for a UNIX process id and will provide the information that
will be required later about the user in order to do further tuning
with extended SQL tracing. Figure 6.2 shows the output from the
sample run of this script.
* find_db_user.sql
--
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial
purposes
-- with no warranties. Use at your own
risk.
--
-- To license this script for a commercial
purpose,
-- contact info@rampant.cc
--
*************************************************
column spid format 999999
column sid format 99999
column serial# format 99999
column username format a15
accept unix_pid prompt 'Enter the UNIX
process ID: '
select
spid,
sid,
a.serial#,
a.username "User in DB",
b.username "User in OS"
from
v$session a, v$process b
where:
See Code Depot
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
