Most redo

--1 find  OBJECTs where a lot blocks changed
--db_block_changes_delta: The delta value is the value of the statistics 
--  from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

select * from (  
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,  
       dhsso.object_name,  
       dhsso.object_type,
       dhsso.owner, 
       SUM(dhss.db_block_changes_delta)  changed_blk 
  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> sysdate - 24/24 
 GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),  
          dhsso.object_name  ,dhsso.object_type,
       dhsso.owner
 order by 5 desc)  
 where rownum < 50
 
 
 
 --2 find SQL which related to these OBJECTS in setp 1
 
 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 '%TEST_REDO%'  
   AND dhss.snap_id = dhs.snap_id  
   AND dhss.instance_Number = dhs.instance_number  
   AND dhss.sql_id = dhst.sql_id;
   

--3 find session、module、machine and so on  related to these SQL in step 2

select * from dba_hist_active_sess_history WHERE sql_id = 'g893rmm0rmjra';  
select * from v$active_session_history where sql_Id = 'g893rmm0rmjra';