Топ-100
 
CerebroSQL

Generate redo data

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

Top objects the generate data redo

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

TOP generat object.jpg

-> Request to retrieve data <-

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.jpg

-> Request to retrieve data <-

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

-----