Page 548
From this output, it is evident that pid 28586 is the heavy CPU user. Note from the usr, sys, and idle values we can see that the system shows a value of 46.2% and sighs 1.2% and the rest is idle. This means that the system is purely servicing user requests, which is a good sign. Although the user is the top CPU user, it's not a cause for alarm, because on a lightly loaded system, a single process that requires a lot of CPU will hog the CPU.
Key things to look for are the load averages on the system. The first line shows the load averages on the system as 0.59, 0.61, and 0.69. The 0.59 is the load average over the last minute, 0.61 is the load average over the last 5 minutes, and 0.69 over the last 15 minutes. The load averages give a fair idea of how the load was on the system and is now over the last 15 minutes, which could be very useful information when the CPU loads suddenly surge and the system performance seems to be deteriorating. When the load average suddenly increases, you can infer that this could be due to some resource-intensive process on the system. System loads of 2 or 3 indicate light system loads, and loads of 5 or 6 indicate medium system loads. Other information to look for is the %CPU column, which indicates the percentage of CPU time used by the particular process. If this figure is high, it is a clear indication of the process being very CPU-intensive.
You also can ascertain heavy users by using the ps command itself. The ps command contains a column that shows the amount of time the CPU spends in servicing the particular process. You can sort the output of ps in descending order of CPU time and again find out the top CPU resource user.
Listing 22.12 Obtaining CPU Usage Using the ps Command$ ps -ael | sort -n -r -k 13 F S UID PID PPID C PRI NI ADDR SZ WCHAN TTY TIME COMD 1 R 105 28586 28585 89 200 20 3903a00 1741 - ? 192:21 oracle 1 S 104 14171 1 0 154 20 3937a00 1711 36f8b38 ? 152:29 oracle 3 S 0 3 0 0 128 20 2529100 0 3c2050 ? 122:09 statdaemon 3 S 0 0 0 0 127 20 40c8b0 0 3c2050 ? 86:47 swapper 3 S 0 2 0 0 128 20 2155e80 0 40e990 ? 32:19 vhand 1 S 0 459 458 0 127 20 2718180 2 7ffe6000 ? 32:05 netfmt 1 S 0 1016 1 0 154 20 2874180 9 40e0a8 ? 22:52 spserver 1 S 0 787 1 0 154 20 280e880 180 40e0a8 ? 21:01 dced 1 S 0 681 1 0 154 20 27cef80 85 40e0a8 ? 15:47 snmpdm 1 S 104 1339 1 0 154 20 2845980 195 40e0a8 ? 14:18 tnslsnr 3 S 0 12 0 0 138 20 254de00 0 391cf40 ? 12:00 vx_sched_threa 3 S 0 54 0 0 100 20 254d980 0 - ? 6:24 nvsisr 1 S 125 13379 1 0 156 20 3330200 1535 461a84 ? 5:21 oracle 3 S 0 4 0 0 128 20 2529380 0 40e4dc ? 4:09 unhashdaemon 1 S 125 13383 1 0 156 20 3739d80 1529 461a94 ? 4:41 oracle 1 S 104 21303 1 0 154 20 2948d80 1846 3e55438 ? 4:02 oracle
Page 549
Listing 22.12 shows that PIDs 28586 and 14171 are the top CPU resources. 28586 has used 192.21 minutes of CPU time, and 14171 has used 152.29 minutes of CPU time since the time the process started.
NOTE |
The processes may be inactive, so beware of sessions that have consumed a lot of CPU resources during the early part of the day and are now idle. To determine whether the process is idle, run the same command repeatedly and check whether the CPU time is increasing. Active sessions can be identified by looking at the second column, S, in Listing 22.12. The value R indicates that it is running. Even here, a process with state S could currently be sleeping because it's waiting for something and then go into the run state. Keep all this in mind to identify a currently active top CPU resource. Alternatively, the snapshot technique as shown in the Oracle route could be used for identifying. Finally, the administrator could use the values used from the Oracle route and the values used from the OS route to zero in on the heavy resource user.n |
After the pid is identified, you can find the sid of the process and can further use the Oracle view V$SQLTEXT to find what the process is currently executing. This can be done by the script in Listing 22.13.
Listing 22.13 Scripts to Obtain the pid from the sid and to Obtain the SQL Statement the Session is ExecutingREM Find the sid of the Unix process using the process id Select sid From v$session a, v$process b Where a.paddr = b.addr And b.spid = <pid> ; REM Find the SQL the user is currently executing Select sql_text From v$sqltext a , v$session b Where a.hash_value = b.sql_hash_value And b.sid = <sid> Order By piece;
When data is required by a particular user program, the OS will first check to see whether this data is currently available in the cache and, if so, it will be used. If the data is not found in the cache, this data will have to be read in from the disk. The process of reading data from the disk is highly expensive when compared to reading data from the cache, because the memory is much faster as compared to disk access. Disk operations greatly retard the throughput of processes.