Targets

A few years ago I would have said quite happily that there weren’t many databases small enough to fit completely in memory, but that’s no longer true given the very large memories that computers can manage these days. Even so, most of the Oracle databases I see aren’t memory-resident, and even if the database can fit in memory, its owners probably don’t feel safe unless they have a fairly recent snapshot of the data copied to disk. Whatever we do with memory to make our databases go faster, we have to recognize that for most databases the software has to have a good strategy for copying changed memory to disk.

In the case of Oracle we have two types of content to think about, the (buffered) data blocks and the redo log (buffer), and two different but interlinked strategies for writing that content to disk. Of the two sets of content, the redo log buffer is the more important because it is the one that gives us durability with scalability, by minimizing the disk I/O we have to do to secure our database changes. But we need to be sure that the strategies we use for writing both sets of content will always leave us in a position where instantaneous failure of an instance will allow us to restart without losing any committed transactions (which is the durability requirement).

As we have seen, the log file holds a description of all changes we make to the data blocks, so if we can find an old copy of the database somewhere, and all the redo log files that have been written since that copy was made, then we can work through the log files and apply the change records they contain to the database copy to bring the copy up to date.

Through a simple mechanical process we can bring an old copy of the database up to the state it was in just before the instance failed. In particular, if our “old copy” is just a few seconds or a few minutes old because it’s the live production copy of the database that’s on disk and being kept up to date in near real time, then we can recover from instance failure very quickly.

The difficult bit, for Oracle Corp.’s developers, is designing an infrastructure that allows this recovery phase to complete as rapidly as possible while ensuring that any work done in anticipation of recovery is kept to a minimum during normal operation.

The key to being able to recover is writing the redo log to disk in a “timely” fashion; but the key to minimizing recovery time is to keep writing database blocks to disk, and the key to minimizing the work you do writing database blocks is to batch them up and write them in the correct order—which (in the case of Oracle) means oldest first.

So we’re going to look at the activity of the log writer (lgwr) and I’ll explain what I mean by “timely,” and we’ll look at the database writer (dbwr) and I’ll explain the significance of “oldest first” and why that strategy on its own doesn’t quite work.

The problem I have, though, is deciding where to start. Which should come first, lgwr or dbwr? You could argue for the log writer because it must write a change description to disk before the database writer writes the block that has been changed. On the other hand, the database writer sometimes calls the log writer to write, and also generates redo records as it writes data blocks, so there’s a good argument for talking about the database writer first.

So here we are, going round and round in circles again. I’m going to start with the log writer, then handle the database writer, and then fill in a few of the gaps I left on the first pass through the log writer by describing, in particular, the interactions between the two.