Linux SAR Command Usage For Oracle DBA
SAR-SYSTEM ACTIVITY REPORT
- sar displays CPU, memory, disk I/O, and network usage, both
current and historical - It uses “/var/log/sa/saXX” file to read historical data. XX is
the day of the month. - The data is recorded for each day in a file named sa<nn> where <nn> is the two digit day of the month. For instance the file sa07 holds the data for the date 07th of that month. This data can be queried by the command sar.
Using sar command is best way check in case of any performance issue report
- RAM
- Swap
- Buffer/Cache
- Run Queue
- CPU
- Network
sar command to get the metrics for the components: -P Specific CPU(s) -d Disks -r Memory -B Paging -W Swapping -n Network
[oracle@DEVRAC1 ~]$ sar Linux 2.6.32-431.el6.x86_64 (DEVRAC1) 10/14/2018 _x86_64_ (8 CPU) 12:00:01 AM CPU %user %nice %system %iowait %steal %idle 12:10:01 AM all 18.83 0.04 3.84 17.88 0.00 59.42 12:20:01 AM all 11.26 0.04 3.74 7.02 0.00 77.94 12:30:01 AM all 10.68 0.03 3.83 8.39 0.00 77.08 The output shows the CPU related metrics collected in 10 minute intervals. The columns mean CPU -The CPU identifier; “all” means all the CPUs %user -The percentage of CPU used for user processes. Oracle processes come under this category. %nice -The %ge of CPU utilization while executing under nice priority %system -The %age of CPU executing system processes %iowait -The %age of CPU waiting for I/O %idle -The %age of CPU idle waiting for work
sar -u The CPU Report [oracle@DEVRAC1 ~]$ sar -u Linux 2.6.32-431.el6.x86_64 (DEVRAC1) 10/14/2018 _x86_64_ (8 CPU) 12:00:01 AM CPU %user %nice %system %iowait %steal %idle 12:10:01 AM all 18.83 0.04 3.84 17.88 0.00 59.42 12:20:01 AM all 11.26 0.04 3.74 7.02 0.00 77.94 The sar –u command is very useful for seeing the overall CPU consumption over time. In the example that follows, I execute sar –u to see the state of the CPU. CPU time can be allocated into the following four sections: user mode, system mode, waiting on I/O, and idle. sar -u Displays CPU usage for the current day that was collected until that point. sar -u 1 3 Displays real time CPU usage every 1 second for 3 times. sar -u ALL Same as “sar -u” but displays additional fields. sar -u ALL 1 3 Same as “sar -u 1 3” but displays additional fields. sar -u -f /var/log/sa/sa10 Displays CPU usage for the 10day of the month from the sa10 file.
03:00:01 AM CPU %user %nice %system %iowait %idle 03:10:01 AM all 44.99 0.00 1.27 2.85 40.89 03:20:01 AM all 44.97 0.00 1.20 2.70 41.13 03:30:01 AM all 45.80 0.00 1.39 3.00 39.81 03:40:01 AM all 40.26 0.00 1.25 3.55 44.93 This tells a different story: the system was loaded by some user processes between 3:00 and 3:40. Perhaps an expensive query was executing; or perhaps an RMAN job was running, consuming all that CPU. This is where the sar command is useful--it replays the recorded data showing the data as of a certain time, not now. This is exactly what you wanted to accomplish the three objectives outlined in the beginning of this section: getting historical data, finding usage patterns and understanding trends.
CPU Usage of Individual CPU or Core (sar -P) If you have 4 Cores on the machine and would like to see what the individual cores are doing, do the following. “-P ALL” indicates that it should displays statistics for ALL the individual Cores. In the following example under “CPU” column 0, 1, 2, and 3 indicates the corresponding CPU core numbers. [oracle@DEVRAC1 ~]$ sar -P ALL 1 1 Linux 2.6.32-431.el6.x86_64 (DEVRAC1) 10/14/2018 _x86_64_ (8 CPU) 09:18:28 AM CPU %user %nice %system %iowait %steal %idle 09:18:29 AM all 9.65 0.00 3.22 2.45 0.00 84.68 09:18:29 AM 0 33.33 0.00 7.53 1.08 0.00 58.06 09:18:29 AM 1 8.25 0.00 3.09 5.15 0.00 83.51 09:18:29 AM 2 6.25 0.00 2.08 5.21 0.00 86.46 09:18:29 AM 4 9.00 0.00 4.00 4.00 0.00 83.00 09:18:29 AM 5 7.14 0.00 4.08 0.00 0.00 88.78 09:18:29 AM 6 5.05 0.00 3.03 0.00 0.00 91.92 09:18:29 AM 7 5.05 0.00 1.01 0.00 0.00 93.94 Average: CPU %user %nice %system %iowait %steal %idle Average: all 9.65 0.00 3.22 2.45 0.00 84.68 Average: 0 33.33 0.00 7.53 1.08 0.00 58.06 Average: 1 8.25 0.00 3.09 5.15 0.00 83.51 Average: 2 6.25 0.00 2.08 5.21 0.00 86.46 Average: 3 4.08 0.00 2.04 5.10 0.00 88.78 Average: 4 9.00 0.00 4.00 4.00 0.00 83.00 Average: 5 7.14 0.00 4.08 0.00 0.00 88.78 Average: 6 5.05 0.00 3.03 0.00 0.00 91.92 Average: 7 5.05 0.00 1.01 0.00 0.00 93.94 Did you notice the “all” value under CPU? It means the stats were rolled up for all the CPUs. In a single processor system that is fine; but in multi-processor systems you may want to get the stats for individual CPUs as well as an aggregate one. The -P ALL option accomplishes that sar -P ALL Displays CPU usage broken down by all cores for the current day. sar -P ALL 1 3 Displays real time CPU usage for ALL cores every 1 second for 3 times (broken down by all cores). sar -P 1 Displays CPU usage for core number 1 for the current day. sar -P 1 1 3 Displays real time CPU usage for core number 1, every 1 second for 3 times. sar -P ALL -f /var/log/sa/sa10 Displays CPU usage broken down by all cores for the 10day day of the month from sa10 file
Overall I/O Activities (sar -b) This reports I/O statistics. “1 3” reports for every 1 seconds a total of 3 times. Following fields are displays in the example below. tps – Transactions per second (this includes both read and write) rtps – Read transactions per second wtps – Write transactions per second bread/s – Bytes read per second bwrtn/s – Bytes written per second [oracle@DEVRAC1 ~]$ sar -b 1 3 Linux 2.6.32-431.el6.x86_64 (DEVRAC1) 10/14/2018 _x86_64_ (8 CPU) 10:31:47 AM tps rtps wtps bread/s bwrtn/s 10:31:48 AM 236.00 52.00 184.00 1540.00 2786.00 10:31:49 AM 121.21 88.89 32.32 2719.19 870.71 10:31:50 AM 216.67 43.75 172.92 1270.83 2420.83 Average: 191.19 61.69 129.49 1848.14 2024.41 Following are few variations: sar -b sar -b 1 3 sar -b -f /var/log/sa/sa10 Note: Use “sar -v” to display number of inode handlers, file handlers, and pseudo-terminals used by the system.
Report network statistics This reports various network statistics. For example: number of packets received (transmitted) through the network card, statistics of packet failure etc.,. “1 3” reports for every 1 seconds a total of 3 times. [oracle@PTCDEVDBRAC1 ~]$ sar -n DEV | more Linux 2.6.32-431.el6.x86_64 (PTCDEVDBRAC1.pasa.pas.local) 10/14/2018 _x86_64_ (8 CPU) 12:0:1 AM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 12:10:01AM lo 113.96 113.96 55.20 55.20 0.00 0.00 0.00 12:10:01AM eth0 1613.80 1517.74 832.23 438.66 0.00 0.00 3.80 12:10:01AM eth1 717.00 1438.19 510.27 1414.79 0.00 0.00 0.76 Average: lo 206.72 206.72 66.70 66.70 0.00 0.00 0.00 Average: eth0 1638.48 1622.38 719.31 542.88 0.00 0.00 4.01 Average: eth1 669.03 681.12 537.74 594.64 0.00 0.00 0.80 To get the historical network statistics, you use the -n option: KEYWORD can be one of the following: DEV – Displays network devices vital statistics for eth0, eth1, etc., EDEV – Display network device failure statistics NFS – Displays NFS client activities NFSD – Displays NFS server activities SOCK – Displays sockets in use for IPv4 IP – Displays IPv4 network traffic EIP – Displays IPv4 network errors ICMP – Displays ICMPv4 network traffic EICMP – Displays ICMPv4 network errors TCP – Displays TCPv4 network traffic ETCP – Displays TCPv4 network errors UDP – Displays UDPv4 network traffic SOCK6, IP6, EIP6, ICMP6, UDP6 are for IPv6 ALL – This displays all of the above information. The output will be very long.
Memory Free and Used [oracle@DEVRAC1 ~]$ sar -r Linux 2.6.32-431.el6.x86_64 (DEVRAC1) 10/14/2018 _x86_64_ (8 CPU) 12:0AM kbmemfree kbmemused %memused kbbuffers kbcached kbcommit %commit 12:10:01 AM 13671388 118418888 89.65 962548 97559656 52549412 39.78 12:20:01 AM 13769904 118320372 89.58 962564 97566608 52239992 39.55 Average: 13546921 118543355 89.74 962796 97590655 52433437 39.70 This shows the CPU identifier (starting with 0) and the stats for each. At the very end of the output you will see the average of runs against each CPU. The command sar is not only fro CPU related stats. It’s useful to get the memory related stats as well. The -r option shows the extensive memory utilization. kbmemfree-The free memory available in KB at that time kbmemused-The memory used in KB at that time %memused-%age of memory used kbbuffers-This %age of memory was used as buffers kbcached-This %age of memory was used as cache kbswpfree-The free swap space in KB at that time kbswpused-The swap space used in KB at that time %swpused-The %age of swap used at that time kbswpcad-The cached swap in KB at that time Following are few variations: sar -r sar -r 1 3 sar -r -f /var/log/sa/sa10
sar 5 2 [oracle@DEVRAC1 ~]$ sar 5 2 Linux 2.6.32-431.el6.x86_64 (DEVRAC1) 10/14/2018 _x86_64_ (8 CPU) 09:05:52 AM CPU %user %nice %system %iowait %steal %idle 09:05:57 AM all 13.06 0.03 3.67 11.12 0.00 72.12 09:06:02 AM all 10.86 0.03 3.99 6.12 0.00 79.00 Average: all 11.96 0.03 3.83 8.63 0.00 75.56 It can also display data in real time, similar to vmstat or mpstat. To get the data every 5 seconds for 2 times,