One of the important tasks of the DBA is to know what the high CPU consuming processes on database server are.
In my last organization, we used get number of request saying that DB server is running slow.
Now the problem is that, this server is hosting many databases, and finding out which is the culprit process and database sounds a daunting task (but it isn’t).
First find out the top CPU processes on your system:
You may use TOP (or ps aux) or any other utility to find the top cpu consuming process.
Here is a sample top output:
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
17480 oracle 11 59 0 1576M 1486M sleep 0:09 23.51% oracle
9172 oracle 258 59 2 1576M 1476M sleep 0:43 1.33% oracle
9176 oracle 14 59 2 1582M 1472M sleep 0:52 0.43% oracle
17550 oracle 1 59 0 3188K 1580K cpu/1 0:00 0.04% top
9178 oracle 13 59 2 1571M 1472M sleep 2:30 0.03% oracle
You can see the bold section. Process# 17480 is consuming 23.51 % CPU.
Now this process can belong to any process out of many instances on this server.
To find out which instance this process belongs to:
bash-3.00$ ps -ef grep 17480
oracle 17480 17479 0 03:02:03 ? 0:48 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
The instance name is highlighted in bold.
Now you know which instance is holding that session.
Change your environmental settings (ORACLE_SID, ORACLE_HOME) related to this database.
and connect to the database as SYSDBA
bash-3.00$ sqlplus “/ as sysdba”
SQL*Plus: Release 10.2.0.2.0 – Production on Thu Dec 21 04:03:44 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Oracle Database 10g Release 10.2.0.2.0 – Production
SQL> select ses.sid SID,sqa.SQL_TEXT SQL from
2 v$session ses, v$sqlarea sqa, v$process proc
3 where ses.paddr=proc.addr
4 and ses.sql_hash_value=sqa.hash_value
5 and proc.spid=17480;
67 delete from test
Now you have the responsible SQL behind 23% CPU using process.
In my case it was a deliberate DELETE statement to induct this test but in your case it can be a query worth tuning.
Mostly knowing what is the problem is solution of the problem. (At least you know what the issue is).