Analyzing why Oracle archive log takes a lot of space

Problem

Oracle archivelog grows extensively. Need to figure out what objects are being changed most frequently.

Solution

Run following statement to get statistics by snapshot date, object name and max number of changes.

SELECT to_char(begin_interval_time,'YYYY-MM-DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta) as maxchages
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2020-05-22 17','YYYY-MM-DD HH24')
                                           AND to_date('2020-05-22 21','YYYY-MM-DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY-MM-DD HH24:MI'),
           dhsso.object_name order by maxchages asc;

In order to find out SQL statements that cause these changes, please use following query:

SELECT to_char(begin_interval_time,'YYYY-MM-DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%<your object name>%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
 AND begin_interval_time BETWEEN to_date('2020-01-10 01','YYYY-MM-DD HH24') AND to_date('2020-01-20 21','YYYY_MM_DD HH24')
    AND dhss.sql_id = dhst.sql_id;

Complete explanation can be found at great article: http://cheatsheet4oracledba.blogspot.com/2013/05/huge-archive-log-generation-what-to-do.html