top session by sampling redo size statistics - standalone version of https://github.com/xtender/xt_scripts/blob/master/tops/top_by_sesstat.sql

top_redo.sql

-- more extended version is here: https://github.com/xtender/xt_scripts/blob/master/tops/top_by_sesstat.sql
var c refcursor;
col username for a30;
col program for a20 trunc;
col osuser for a20 trunc;

declare
   v_stat1 sys.ku$_objnumpairlist;
   v_stat2 sys.ku$_objnumpairlist;

   p_stat       int := 313; -- redo size
   p_interval   int:= 5; -- 5 seconds

   cursor c_stats(p_statistic# int) is
      select
         sys.ku$_objnumpair(
            st.SID
           ,st.value
         ) as numpair
      from v$sesstat st
      where st.STATISTIC#=p_statistic#;

begin

   open c_stats(p_stat);
   fetch c_stats bulk collect into v_stat1;
   close c_stats;

   -- Sleep:
   dbms_lock.sleep(p_interval);
   -- Repeating:
   open c_stats(p_stat);
   fetch c_stats bulk collect into v_stat2;
   close c_stats;

   open :c for
      with s_top(sid,delta) as
           (
            select *
            from (
                 select
                    t1.num1            as sid
                   ,t2.num2-t1.num2    as delta
                 from table(v_stat1) t1
                     ,table(v_stat2) t2
                 where t1.num1 = t2.num1
                 order by delta desc
                 )
            where rownum<=10
           )
      select--+ use_nl(t s) leading(t s) no_merge(t)
         t.sid,t.delta
        ,s.username
        ,s.program
        ,s.sql_id
        ,s.osuser
        ,s.event
        ,s.status
      from s_top     t
          ,v$session s
      where s.sid=t.sid
      order by 2 desc;
end;
/
print c;