Consistent Gets – 2

How would you describe “db  block gets” and “consistent gets” in a couple of short paragraphs ? Having asked the question, I suppose I ought to offer my version of the answer up for scrutiny and dissection.

Before supplying my descriptions, I’d like to point out that there are (at least) two other mechanisms that Oracle uses to visit data blocks in the buffer cache: re-visiting pinned buffers, and doing a fast cleanout on commit. These buffer visits can be seen in the statistics: “buffer is pinned count” and “commit cleanouts successfully completed”. (The statistic “commit cleanouts” tells you how many times Oracle tried to do a commit cleanout and there are various statistics to explain the failures.)

Whan I asked the question originally, I pointed out that it is possible for Oracle to change a block without recording any type of “get” and without generating any undo or redo; that’s the “commit cleanout”. Strictly speaking I wasn’t telling the exact truth, the cleanout is completed by the next person to update the block, and it’s the next person that generates the relevant undo and redo, so technically you could argue that the commit cleanout is the source of some undo and redo, even though its appearance is delayed.

So, back on point:

A db block get is used when Oracle has to see the most recent version of a block, including all the uncommitted changes – but “commit cleanouts” are not considered to be db block gets even though the block has to be the most recent version of the block.

The two commonest reasons for db block gets are:

•  access to a block to change the data – you should only change the latest version of the block and …
•  access to index blocks to check uniqueness and  referential integrity constraints – behind the scenes, your session  needs to see uncommitted changes from other sessions (even though you, the end-user aren’t allowed to see these changes) in case you are trying to make a change that will become illegal when the other session commits. This conflict is often the cause of mode 4 waits on TX enqueues. [Paragraph updated in response to a comment below.]

A consistent get is any other “latch protected” block visit (i.e. excluding visits to pinned buffers) that does not need to see the latest version of the block.

The two commonest reasons for consistent gets are:

• access to an undo block to find records that can be used to construct a “read-consistent” version of a data block
• access to a version of  a data block showing only the changes that had been committed as at a given point in time (as indicated by a system change number (SCN)).

Of course, when all you want to do is give the right sort of impression about what’s going on, the “traditional” descriptions are adequate approximations: “a db block get access the latest version of a block”, “a consistent read uses undo to produce a version of a block as at an earlier point in time”. But it’s nice to be able to add more precision when you see unexpected patterns in the numbers in a Statspack or AWR report.

Here, for example, is a thought that’s often overlooked – and it’s a point I made in the earlier posting. A “read-consistent” block may be in a state that never existed in the past. Consider this scenario:

• Session 1 updates row 1 in the block, but does not commit.
• Session 2 updates row 2 in the block, and commits
• Session 3 queries the block

Session 3 has to apply undo records to a copy of the block to reverse out the change to row 1. The “read-consistent” copy of the block includes the change to row 2, but not the change to row 1 – it is in a state that never existed in the past.

Once you’ve realised the significance of this simple scenario you can understand why it’s possible to see extreme numbers of consistent gets when several processes mix short and long transactions to update a small section of a database – the processes are constantly thrashing through the undo segments to create versions of a handful of blocks to see what the data would look like if the changes made by other sessions weren’t there. This effect can become particularly serious with code that does “select for update” followed a little later by “update …”

One final thought – if you’re about to update a block you acquire it with a db block get to make sure you’ve got the latest version. But before you update it you have to create a read-consistent copy to check that

• the target rows you can see in the current block existed when you started your update
• there aren’t any  “missing” rows that were target rows when you started the update but don’t look like targets in the current block because they have been deleted or updated by other transactions (which may or may not have committed) in the interim.

When you start to think carefully about read-consistency you sometimes wonder how the technical wizards at Oracle Corp. managed to get it working at all !