Hi Experts,
I have an issue with performance of oracle, the db sits on EBS volumes on and aws EC2 instance. I have captured the iostat data and feed it to iostat_plotter to forward it to the DB administrators. However what I'm trying to understand is the variable avgrq-sz, it is quite in consistent.
The system is running RHEL 6.4 64 bit, Oracle 11g. The block size is 4K .Following is the extract for two devices which are of interest
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
xvdj 0.00 80.55 15.18 59.58 1155.01 1121.04 30.45 1.10 14.74 0.70 5.20
xvdj 0.00 0.00 3.00 2.00 48.00 16.00 12.80 0.03 5.80 5.60 2.80
xvdj 0.00 0.00 14.00 162.00 704.00 1296.00 11.36 0.34 1.94 0.87 15.30
xvdj 0.00 0.00 8.00 4.00 304.00 32.00 28.00 0.15 12.25 5.50 6.60
xvdj 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.00 1.00 1.00 0.10
xvdj 0.00 114.00 2.00 147.00 96.00 2088.00 14.66 0.86 5.77 0.58 8.60
xvdj 0.00 0.00 0.00 33.00 0.00 264.00 8.00 0.05 1.42 0.48 1.60
xvdj 0.00 0.00 4.00 6.00 64.00 48.00 11.20 0.08 7.60 7.00 7.00
xvdj 0.00 0.00 6.00 60.00 96.00 480.00 8.73 0.17 2.56 1.80 11.90
xvdj 0.00 0.00 4.00 3.00 64.00 24.00 12.57 0.10 15.00 14.71 10.30
and
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
xvdk 0.00 0.09 38.83 28.48 2696.19 228.51 43.45 0.35 5.24 1.29 8.67
xvdk 0.00 0.00 187.00 3.00 5440.00 24.00 28.76 2.65 14.23 5.26 100.00
xvdk 0.00 0.00 330.00 14.00 12592.00 112.00 36.93 3.54 10.15 2.91 100.00
xvdk 0.00 0.00 270.00 0.00 9968.00 0.00 36.92 4.01 14.99 3.70 100.00
xvdk 0.00 0.00 269.00 0.00 8336.00 0.00 30.99 3.27 12.20 3.72 100.00
xvdk 0.00 0.00 249.00 0.00 8496.00 0.00 34.12 3.50 13.47 4.02 100.00
xvdk 0.00 0.00 261.00 0.00 8432.00 0.00 32.31 3.35 13.19 3.83 100.00
xvdk 0.00 0.00 255.00 3.00 8944.00 24.00 34.76 3.56 13.94 3.88 100.00
xvdk 0.00 0.00 255.00 12.00 7872.00 96.00 29.84 3.04 11.38 3.75 100.10
xvdk 0.00 0.00 281.00 0.00 9424.00 0.00 33.54 3.03 10.79 3.56 99.90
These samples are one sec apart. My first guess is that the data sets which are being processed by oracle need reindexing.
Any suggestions would be highly appreciated