Thanks Tom.
gs, January 29, 2003 - 12:38 pm UTC
After sending you the question, I realised logminer is the way to go and started exploring it. Now, your answer confirm it. Thanks.
Wishing for logminer on 7.3 database
DW, May 03, 2005 - 6:08 pm UTC
I have the same question, but for an older database. I am seeing unusually high redo log activity on a 7.3 database and I can't identify what transactions are responsible. Any suggestions (short of upgrading)?
May 03, 2005 - 7:28 pm UTC
7.3 was less than primitive from todays perspective. When it came out, it seemed so advanced...
guess you could start with
a) snapshot v$sysstat where name like '%redo%' and copy executions/rows_processed/sql_text from v$sql
b) do it again (and again and again, with timestamps, maybe use a job)
come back to analyze, look for a window of time with high redo -- peek at the sql that was executed during that window and maybe look for lots of executes of a modification or a modification with tons of rows.
just a starting place.
Culprit identified
DW, May 05, 2005 - 4:30 pm UTC
Excellent-- thanks! v$sql revealed an unnecessary update in a frequently called procedure that kept hitting the same 7400 rows over and over and over. The number 7400 gradually crept up over time, so the performance impact crept up on us too.
This site is the greatest. It has helped answer many of my database questions. Many thanks from a fellow math major turned Oracle specialist.
SMON generates high redo
shailesh, January 11, 2006 - 11:39 am UTC
Hello Tom,
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 does SMON generates so much of redo? What can we do to stop this access redo generation. Please help us.
Thanks & Regards,
Shailesh
January 12, 2006 - 10:39 am UTC
50mb in 10-20 minutes is pretty "small"
You are seeing the AWR repository being maintained - the "long term memory of the database"
Shailesh, January 12, 2006 - 1:57 pm UTC
Thanks Tom,
At this customer with normal usage archive file gets created by 30 minutes, but last few days with same usage it is generating archive file in 10 minutes.
So this was the question.
Regards,
Shailesh