Thursday, January 1, 2009

How to monitor the amount of redo generated per hour

At customer sites very often I can see the transaction load via the amount of redo which was generated.

For best overview I like to query the amount of redo generated per hour:

SELECT Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
2) AS Mbytes,
Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time,
'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time,
'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time,
'YYYY-MM-DD'),
To_Char(Vlh.First_Time,
'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
Log_Hist.Start_Time;

Sample output:

START_DATE START_TIME NUM_LOGS               MBYTES                 DBNAME
---------- ---------- ---------------------- ---------------------- ---------
2006-01-24 11:00 1 100 MYDB
2006-01-24 14:00 2 200 MYDB
2006-01-24 16:00 1 100 MYDB
2006-01-24 18:00 1 100 MYDB
2006-01-24 20:00 5 500 MYDB
2006-01-24 21:00 14 1400 MYDB
2006-01-24 22:00 11 1100 MYDB
2006-01-25 00:00 1 100 MYDB
...

No comments:

Post a Comment