Zrovna jsem řešil u zákazníka problém s databází, že je prý pomalá. Dohledal jsem příčinu pomocí dalších dotazů, ale i jeden z ukazatelů, jako je IOPS mě nasměroval, že je zvýšená intenzita přístupů k diskům.
Pak je stačí zjistit, který dotaz daný workload generuje. Dotaz jsem získal zde.
SQL> spool off SQL> set serveroutput off SQL> set termout on SQL> select 'orcl', sample_hour, (rps+wps) IOPS 2 from ( 3 with snaps as ( 4 select hiof1.snap_id, sum(hiof1.value) reads, sum(hiof2.value) writes 5 from sys.WRH$_SYSSTAT HIOF1, sys.WRH$_SYSSTAT HIOF2 6 where HIOF1.stat_id in (select stat_id from v$statname where name like '%physical read total IO%') 7 and HIOF2.stat_id in (select stat_id from v$statname where name like '%physical write total IO%') 8 and HIOF1.snap_id=hiof2.snap_id 9 group by hiof1.snap_id 10 ), 11 my_snaps as 12 (select snap_id, instance_number, begin_interval_time, end_interval_time, 13 extract(second from (end_interval_time-begin_interval_time))+ 14 (extract(minute from (end_interval_time-begin_interval_time))*60)+ 15 (extract(hour from (end_interval_time-begin_interval_time))*60*60) seconds 16 from dba_hist_snapshot) 17 select s1.snap_id snap_1, s2.snap_id snap_2, to_date(to_char(begin_interval_time,'MM/DD/YYYY HH24'),'MM/DD/YYYY HH24') sample_hour, sum(s2.reads-s1.reads) reads, sum(s2.writes-s1.writes) 18 trunc(sum(s2.reads-s1.reads)/sum(seconds)) rps, trunc(sum(s2.writes-s1.writes)/sum(seconds)) wps 19 from snaps s1, snaps s2, my_snaps ms 20 where s1.snap_id=ms.snap_id 21 and s1.snap_id=(s2.snap_id-1) 22 and (s2.reads-s1.reads)>1 23 and (s2.writes-s1.writes)>1 24 group by s2.snap_id, to_date(to_char(begin_interval_time,'MM/DD/YYYY HH24'),'MM/DD/YYYY HH24'), s1.snap_id 25 ) order by 3 desc; 'ORC SAMPLE_HO IOPS ---- --------- ---------- orcl 21-OCT-15 1341 --> Jak je vidět, problém gradoval 21.10.2015 a trvá dodnes, tedy 23.10. orcl 23-OCT-15 1318 orcl 21-OCT-15 1298 orcl 22-OCT-15 1239 orcl 22-OCT-15 1195 orcl 20-OCT-15 1187 --> 20.10. se něco spustilo (refresh view). To samé se pak spustilo 21.10. a začalay být problémy. orcl 21-OCT-15 1180 orcl 20-OCT-15 1179 orcl 20-OCT-15 1154 orcl 21-OCT-15 1154 orcl 20-OCT-15 1149 orcl 22-OCT-15 1148 orcl 20-OCT-15 1147 ..... Předtím vše v pořádku. orcl 16-OCT-15 79 orcl 16-OCT-15 78 orcl 20-OCT-15 72 orcl 19-OCT-15 49 orcl 19-OCT-15 45 orcl 17-OCT-15 43 orcl 16-OCT-15 42 orcl 16-OCT-15 37 orcl 18-OCT-15 32 orcl 19-OCT-15 30 orcl 19-OCT-15 26 orcl 16-OCT-15 26 orcl 20-OCT-15 24 orcl 20-OCT-15 24 orcl 19-OCT-15 24 Pro podrobný výpis jsem použil script z těchto stránek od Jamese Koopmanna. Small Small Total Small Small Large Large Total Large Large Total Total Read Write Small Read Write Read Write Large Read Write Read Written Total END_TIME IOPS IOPS IOPS I/O% I/O% IOPS IOPS IOPS I/O% I/O% MBPS MBPS MBPS ------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 20-OCT-15 04.00.30.271 AM 26.538 430.929 457.467 5.801 94.199 7.389 25.825 33.214 22.246 77.754 .944 6.489 7.433 20-OCT-15 05.01.00.114 AM 75.303 501.023 576.326 13.066 86.934 77.651 27.281 104.932 74.001 25.999 10.238 7.882 18.121 20-OCT-15 06.00.42.268 AM .783 1.101 1.885 41.564 58.436 .041 .304 .345 11.964 88.036 .012 .018 .031 20-OCT-15 07.00.27.391 AM 1.047 2.671 3.718 28.157 71.843 .497 .489 .986 50.41 49.59 .069 .045 .114 20-OCT-15 08.01.08.834 AM 1.978 1.149 3.128 63.254 36.746 .635 .369 1.004 63.249 36.751 .085 .019 .104 20-OCT-15 09.00.48.207 AM 20.738 13.127 33.864 61.238 38.762 38.943 1.927 40.871 95.284 4.716 4.744 .31 5.053 20-OCT-15 10.00.14.579 AM 67.917 384.534 452.452 15.011 84.989 56.253 19.048 75.301 74.705 25.295 7.348 6.559 13.907 20-OCT-15 11.00.30.422 AM 2.898 19.967 22.865 12.675 87.325 1.009 1.437 2.446 41.266 58.734 .087 .408 .496 20-OCT-15 12.00.50.327 PM 36.012 469.878 505.89 7.119 92.881 38.273 16.132 54.405 70.348 29.652 4.938 7.645 12.583 20-OCT-15 01.00.35.559 PM 6.394 3.561 9.954 64.23 35.77 .067 1.045 1.112 6.02 93.98 .058 .115 .173 20-OCT-15 02.01.06.199 PM 135.535 586.004 721.539 18.784 81.216 54.094 19.075 73.169 73.93 26.07 7.375 9.382 16.757 20-OCT-15 03.00.33.915 PM 85.218 76.566 161.784 52.674 47.326 1034.849 3.951 1038.8 99.62 .38 127.037 1.208 128.245 20-OCT-15 04.01.07.274 PM 81.685 1.288 82.973 98.448 1.552 1049.799 1.271 1051.071 99.879 .121 129.751 .121 129.873 20-OCT-15 05.00.41.224 PM 100.923 1.133 102.055 98.89 1.11 1065.061 .761 1065.822 99.929 .071 131.462 .02 131.483 20-OCT-15 06.00.23.472 PM 77.382 1.157 78.54 98.527 1.473 1068.823 .611 1069.434 99.943 .057 131.833 .02 131.853 20-OCT-15 07.01.05.769 PM .569 1.147 1.716 33.147 66.853 1080.214 .616 1080.83 99.943 .057 133.808 .02 133.828 20-OCT-15 08.00.48.032 PM .528 1.148 1.676 31.529 68.471 1080.211 .692 1080.903 99.936 .064 133.808 .02 133.828 20-OCT-15 09.00.33.249 PM .601 1.159 1.76 34.147 65.853 1079.733 .574 1080.308 99.947 .053 133.748 .02 133.768 20-OCT-15 10.00.15.466 PM .992 1.147 2.139 46.379 53.621 1077.054 .593 1077.647 99.945 .055 133.416 .02 133.436 20-OCT-15 11.00.51.152 PM 88.065 44.14 132.206 66.612 33.388 1028.288 9.648 1037.936 99.07 .93 125.457 .665 126.122 21-OCT-15 12.00.24.416 AM .623 1.15 1.773 35.127 64.873 1081.37 .602 1081.973 99.944 .056 133.951 .021 133.972 21-OCT-15 01.01.00.681 AM 1.247 1.173 2.42 51.534 48.466 1078.207 .623 1078.831 99.942 .058 133.562 .021 133.583