CODEWITHSUNDEEP
oracle-databaseoracle11g
anudeepks
anudeepks

Reputation: 1132

Observing frequent log file switches depsite increasing the redo log size

We had redo log sized 256m and then bumped it up to 512 and eventually 1024M and currently have 8 logs. Despite that we are observing log switch happenign every 1minute and it is eating into our performance,

A snapshot from AWR

Load Profile

Per Second  Per Transaction Per Exec    Per Call
DB Time(s): 1.0 0.1 0.00    0.01
DB CPU(s):  0.6 0.1 0.00    0.01
Redo size:  34,893.0    4,609.0      

 Instance Activity Stats - Thread Activity
 Statistics identified by '(derived)' come from sources other than SYSSTAT
 Statistic  Total   per Hour
 log switches (derived) 82  59.88

Any suggestions on how to reduce the number of log file switches, I have read that ideally it should be about 1 switch in 15-20 minutes.

Upvotes: 1

Views: 2204

Answers (1)

Balazs Papp
Balazs Papp

Reputation: 526

34893 bytes of redo per second = 125614800 bytes per hour, that is about 120 MB, nowhere near the size of 1 redo log group.

Based on this and the size of the redo logs, I would say something forces log switches periodically. The built-in parameter archive_lag_target forces a log switches after the specified amount of seconds elapses, that is the first thing I would check. Other than that, it could be anything else logging in to the database and forcing a log switch manually, e.g a cron job. (60 log switches per 60 minutes, thats quite suspicious)

Upvotes: 1

Related Questions