Linux Top Command Usage For Oracle DBA
top – Display Linux processes
- The most useful one for an Oracle DBA managing a database on Linux
- It refreshes the screen to display new information
- The top command provides much the same information as the Windows Task Manager.
- we can see the running processes CPU and memory usage as well as the overall system load.
- We are able to easily identify CPU usage, memory usage, and who is running the most expensive processes.
- The top utility provides the same information with “uptime”
and “free”, and it also shows who’s consuming CPU
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
Ref: www.pafumi.net