Here is the scripts for Tracking Redo Generation per Hours and by Days .
Track redo generation by day
SQL>select trunc(completion_time) rundate ,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) “REDO PER DAY (MB)”
from v$archived_log group by trunc(completion_time) order by 1;
Sample Output :
RUNDATE LOGSWITCH REDO PER DAY (MB)
------------- ------------------ ----------------------
18-APR-11 2 1
19-APR-11 5 230
20-APR-11 36 1659
21-APR-11 14 175
22-APR-11 5 147
Track the Amount of Redo Generated per Hour :
SQL> 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 NUM_LOGS MBYTES DBNAME
------------------------- ---------- --------- ---------
2011-04-18 16:00 1 50 NOIDA
2011-04-18 17:00 2 100 NOIDA
2011-04-19 00:00 1 50 NOIDA
2011-04-19 09:00 1 50 NOIDA
2011-04-19 14:00 1 50 NOIDA
2011-04-19 20:00 1 50 NOIDA
2011-04-19 23:00 1 50 NOIDA
2011-04-20 06:00 1 50 NOIDA
2011-04-20 10:00 5 250 NOIDA
2011-04-20 11:00 8 400 NOIDA
2011-04-20 12:00 21 1050 NOIDA
2011-04-20 14:00 1 50 NOIDA
2011-04-21 09:00 1 50 NOIDA
2011-04-21 13:00 3 150 NOIDA
2011-04-21 15:00 1 50 NOIDA
2011-04-21 17:00 8 40 NOIDA
2011-04-21 22:00 1 50 NOIDA
2011-04-22 00:00 1 50 NOIDA
2011-04-22 05:00 1 50 NOIDA
2011-04-22 14:00 2 100 NOIDA
Enjoy :-)
4 comments:
can u also post the script applicable for RAC Thanks in advance
What if your database is running in noarchivelog mode -- how do you track the amount of redo then?
@Anonymous .....
Above query will help u in tracking the amount of redo used . Query will give the same output in case of noarchivelog mode also ...
Have Good Time Ahead .....
Enjoy :)
Thanks Buddy...your query was useful for me...
Post a Comment