Finding Top Memory and CPU Consumers Using “top” Command

 Every now and then DBAs need to investigate memory and CPU consumption on database servers. To find out the CPU and memory consumption, “top” is the most commonly used command in Linux environments. “free” is another command to display current memory consumption. Most important part of investigation is to sort the output of ”top” command to find out the top consumers of memory or CPU. In this article I will explain the easiest and simplest way to display top consumers in descending order.

We can use “-o” option of “top” command in order to sort the output based on a column. Following are the columns that are displayed in the output of top command.

PID	OS Process ID
USER	OS User running the process
PR	Process Priority
NI
VIRT	Virtual Memory usaed by the process
RES	Physical memory used by the process
SHR	Shared meory used by the process
S	Status of the process
%CPU	Percentage of CPU used by the process
%MEM	Percentage of memory used by the process
TIME+	CPU centiseconds (hundredth of a second) used by the process
COMMAND	Command being executed

In a real time scenario, I observed memory usage going above 90% despite SGA (and current PGA usage) was not more than 60 percent of total memory. Although OS caching of file system also consumes memory, but in my case this consumption was also not very high, as an be seen below.

[root@dbhost ~]# free -g               total        used        free      shared  buff/cache   available Mem:            235         215          15           0           5          16 Swap:            15           8           7

As you can see that 215G memory is in used out of total 235G. To find out top memory consumers, I used below command.

[root@dbhost ~]# top -o RES  top - 06:13:12 up 8 days, 10:40,  2 users,  load average: 0.73, 0.88, 1.10 Tasks: 1259 total,   1 running, 1258 sleeping,   0 stopped,   0 zombie %Cpu(s):  4.6 us,  2.5 sy,  0.0 ni, 92.4 id,  0.4 wa,  0.0 hi,  0.1 si,  0.0 st KiB Mem : 24737084+total, 15762300 free, 22589432+used,  5714240 buff/cache KiB Swap: 16777212 total,  7560820 free,  9216392 used. 17400180 avail Mem     PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND 28258 oracle    20   0  121.8g   1.3g  57096 S   0.0  0.6  27:16.37 ora_p01u_p00zxx 28221 oracle    20   0  121.8g   1.3g  55184 S   0.0  0.6  27:00.20 ora_p01t_p00zxx 28276 oracle    20   0  121.8g   1.3g  50256 S   0.0  0.6  26:56.74 ora_p01v_p00zxx 28210 oracle    20   0  121.8g   1.3g  49524 S   0.0  0.6  26:16.46 ora_p01s_p00zxx 27788 oracle    20   0  121.6g   1.2g  47712 S   0.0  0.5  24:49.15 ora_p00x_p00zxx 28536 oracle    20   0  121.6g   1.2g  26540 S   0.0  0.5  26:29.13 ora_p025_p00zxx 28564 oracle    20   0  121.6g   1.2g  29440 S   0.0  0.5  26:18.79 ora_p026_p00zxx 27814 oracle    20   0  121.4g   1.1g  34340 S   0.0  0.5  24:10.08 ora_p00z_p00zxx 28648 oracle    20   0  121.4g   1.1g  45184 S   0.0  0.5  25:20.42 ora_p02b_p00zxx 27825 oracle    20   0  121.3g   1.1g  48944 S   0.0  0.5  23:48.57 ora_p010_p00zxx ... ... ...

Above command output exposed that oracle parallel server processes are the culprit here (tuning parallel server process is not the topic under discussion here therefore I would not explain it further).

Similarly, “top -o %CPU” can be used to display the output sorted in order of CPU consumption by individual processes