We have RAC environment and we have noticed huge archive generation. When we run the query to know who is generating more redo, Background processes are on top. Kindly advice.
SQL>
SQL> select
2 ss.sid,
3 'redo size:'||ss.value,
4 s.program,
5 s.module
6 from
7 v$statname
8 sn,v$sesstat
ss,v$session s
9 10 where
ss.statistic#=sn.statistic#
11 12 and
sn.name='redo size'
and
s.sid=ss.sid
and
ss.value>0
order by
ss.value; 13 14 15 16 17 18 19
SID 'REDOSIZE:'||SS.VALUE PROGRAM MODULE
---------- -------------------------------------------------- ------------------------------------------------ ----------------------------------------------------------------
132 redo size:1392 Mobile.SignalR.Host.exe
103 redo size:1584 oracle@arc-oraprd-db1.alramz.local (W001) KTSJ
76 redo size:45612 eAccessOrd.exe
164 redo size:59260 Mobile.SignalR.Host.exe
35 redo size:63220 oracle@arc-oraprd-db1.alramz.local (CJQ0)
34 redo size:96524 oracle@arc-oraprd-db1.alramz.local (MMON)
158 redo size:5729312 oracle@arc-oraprd-db1.alramz.local (LMS0)
188 redo size:5820132 oracle@arc-oraprd-db1.alramz.local (LMS1)
160 redo size:76300936 oracle@arc-oraprd-db1.alramz.local (SMON)
127 redo size:101729888 oracle@arc-oraprd-db1.alramz.local (CKPT)
96 redo size:170059588 oracle@arc-oraprd-db1.alramz.local (LGWR)
65 redo size:172806096 oracle@arc-oraprd-db1.alramz.local (DBW0)