Thursday, December 21, 2006

Top SQL's

Awareness is first step towards resolution.
I hope you will agree with me.

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 86 databases, and finding out which is the culprit process and database sounds a daunting task (but it isn't).

See this:

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:

bash-3.00$ top
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 red.

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.


Connected to:
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;

SID SQL
--------- -----------------
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).
Issue is to be addressed right away or to be taken to development team is a subjective issue which i don’t want to comment.

5 comments:

rAvi said...

Sachin,

Can you please brief, on how would you go about finding the top CPU consuming process in a Windows Environment.

Through task manager all you can find is the total consumed cpu resource for oracle.exe as a process.

Appreciate the help and the article.

Regards,
~rAvi...

Sachin said...

You may check

Oracle Administration Assistant for Windows (under configuration and migration tools).

check your db and right click on process, you will get thread-cpu information.

Sachin

vagabond said...

i like the opening statement of the post :)

prakash said...

Hi i do have problems in my database the top command shows following results:
load averages: 0.83, 1.20, 1.95 14:12:23
328 processes: 327 sleeping, 1 on cpu
CPU states: 79.5% idle, 14.6% user, 4.3% kernel, 1.6% iowait, 0.0% swap
Memory: 16G real, 8004M free, 5260M swap in use, 10G swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
2651 oracle 173 59 0 0K 0K sleep 535:22 6.36% oracle
24246 oracle 1 59 0 0K 0K sleep 293:54 3.42% oracle
19882 oracle 1 59 0 0K 0K sleep 5:02 3.11% oracle
1704 oracle 1 59 0 0K 0K sleep 0:12 0.79% oracle
9809 oracle 1 59 0 0K 0K sleep 4:05 0.63% oracle
2853 oracle 1 59 0 0K 0K sleep 51:08 0.38% oracle
20466 oracle 1 59 0 0K 0K sleep 2:43 0.33% oracle
9769 oracle 1 59 0 0K 0K sleep 3:34 0.19% oracle
28574 root 23 29 10 82M 37M sleep 45:44 0.15% java
24152 oracle 11 59 0 0K 0K sleep 23:38 0.12% oracle
3566 oracle 1 59 0 0K 0K sleep 0:00 0.09% oracle
1809 oracle 1 59 0 2528K 1528K cpu/1 0:02 0.09% top
23679 oracle 1 43 0 0K 0K sleep 7:33 0.07% tnslsnr
26715 oracle 1 59 0 0K 0K sleep 0:20 0.07% oracle
3560 oracle 1 59 0 0K 0K sleep 0:00 0.06% oracle


in this a process 2651 is taking cpu from 535 min and eventhough it is in sleep state why it is using cpu and what are the steps to be taken for process 2651 to come out from system


Regards

Prakash

Kumar said...

It poped up while I was searching in google. This post was really very helpful to me

I added your blog in my favorites

Hope I see more posts in future
Thanks !