Redo Log Switch Order

Hi,

I always have the impression that redo log switch occurs in group number order but a small test showed me, I was wrong.

As you know redo log is one of the most important part of a Oracle database. it almost protect everything! your data in memory, data file, your transaction etc.  whenever you made a change in database a redo entry (maybe two) is written to redo log files. So the slowest component of a Oracle database is redo log files because it always does an disk IO. as a result we mostly use fastest disks to locate redo log files that way our TPS (transaction per second) can be maximum.

Lately, I was tuning a client database and realized their redo log files and data files are on the same disk! this is already a availability issue; if something happens to disk, you will loose your data files and redo log files. there is one thing guaranteed in Oracle, if you loose an active redo log file, you will loose some data. we always have more than one redo log member (file) in a redo log group and put those files into separate disks. that way if something happens to one of disks, we can still use redo log file which is on the other disk. of course you can use an ASM disk group and reduce the risk. if you don’t have a ASM disk group then you must put your redo log files on different disks. Anyway, so I asked to hosting company to add 4 new disks. I had 4 redo log groups with 2 members each. I put first and third redo log group members into disk1 and disk2 and then second and fourth redo log group members into disk3 and disk4.

while Oracle is writing to redo log group 1, Disk1 and Disk2 will be written and when it is switched to redo log group 2, Disk3 and Disk4 will be written. reason I do this, these disk drives has some limits. while you are writing some data they are using their full speed but when you start to read another file at the same time, write speed is decreased. As you can guess, since our database in archivelog mode, when a redo log switch occurs, Oracle starts to archive previous redo log file but this means you need to read it! so I don’t want Oracle write and read at the same time for the same disk. So, when redo log group1 is full and a switch occurs, Disk1 will be read (to archive redo log file) and redo log group 2 will be start to be written which resides on disk3 and disk4. This way none of the disks will be read and written at the same time and we will get the maximum performance. At least I thought so!

while testing that structure, I realized that performance has decreased dramatically after a switch occurs. when I checked, after redo log group 1 is full, it did not switch to redo log group 2! it switched to redo log group 3 which is at the same disk groups (1 and 2).

As I said at the beginning, I always have the impression that redo log switch occurs in group number order but it does not. while moving redo log files to those disks I did not shutdown database and move redo log files. I added some new redo log groups and dropped old ones so at some point some of the redo log groups were UNUSED state (they never used) and when a switch is required, oracle switched to smallest UNUSED redo log groups.

as you can see, switch occurs from 1 to 3 and also Oracle remembers that order and keep switching in that order so if you clear your logfile or add new log group, keep in mind that next switch will be to newly added or cleared log group.

PS: by changing the disk structure as this, TPS (transaction per second) increased from 15.000 to 45.000! small changes can produce big results.