Archive Log and Redo Generation Rate

Last One Year archive log generation
----------------------------------------------------

select THREAD#,trunc(next_time),count(*)
from V$ARCHIVED_LOG
where trunc(next_time) between trunc(sysdate-366) and trunc(sysdate)
and DEST_ID =1
group by THREAD#,trunc(next_time)
order by 2 ;

Last One Week archive log generation
---------------------------------------------------------

select THREAD#,trunc(next_time),count(*)
from V$ARCHIVED_LOG
where trunc(next_time) between trunc(sysdate-7) and trunc(sysdate)
and DEST_ID =1
group by THREAD#,trunc(next_time)
order by trunc(next_time) desc ;

Per Month archive log generation
----------------------------------------------------

set pages 8000
select count(name), to_char(next_time,'mon')
from v$archived_log
group by to_char(next_time,'mon') order by 1;



Query to find  HOURLY archive log generation 
---------------------------------------------------------------

set pagesize 30;
set lines 300;
col day for a17;
col 00 for a5
col 01 for a5
col 02 for a5
col 03 for a5
col 04 for a5
col 05 for a5
col 06 for a5
col 07 for a5
col 08 for a5
col 09 for a5
col 10 for a5
col 11 for a5
col 12 for a5
col 13 for a5
col 14 for a5
col 15 for a5
col 16 for a5
col 17 for a5
col 18 for a5
col 19 for a5
col 20 for a5
col 21 for a5
col 22 for a5
col 23 for a5
select
  to_char(first_time,'YY-MM-DD') day,
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
  COUNT(*)
from v$log_history
group by to_char(first_time,'YY-MM-DD')
order by day ;

HOURLY
----------------
set pages 100
set lines 200
select count(name) COUNT, to_char(next_time,'mm:dd:yy') "DATE" ,to_char(next_time,'hh24') "HOUR"
from v$archived_log
group by to_char(next_time,'mm:dd:yy'),to_char(next_time,'hh24') order by to_char(next_time,'mm:dd:yy');

Query to find  REDO APPLY RATE ON STANDBY
------------------------------------------------------

Set Linesize 400
Col Values For A65
Col Recover_start For A21
Select To_char(START_TIME,'Dd.mm.yyyy Hh24:Mi:ss') "Recover_start",To_char(Item)||' = '||To_char(Sofar)||' '||To_char(Units)||' '|| To_char(TIMESTAMP,'Dd.mm.yyyy Hh24:Mi') "Values" From V$Recovery_progress Where Start_time=(Select Max(Start_time) From V$Recovery_progress);

Query to find Redo size generated per day
-------------------------------------------

SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_GB,
         SUM_ARCH_DEL.DELETED_GB,
         SUM_ARCH.GENERATED_GB - SUM_ARCH_DEL.DELETED_GB "REMAINING_GB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size)/(1024*1024*1024), 2))
                      GENERATED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024 * 1024), 2))
                      DELETED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

Query to find Archive log generation on an hourly basis:
----------------------------------------------------------------------------

set pages 100
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;


Query to find User Generating more redo
-----------------------------------------------------------

SELECT  s.sid, s.serial#, s.username, s.program, i.block_changes,(i.block_changes*8)/1024/1024 GB_redo
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid AND i.block_changes > 0 and s.username is not null and rownum<20
ORDER BY 5 DESC, 1;


col username for a15
col program for a30
col sql_text for a100
SELECT
s.sid,
s.username,
s.program,
ROUND(t.VALUE/(1024*1024)) AS "Redo Size MB",
sa.sql_text
FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn, V$SQLAREA sa
WHERE s.sid = t.sid
AND t.statistic# = sn.statistic#
AND sn.name = 'redo size'
AND sa.sql_id = s.sql_id
AND ROUND(t.VALUE/(1024*1024)) != 0 and rownum<20
ORDER BY t.VALUE DESC;


col machine for a25
col username for a15
col redo_MB for 999G990 heading “Redo |Size MB”
column sid_serial for a13;
select b.inst_id,
       lpad((b.SID || ',' || lpad(b.serial#,5)),11) sid_serial,
       b.username,
       machine,
       b.osuser,
       b.status,
       a.redo_mb
from (select n.inst_id, sid,
             round(value/1024/1024) redo_mb
        from gv$statname n, gv$sesstat s
        where n.inst_id=s.inst_id
              and n.name = 'redo size'
              and s.statistic# = n.statistic#
        order by value desc) a,
     gv$session b
where b.inst_id=a.inst_id  and a.sid = b.sid  and   rownum <= 10  and b.username is not null order by redo_mb desc;



Undo Blocks accessed
-----------------------
SELECT s.sid, s.serial#, s.username, s.program, s.machine, t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 6, 7 desc;