Oracle „log file sync“ – Zeiten per Script auswerten

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>