Linux Top Command Usage For Oracle DBA

Linux Top Command Usage For Oracle DBA

 

top – Display Linux processes

  1. The most useful one for an Oracle DBA managing a database on Linux
  2. It refreshes the screen to display new information
  3. The top command provides much the same information as the Windows Task Manager.
  4. we can see the running processes CPU and memory usage as well as the overall system load.
  5. We are able to easily identify CPU usage, memory usage, and who is running the most expensive processes.
  6. The top utility provides the same information with “uptime”
    and “free”, and it also shows who’s consuming CPU

Click here for more information about Top command http://man7.org/linux/man-pages/man1/top.1.html

Displays The current information.

[oracle@prod101:~ orcl] top

top - 01:31:42 up 209 days, 20:31,  1 user,  load average: 5.02, 4.17, 3.93
Tasks: 1874 total,   5 running, 1868 sleeping,   0 stopped,   1 zombie
Cpu(s): 18.8%us,  1.6%sy,  0.0%ni, 64.8%id, 14.6%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:  264422444k total, 176117440k used, 88305004k free,  1119876k buffers
Swap: 135757816k total,        0k used, 135757816k free, 138847252k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14584 oracle    20   0 17.3g 4.6g 4.6g R 100.0  1.8   7:53.93 oracle_14584_gl
 5388 oracle    20   0 17.3g 376m 372m R 99.3  0.1   1:27.23 oracle_5388_gl
21293 oracle    20   0 17.3g 7.4g 7.4g R 41.8  2.9  80:43.52
The first line:-
top - 01:31:42 up 209 days, 20:31, 1 user, load average: 5.02, 4.17, 3.93 

shows the current time (01:31:42), that system has been up for 42 mints; that the system has been working for 20 hours 31 seconds. The load average of the system is shown (5.02, 4.17, 3.93) for the last 1, 5 and 15 minutes respectively. (By the way, you can also get this information by issuing the uptime command.)
If the load average is not required, press the letter "l" (lowercase L); it will turn it off. To turn it back on press l again. Ideally Load average should be less than 1, otherwise the processes are fully burdened
The second line:-
Tasks: 1874 total, 5 running, 1868 sleeping, 0 stopped, 1 zombie

shows the number of processes, running, sleeping, etc. 
1874 processes: 1868 sleeping, 5 running, 1 zombie, 0 stopped
The third and fourth lines:-
CPU states: cpu user nice system irq softirq iowait idle 
total 18.8% 0.0% 1.6% 0.0% 0.2% 14.6% 64.8%

show the CPU utilization details. The above line shows that user processes consume 18.8% and system consumes 1.6%. The user processes include the Oracle processes. Press "t" to turn these three lines off and on. If there are more than one CPU, you will see one line per CPU.
The next two lines:-
Mem: 264422444k total, 176117440k used, 88305004k free, 1119876k buffers
Swap: 135757816k total, 0k used, 135757816k free, 138847252k cached

show the memory available and utilized. Total memory is "252GB", approximately 252GB (264422444k), of which only 167GB used (176117440k). Free space around 84GB (88305004k) The swap space is 129GB (135757816k); but it's almost not used. To turn it off and on, press "m".
 Filtering the processes from oracle user only To revert back enter 0

Top and press u means
If you want only top 10 processes from oracle user just press n and enter 10 To revert back enter 0

Top and press n and 10
Find how many cpu's in the server.

Top and press 1

top - 08:41:55 up 210 days, 3:54, 2 users, load average: 1.24, 1.46, 1.44
Tasks: 1481 total, 1 running, 1480 sleeping, 0 stopped, 0 zombie
Cpu0 : 30.0%us, 5.9%sy, 0.0%ni, 63.4%id, 0.3%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu1 : 11.0%us, 3.3%sy, 0.0%ni, 85.3%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu2 : 7.4%us, 4.4%sy, 0.0%ni, 87.8%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 4.3%us, 3.3%sy, 0.0%ni, 91.7%id, 0.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 7.0%us, 3.3%sy, 0.0%ni, 88.0%id, 1.3%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu5 : 6.0%us, 3.0%sy, 0.0%ni, 90.6%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 5.4%us, 2.7%sy, 0.0%ni, 91.6%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 3.0%us, 1.3%sy, 0.0%ni, 95.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Let’s start the Demo:-

Here the PID of the highest consuming process is: 11282.
We can use the following query to determine what is the process is consuming More cpu:

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process.

SELECT a.username,a.osuser,a.program,spid,sid,a.serial#
FROM v$session a,v$process b
WHERE a.paddr = b.addr AND spid = '&pid';

select s.sid, s.username, s.program
from v$session s, v$process p
where spid = &server_process_id
and p.addr = s.paddr;
top - 02:16:23 up 209 days, 21:16, 1 user, load average: 2.76, 2.84, 3.08
Tasks: 1895 total, 2 running, 1892 sleeping, 0 stopped, 1 zombie
Cpu(s): 13.0%us, 2.0%sy, 0.0%ni, 79.3%id, 5.7%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 264422444k total, 176184672k used, 88237772k free, 1119944k buffers
Swap: 135757816k total, 0k used, 135757816k free, 138866156k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11282 oracle 20 0 17.4g 4.6g 4.6g R 95.8 1.8 10:08.14 oracle_11282_gl
5546 root 20 0 4857m 522m 9.8m S 32.6 0.2 1725:49 java


SQL> SELECT a.username,a.osuser,a.program,spid,sid,a.serial#
FROM gv$session a,gv$process b
WHERE a.paddr = b.addr AND spid = '&pid'; 2 3
Enter value for pid: 11282
old 3: WHERE a.paddr = b.addr AND spid = '&pid'
new 3: WHERE a.paddr = b.addr AND spid = '11282'

USERNAME OSUSER PROGRAM                     SPID SID SERIAL#
-------  ----- ----------                   ---- ----- -------
PROD101 sam    pro567@system(TNS V1-V3)    11282 1303 11906 
PROD101 admin   AppModule.exe              11282 1295 30526
Find that a process is consuming a lot of CPU and memory

$ top -c -p 11282

top - 02:27:36 up 209 days, 21:27, 1 user, load average: 3.60, 3.37, 3.20
Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.1%us, 1.4%sy, 0.0%ni, 82.6%id, 7.8%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 264422444k total, 175215624k used, 89206820k free, 1119964k buffers
Swap: 135757816k total, 0k used, 135757816k free, 138858124k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11282 oracle 20 0 17.4g 5.7g 5.7g D 9.3 2.3 11:54.19 oraclePROD101 (LOCAL=NO)

Skill 

$ skill -CONT 11282
This approach is immensely useful for temporarily freezing processes to make room for more important processes to complete.

The command is very versatile. If you want to stop all processes of the user "oracle", only one command does it all:

$ skill -STOP oracle
You can use a user, a PID, a command or terminal id as argument. The following stops all rman commands.

$ skill -STOP rman

References

http://www.pafumi.net/Unix_Commands_for_DBAs.html#top

Catch Me On:- Hariprasath Rajaram

LinkedIn:      https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:      https://www.facebook.com/HariPrasathdba
Facebook Group:https://www.facebook.com/groups/894402327369506/
Facebook Page: https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba