Re: What is filling the logs
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: dedba@xxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 May 2016 15:59:05 -0400
On 05/12/2016 12:40 AM, De DBA wrote:
G'day!
I think I'm going mad.. I have a database which is completely idle. No
jobs, no job engines, no users connecting, just background processes.
log_checkpoint_timeout and log_checkpoint_interval are left default.
Yet the 1GB logfiles switch every 7 minutes on average.
I must be overlooking the obvious.. What could cause this rediculous
amount of log switches? It is a copy of the production database. I am
now suspecting that whatever causes it may also happen on the (very
busy) production itself. Any thoughts welcome...
Cheers,
Tony
--
//www.freelists.org/webpage/oracle-l
So, you're having a switch every 7 min or so? Here is a SQL that you can
run from sqlcl, every 30 seconds or so, which will tell you what is
writing the redo entries:
select ss.sid,'redo size:'||ss.value,s.program,s.module
from v$statname sn,v$sesstat ss,v$session s
where ss.statistic#=sn.statistic# and
sn.name='redo size' and
s.sid=ss.sid and
ss.value>0
order by ss.value;
You can use the "repeat" command from sqlcl to repeat that every 10
seconds or so. That way, you get an instant top-like monitor:
SQL> set sqlformat ansiconsole
SQL> select ss.sid,'redo size:'||ss.value,s.program,s.module
2 from v$statname sn,v$sesstat ss,v$session s
3 where ss.statistic#=sn.statistic# and
4 sn.name='redo size' and
5 s.sid=ss.sid and
6 ss.value>0
7 order by ss.value;
SID 'REDOSIZE:'||SS.VALUE PROGRAM MODULE
9 redo size:124 oracle@xxxxxxxxxxxxxxxxx (DBRM)
114 redo size:340 oracle@xxxxxxxxxxxxxxxxx (W002) KTSJ
35 redo size:1092 oracle@xxxxxxxxxxxxxxxxx (W001) KTSJ
115 redo size:1428 java@umajor (TNS V1-V3) java@umajor
(TNS V1-V3)
42 redo size:1428 java@umajor (TNS V1-V3) java@umajor
(TNS V1-V3)
15 redo size:11212 oracle@xxxxxxxxxxxxxxxxx (SMON)
40 redo size:23748 oracle@xxxxxxxxxxxxxxxxx (CJQ0)
12 redo size:29504 oracle@xxxxxxxxxxxxxxxxx (DBW0)
18 redo size:998184 oracle@xxxxxxxxxxxxxxxxx (MMON)
After that, you type "repeat 10 5" to repeat that 10 times, with 5
seconds of beauty sleep between the invocations. If the answer turns out
to be MMON, you are probably doing AWS and ASH snapshots. V$SESSION will
not show SQL_ID for MMON or other system processes, but you can still
turn on SQL trace to get that. Did I forget to mention that sqlcl is the
best thing since sliced bread?
Regards
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
Other related posts:
