Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 583

[oracle@ssky1l4p1 ~]$ mpstat -P ALL
Linux 2.6.18-128.el5 (ssky1l4p1) 11/15/2013
12:10:27 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
12:10:27 PM all 5.88 0.01 1.30 2.93 0.05 0.22 0.00 89.61 5684.01
12:10:27 PM 0 2.57 0.01 0.68 0.07 0.00 0.01 0.00 96.67 1000.20
12:10:27 PM 1 6.35 0.01 1.40 10.15 0.04 0.15 0.00 81.90 100.27
12:10:27 PM 2 3.03 0.01 0.75 3.59 0.01 0.05 0.00 92.56 38.04
12:10:27 PM 3 5.92 0.01 1.17 7.17 0.03 0.12 0.00 85.58 64.51
12:10:27 PM 4 1.83 0.01 0.56 0.69 0.00 0.02 0.00 96.90 18.18
12:10:27 PM 5 6.12 0.00 1.34 3.29 0.00 0.10 0.00 89.14 3.17
12:10:27 PM 6 1.89 0.01 0.55 0.72 0.00 0.01 0.00 96.81 17.04
12:10:27 PM 7 6.20 0.01 1.31 5.26 0.01 0.11 0.00 87.10 35.39
12:10:27 PM 8 2.45 0.01 0.63 0.05 0.00 0.01 0.00 96.85 0.00
12:10:27 PM 9 4.64 0.01 0.87 1.48 0.00 0.03 0.00 92.97 26.45
12:10:27 PM 10 4.22 0.01 1.83 0.37 0.00 0.04 0.00 93.53 1.48
12:10:27 PM 11 8.85 0.01 2.33 1.58 0.07 0.33 0.00 86.84 657.63
12:10:27 PM 12 2.96 0.01 0.86 0.10 0.00 0.01 0.00 96.06 1.69
12:10:27 PM 13 19.36 0.00 3.27 7.76 0.34 1.52 0.00 67.75 1531.39
12:10:27 PM 14 3.01 0.01 0.69 0.09 0.00 0.01 0.00 96.19 8.24
12:10:27 PM 15 14.61 0.01 2.62 4.59 0.22 0.96 0.00 76.99 2180.31

[mvallath@prddb1 mysql]$ sar -u 2 5
Linux 2.6.18-128.el5 (prddb1) 11/15/2010
01:07:03 PM CPU %user %nice %system %iowait %steal %idle
01:07:05 PM all 11.03 0.00 0.88 6.12 0.00 81.97
01:07:07 PM all 12.43 0.00 1.81 6.28 0.00 79.48
01:07:09 PM all 12.37 0.00 2.53 6.50 0.00 78.60
01:07:11 PM all 13.78 0.00 1.84 6.22 0.00 78.16
01:07:13 PM all 14.58 0.00 2.65 6.03 0.00 76.73
Average: all 12.84 0.00 1.94 6.23 0.00 78.99

In the preceding output, -u 2 5 reports CPU utilization. The following values are displayed:
•

%user : Percentage of CPU utilization that occurred while executing at the user level
(application)

•

%nice : Percentage of CPU utilization that occurred while executing at the user level with nice
priority

•

%system : Percentage of CPU utilization that occurred while executing at the system level
(kernel)

•

%iowait : Percentage of time that the CPU or CPUs were idle, during which the system had an
outstanding disk I/O request

•

%idle : Percentage of time that the CPU or CPUs were idle and the system did not have an
outstanding disk I/O request

While tuning, database systems latches are the least of anyone's concerns. Very little attention is given to tuning
latches. Most of the time, resource contention and latch-related wait times would reduce when overall wait times are
reduced, certain types of latches cannot be ignored.
As discussed earlier, high ratio value between gets and misses should be indicators to give closer attention to
the latch-related wait numbers. The LOCATION column in V$LATCH_MISSES view provides better indication of the area
where the latch contention occurs.