Re: logfile switching frequency

You should switch at a rate that you're happy with.

Sorry -that sounds like a bit of a non-answer, but it isn't. Log switches are good because they induce checkpoints. Once a checkpoint has been issued, you can pretty much guarantee that if you suddenly lost power on your machine and therefore suffered an Instance failure, transactions raised prior to the checkpoint will not need to be recovered. Therefore, frequent log switches bound Instance recovery time.

On the other hand, log switches are also bad because they induce checkpoints. Checkpoints cause DBWR to flush buffers left, right, and centre, and also cause CKPT to have to do its thing to every data file and control file in the database. Masses of I/O mean bad performance. So frequent log switches cause woeful performance.

Somewhere between trivially small Instance recovery times/dreadful performance (frequent switches)and huge Instance recovery times/good performance (few switches) is a point of equilibrium and compromise that you'll be able to live with. But it varies for each database, installation, and dba. You have to find your own happy compromise.

On the other hand, lots of sites seem to think (as a sort-of rule of thumb) that a switch every half-hour or hour or so is acceptable.

All you need then do is add log groups of a size that brings the rate of switching down to your chosen number, and drop the logs that are of a different size (because you can't resize existing logs, unfortunately).

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"Tomasz Boboli" <tboboli_at_elka.pw.edu.pl> wrote in message
news:9tdh0i$mr9$1_at_julia.coi.pw.edu.pl...


> Hello,

> My database has 4 logfiles.

> How often Oracle should switch between those files?

>

> Thank you,

> Tomek

>

>