Топ-100
top of page
CerebroSQL

Generate redo data

Viewing information on objects during work with which the maximum amount of data is generated in the REDO logs of the Oracle database

Redo generate general

Top objects the generate data redo

Statistics on objects, when working with which the maximum amount of data is generated in the REDO logs

TOP generat object

select *
 from (SELECT to_char(min(begin_interval_time), 'DD-Mon-YY HH24:MI') ||' - ' ||
               to_char(max(begin_interval_time), 'DD-Mon-YY HH24:MI') as WHEN,
              dhso.object_name,
              dhso.object_type,
              dhso.tablespace_name,
              sum(db_block_changes_delta) as db_block_changes,
              to_char(round((RATIO_TO_REPORT(sum(db_block_changes_delta)) OVER())
                             * 100, 2), '99.00')||' %' as REDO_PERCENT          
         FROM dba_hist_seg_stat     dhss,
              dba_hist_seg_stat_obj dhso,
              dba_hist_snapshot     dhs
         WHERE dhs.snap_id = dhss.snap_id
           AND dhs.instance_number = dhss.instance_number
           AND dhss.obj# = dhso.obj#(+)
           AND dhss.dataobj# = dhso.dataobj#(+)
           AND begin_interval_time BETWEEN
               to_date('<date_start>','dd.mm.yyyy hh24:mi:ss') AND
               to_date('<date_end>','dd.mm.yyyy hh24:mi:ss')
        GROUP BY dhso.object_name,dhso.object_type,dhso.tablespace_name
        ORDER BY db_block_changes desc)

Size REDO archived

Statistics on the size of archivelog files generated by day

Size generate archivelog

select s."DateFirst",
      round(sum(s."Bytes")/1024/1024/1024, 2)||' GB' "GB",
      (select dest_name||'='||destination
         from V$ARCHIVE_DEST where dest_id=s.dest_id)
 from (select TRUNC(first_time) "DateFirst",
              blocks*block_size "Bytes",                 
              dest_id
         from V$ARCHIVED_LOG) s
group by s."DateFirst", dest_id
order by 1 desc, 3

bottom of page