Oracle查看导致归档日志变大的语句

with aa as 
(SELECT IID,
       USERNAME,
       to_char(BEGIN_TIME,'mm/dd hh24:mi') begin_time,
       SQL_ID,
       decode(COMMAND_TYPE,3,'SELECT',2,'INSERT',6,'UPDATE',7,'DELETE',189,'MERGE INTO','OTH') "SQL_TYPE",
       executions "EXEC_NUM",
       rows_processed "Change_NUM"
  FROM (SELECT s.INSTANCE_NUMBER IID,
               PARSING_SCHEMA_NAME USERNAME,COMMAND_TYPE,
               cast(BEGIN_INTERVAL_TIME as date) BEGIN_TIME,
               s.SQL_ID,
               executions_DELTA executions,
               rows_processed_DELTA rows_processed,
               (IOWAIT_DELTA) /
               1000000 io_time,
               100*ratio_to_report(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) RATIO,
               sum(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) totetime,
               elapsed_time_DELTA / 1000000 ETIME,
               CPU_TIME_DELTA / 1000000 CPU_TIME,
               (CLWAIT_DELTA+APWAIT_DELTA+CCWAIT_DELTA+PLSEXEC_TIME_DELTA+JAVEXEC_TIME_DELTA)/1000000 OTIME,
               row_number() over(partition by s.INSTANCE_NUMBER,BEGIN_INTERVAL_TIME order by rows_processed_DELTA desc) TOP_D
                   FROM dba_hist_sqlstat s, dba_hist_snapshot sn,dba_hist_sqltext s2
         where s.snap_id = sn.snap_id
           and s.INSTANCE_NUMBER = sn.INSTANCE_NUMBER 
           and rows_processed_DELTA is not null
           and s.sql_id = s2.sql_id and COMMAND_TYPE in (2,6,7,189)
           and sn.BEGIN_INTERVAL_TIME > sysdate - nvl(180,1)/1440         and PARSING_SCHEMA_NAME<>'SYS')
 WHERE TOP_D <= nvl(20,1)
  )
  
select aa.*,s.sql_fulltext "FULL_SQL" from aa left join  v$sql s on  aa.sql_id=s.sql_id ORDER BY IID, BEGIN_TIME desc,"Change_NUM" desc