Monday, September 19, 2011

Identifying Top Server-Consuming Processes (top)

If installed, the top utility is often the first investigative tool employed by DBAs and system
administrators to identify resource-intensive processes on a server. If a process is continuously
consuming excessive system resources, then you should further determine if the process is associated
with a database and a specific SQL statement.

$ top

top - 04:40:05 up 353 days, 15:16,  3 users,  load average: 2.84, 2.34, 2.4
Tasks: 454 total,   4 running, 450 sleeping,   0 stopped,   0 zombie 
Cpu(s): 64.3%us,  3.4%sy,  0.0%ni, 20.6%id, 11.8%wa,  0.0%hi,  0.0%si,  0.0
Mem:   7645184k total,  6382956k used,  1262228k free,   176480k buffers 
Swap:  4128760k total,      184k used,  4128576k free,  3953512k cached 

19888 oracle    25   0  148m  13m  11m R 100.1  0.2 313371:45 oracle 
19853 oracle    25   0  148m  13m  11m R 99.8  0.2 313375:41 oracle 
9722 oracle    18   0 1095m 287m 150m R 58.6  3.8   0:41.89 oracle 
445 root      11  -5     0    0    0 S  0.3  0.0   8:32.67 kjournald 
9667 oracle    15   0  954m  55m  50m S  0.3  0.7   0:01.03 oracle 
2 root      RT  -5     0    0    0 S  0.0  0.0   2:17.99 migration/0 

the first section of the output displays general system information such as how long the server has been running, number of users, CPU information, and so on.
The second section shows which processes are consuming the most CPU resources (listed top to bottom). In the prior output, the process ID of 19888 is consuming a large amount of CPU. To determine which database this process is associated with, use the ps command:

$ ps 19888

Here is the associated output:
19888 ?        Rs   313393:32 warehouselab (DESCRIPTION=(LOCAL=YES)

In the prior output, the fourth column displays the value of warehouselab. This indicates that this is
an Oracle process associated with the warehouselab database. If the process continues to consume resources,
you can next determine if there is a SQL statement associated with the process or terminate the process

In order to link/map the process to the database, read the following post

No comments:

Post a Comment