Um eine schnelle Übersicht über die log file sync Zeiten im Oracle zu erhalten bietet sich folgendes kleines Script an. Es gibt die Durchschnittswerte auf Stundenbasis für die letzten 5 Tage aus („sysdate -5“).
Zuvor sollte noch kurz die Ausgabe formatiert werden:
set pages 200 lines 200
Das Script sieht wie folgt aus:
select time, round(time_delta/1e3/nullif(waits_delta,0), 1) avg_wait_ms from ( select sn.snap_id, sn.begin_interval_time time, e.total_waits - lag(e.total_waits) over (partition by e.event_name order by e.snap_id) waits_delta, e.time_waited_micro - lag(e.time_waited_micro) OVER (PARTITION BY e.event_name ORDER BY e.snap_id) time_delta from dba_hist_system_event e, dba_hist_snapshot sn where e.snap_id = sn.snap_id AND e.event_name = 'log file sync' and sn.end_interval_time > sysdate -5 ) ev WHERE ev.time_delta > 0 ;
Der Output des Scriptes sieht dann in etwa so aus:
TIME AVG_WAIT_MS --------------------------------------------------------------------------- ----------- 13-MAY-13 12.01.00.164 PM 13.2 13-MAY-13 01.00.05.886 PM 11.1 13-MAY-13 02.00.12.186 PM 6.9 13-MAY-13 03.00.18.216 PM 4.6 13-MAY-13 04.00.24.174 PM 4.4 13-MAY-13 05.00.29.968 PM 3.1 13-MAY-13 06.00.35.817 PM 3.3 13-MAY-13 07.00.41.605 PM 42.4 13-MAY-13 08.00.47.708 PM 5.1 13-MAY-13 09.00.54.159 PM 7.4 13-MAY-13 10.01.00.172 PM 12 13-MAY-13 11.00.06.965 PM 3.1 14-MAY-13 12.00.13.132 AM 2.7 14-MAY-13 01.00.19.202 AM 3.6 14-MAY-13 02.00.25.155 AM 2.3 14-MAY-13 03.00.31.179 AM 2.9 14-MAY-13 04.00.36.880 AM 4.7 14-MAY-13 05.00.42.555 AM 6 14-MAY-13 06.00.49.718 AM 3.7 14-MAY-13 07.00.55.602 AM 4.2 14-MAY-13 08.00.01.303 AM 2.6 14-MAY-13 09.00.07.170 AM 3.6 14-MAY-13 10.00.13.302 AM 11.6 14-MAY-13 11.00.20.766 AM 7.6 14-MAY-13 12.00.27.594 PM 10.1 14-MAY-13 01.00.34.332 PM 7.1 14-MAY-13 02.00.41.516 PM 6.2 14-MAY-13 03.00.47.981 PM 8.4 14-MAY-13 04.00.54.442 PM 3.3 14-MAY-13 05.00.00.538 PM 3.8 14-MAY-13 06.00.06.972 PM 4 14-MAY-13 07.00.13.194 PM 4.7 14-MAY-13 08.00.19.517 PM 5.5 14-MAY-13 09.00.27.302 PM 4.2 14-MAY-13 10.00.33.796 PM 5.2 14-MAY-13 11.00.40.450 PM 10.9 15-MAY-13 12.00.47.265 AM 6.6 15-MAY-13 01.00.54.489 AM 9.9 15-MAY-13 02.00.01.543 AM 6.1 15-MAY-13 03.00.08.127 AM 4.7 15-MAY-13 04.00.15.281 AM 11.2 15-MAY-13 05.00.22.374 AM 8.3 15-MAY-13 06.00.29.457 AM 7.8 15-MAY-13 07.00.36.206 AM 7.8 15-MAY-13 08.00.43.069 AM 28.2 15-MAY-13 09.00.49.723 AM 6.2 15-MAY-13 10.00.56.938 AM 7.2 15-MAY-13 11.00.04.146 AM 5.6 15-MAY-13 12.00.11.010 PM 3.4 15-MAY-13 01.00.17.308 PM 5.9 15-MAY-13 02.00.23.705 PM 11.8 15-MAY-13 03.00.30.357 PM 32.1 15-MAY-13 04.00.36.355 PM 16.6 15-MAY-13 05.00.42.325 PM 5.6 15-MAY-13 06.00.48.889 PM 3.6 15-MAY-13 07.00.55.203 PM 4.9 15-MAY-13 08.00.01.304 PM 5.7 15-MAY-13 09.00.07.618 PM 32.9 15-MAY-13 10.00.15.555 PM 16.6 15-MAY-13 11.00.22.705 PM 4.1 16-MAY-13 12.00.28.937 AM 5.9 16-MAY-13 01.00.35.552 AM 7.4 16-MAY-13 02.00.42.304 AM 7.5 16-MAY-13 03.00.48.460 AM 20.7 16-MAY-13 04.00.57.032 AM 44 16-MAY-13 05.00.04.083 AM 7.6 16-MAY-13 06.00.10.266 AM 6.9 16-MAY-13 07.00.16.651 AM 5.2 16-MAY-13 08.00.22.694 AM 4.8 16-MAY-13 09.00.28.966 AM 5.8 16-MAY-13 10.00.35.630 AM 6.2 16-MAY-13 11.00.41.654 AM 4.6 16-MAY-13 12.00.48.545 PM 4.4 16-MAY-13 01.00.54.599 PM 3.8 16-MAY-13 02.01.00.668 PM 3.1 16-MAY-13 03.00.07.007 PM 5.2 16-MAY-13 04.00.13.292 PM 2.6 16-MAY-13 05.00.19.485 PM 2 16-MAY-13 06.00.25.329 PM 2.6 16-MAY-13 07.00.31.156 PM 2.3 16-MAY-13 08.00.36.604 PM 3.4 16-MAY-13 09.00.42.579 PM 3.1 16-MAY-13 10.00.49.170 PM 2.1 16-MAY-13 11.00.54.843 PM 2.6 17-MAY-13 12.01.00.568 AM 2.1 17-MAY-13 01.00.06.452 AM 4.5 17-MAY-13 02.00.12.433 AM 2.3 17-MAY-13 03.00.18.299 AM 4.6 17-MAY-13 04.00.24.482 AM 3.3 17-MAY-13 05.00.30.433 AM 3.2 17-MAY-13 06.00.36.403 AM 2.9 17-MAY-13 07.00.42.461 AM 2.5 17-MAY-13 08.00.48.437 AM 2.4 17-MAY-13 09.00.54.327 AM 3.1 17-MAY-13 10.01.00.221 AM 2.4 17-MAY-13 11.00.06.150 AM 2.3 17-MAY-13 12.00.12.099 PM 2.1 17-MAY-13 01.00.18.282 PM 2.8 17-MAY-13 02.00.24.623 PM 2.8 17-MAY-13 03.00.30.683 PM 2.2 17-MAY-13 04.00.36.572 PM 3.4 17-MAY-13 05.00.42.618 PM 2.9 17-MAY-13 06.00.48.435 PM 3.4 17-MAY-13 07.00.54.943 PM 3 17-MAY-13 08.00.01.103 PM 3.3 17-MAY-13 09.00.07.998 PM 5 17-MAY-13 10.00.14.986 PM 3 17-MAY-13 11.00.21.832 PM 3.5 18-MAY-13 12.00.28.601 AM 3.4 18-MAY-13 01.00.35.768 AM 5.1 18-MAY-13 02.00.43.014 AM 5 18-MAY-13 03.00.49.678 AM 4.8 18-MAY-13 04.00.56.621 AM 3.5 18-MAY-13 05.00.03.262 AM 3.5 18-MAY-13 06.00.09.528 AM 3.4 18-MAY-13 07.00.16.410 AM 3.1 18-MAY-13 08.00.22.725 AM 2.2 18-MAY-13 09.00.28.556 AM 3.5 18-MAY-13 10.00.34.560 AM 3.1 119 rows selected. SQL>