SMON generates high redo
RDBMS Version: 10.1.0.2.0
Operating System and Version: Windows 2000 SP4
We are facing lot of redo generation which create archive file of 50 MB each for 10-20 minutes, this doesn't happen in normal usage. When we executed following query to findout which session is doing lot of transactions, we found oracle background process SMON and little bit of MMON is generating lot of redo.
SELECT s.sid, s.serial#, s.terminal, s.username, s.program,i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 6 desc, 1, 2, 3, 4, 5;
when I checked following query to check volume of redo generated by SMON session, it shows "redo size" 20 MB.
select a.name, (b.value)/1024/1024
from v$statname a, v$sesstat b
where b.STATISTIC# = a.STATISTIC#
and a.name like 'redo%'
and b.sid = SMON SID
and b.value != 0
Why SMON generates so much of redo? What can we do to stop this access redo generation. Please help us.
Thanks & Regards,
50Mb every 20 minutes is *nothing*
DB usage at this customer is low and normally one archive file gets created in approx. 30 min., but last few days with same usage every 10 minute archive files are getting created.
well mine your archive logs with log miner then, see what is going on