Friday, April 22, 2011

Track Redo Generation per hours and days

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:

Anonymous said...

can u also post the script applicable for RAC Thanks in advance

Anonymous said...

What if your database is running in noarchivelog mode -- how do you track the amount of redo then?

NEERAJ VISHEN said...

@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 :)

siva said...

Thanks Buddy...your query was useful for me...