(Archived) Redo Log Peak Loads

Bei Einsatz einer Standby Datenbank oder von Oracle Streams ist es sinnvoll, einen Überblick über Peak Loads zu erhalten. Die folgende Abfrage greift auf die View v$archived_log zu, um eine Übersicht über die Peaks der letzten 30 Tage zu generieren.

Oracle Datenbanken: Archived Redo Logs Peak Load
SELECT * 
FROM 
   (SELECT 
        TO_CHAR (b.first_time,'dd.mm.yyyy hh24:mi:ss')    AS "Datum",
        a.blocks*a.block_size                             AS "Redo Kbytes",
        ROUND ((b.first_time - a.first_time)*24*60*60)    AS "Sekunden",
        ROUND ((a.blocks*a.block_size*8/1000/1000)/
               (b.first_time - a.first_time)/(24*60*60))  AS "Redo Mbps"
    FROM 
        v$archived_log a,v$archived_log  b
    WHERE 
        a.thread#                = b.thread#
        AND a.dest_id            = b.dest_id
        AND a.dest_id            = 1
        AND a.SEQUENCE#          = b.SEQUENCE# -1
        AND a.resetlogs_change#  = b.resetlogs_change#
        AND a.first_time         > SYSDATE - 30
        AND (b.first_time - a.first_time) > 0
        ORDER BY "Redo Mbps" DESC
   )
WHERE ROWNUM <= 20
/

 

Das Ergebnis kann beispielsweise wie folgt aussehen: 

Datum               Redo Kbytes   Sekunden  Redo Mbps
------------------- ----------- ---------- ----------
04.01.2010 03:02:10   534664704         24        178
21.12.2009 01:16:13   535636480         25        171
28.12.2009 14:54:45   535099392         25        171
28.12.2009 14:34:57   536238592         26        165
21.12.2009 03:02:52   534230016         26        164
21.12.2009 01:03:00   534757888         27        158
04.01.2010 03:02:37   533809152         27        158
28.12.2009 14:35:24   534223872         27        158
29.12.2009 03:01:05   534217216         27        158
26.12.2009 03:02:28   535873536         28        153
04.01.2010 01:25:44   536867328         28        153
28.12.2009 01:03:18   533700608         28        152
04.01.2010 01:26:14   531593216         30        142
25.12.2009 03:03:01   529756672         30        141
21.12.2009 03:02:26   535924224         31        138
04.01.2010 01:02:36   536068608         31        138
28.12.2009 14:55:16   533797888         31        138
28.12.2009 14:35:55   535196160         31        138
21.12.2009 01:16:44   529783808         31        137
02.01.2010 03:03:59   529860608         31        137

 

Die folgende Abfrage greift auf das Automatic Workload Repository (AWR) zu, um Informationen zum Redo Log Load zu ermitteln:

Oracle-Datenbanken: Redo Log Informationen aus dem AWR
COLUMN "Snap-Zeit" FORMAT a20
COLUMN "Interval"  FORMAT a20
COLUMN "Redo Size" FORMAT 999,999,999,990
 
 SELECT
       a.snap_id                            AS "Snap ID",
       TO_CHAR(b.BEGIN_INTERVAL_TIME,  
              'dd.mm.yyyy hh24:mi')     AS "Interval",
       (a.VALUE - c.VALUE)    AS "Redo Size"
 FROM  dba_hist_sysstat  a,
       dba_hist_snapshot b,
       dba_hist_sysstat  c
 WHERE
       a.snap_id         = b.snap_id
 AND   a.snap_id -1      = c.snap_id
 AND   a.instance_number = 1
 AND   a.instance_number = b.instance_number
 AND   b.instance_number = c.instance_number
 AND   a.dbid            = b.dbid
 AND   b.dbid            = c.dbid
 AND   a.stat_name       IN ('redo size')
 AND   a.stat_name       = c.stat_name
 AND   a.VALUE           > c.VALUE
 ORDER BY a.snap_id
/

 

Die Ausgabe kann beispielsweise wie folgt aussehen: 

   Snap ID Interval                    Redo Size
---------- -------------------- ----------------
     11374 28.12.2009 23:00          408,709,780
     11375 29.12.2009 00:00          800,642,644
     11376 29.12.2009 01:00          699,515,428
     11377 29.12.2009 02:00          183,565,456
     11378 29.12.2009 03:00        1,342,516,976
     11379 29.12.2009 04:00          155,913,976
     11380 29.12.2009 05:00          224,559,236
     11381 29.12.2009 06:00          241,675,768
     11382 29.12.2009 07:00          186,273,168
     11383 29.12.2009 08:00          240,554,832
     11384 29.12.2009 09:00          685,414,528
     11385 29.12.2009 10:00          660,091,588
     11386 29.12.2009 11:00          344,466,012
     11387 29.12.2009 12:00        2,198,059,836
     11388 29.12.2009 13:00        2,210,343,352
     11389 29.12.2009 14:00          370,614,532
     11390 29.12.2009 15:00          353,510,124
     11391 29.12.2009 16:00          398,872,972
     11392 29.12.2009 17:00          297,910,440
     11393 29.12.2009 18:00          201,283,512
     11394 29.12.2009 19:00          156,506,980
     11395 29.12.2009 20:00          128,041,948
     11396 29.12.2009 21:00          492,715,320
     11397 29.12.2009 22:00        6,470,754,640
     11398 29.12.2009 23:00          432,756,468
     11399 30.12.2009 00:00          376,624,544
     11400 30.12.2009 01:00          936,272,004
     11401 30.12.2009 02:00          172,786,400
     11402 30.12.2009 03:00        2,542,111,128
     11403 30.12.2009 04:00          207,638,332
     11404 30.12.2009 05:00          220,820,300
     11405 30.12.2009 06:00          266,422,600
     11406 30.12.2009 07:00          200,250,964
     11407 30.12.2009 08:00          258,164,664
     11408 30.12.2009 09:00          731,884,948
     11409 30.12.2009 10:00          330,421,436
     11410 30.12.2009 11:00          362,577,384
     11411 30.12.2009 12:00        2,029,942,108
     11412 30.12.2009 13:00        2,618,423,208
     11413 30.12.2009 14:00          358,792,776
     11414 30.12.2009 15:00          309,413,684
     11415 30.12.2009 16:00          315,088,908
     11416 30.12.2009 17:00          431,411,600
     11417 30.12.2009 18:00          191,815,252
     11418 30.12.2009 19:00          147,269,520
     11419 30.12.2009 20:00          112,132,832
     11420 30.12.2009 21:00          307,532,776
     11421 30.12.2009 22:00          439,248,452
     11422 30.12.2009 23:00          150,071,372