June 9, 2009

Quiz Night

I was reading a blog earlier on this evening that was talking about the definitions of “consistent gets” and “db block gets”. The point of the article was that it’s very hard to find good descriptions of exactly what these two terms mean.

I’ll add the link to the blog item (and give my definitions) in a few days time but, in the interim, does anyone want to try their hand at defining these two very common terms in a couple of short paragraphs.

[Updated: 11th June] Just a brief note to tantalize a little more, did you know that :

You can visit blocks in the buffer cache without recording a consistent get or a db block get.

You can CHANGE the current version of a block without recording a db block get (or even a consistent get – and without generating undo or redo).

Consistent gets can create versions of a block that have NEVER, EVER, existed at any point in time (or, much the same thing, at any SCN).

1) The “db block get” is easy to explain – except that when you read undo blocks to apply undo records for read consistency, you HAVE to read the current version [of the undo block] – so why is that [access to the undo block] a “consistent get”.

2) “A consistent get is when you get a block in consistent mode.” and a “saggar maker’s bottom knocker” is a person who knocks the bottoms for the saggar maker – any wiser ?

Your (or Tom’s) 3 sentences make a start, but don’t explain consistency, or why some gets which are necessarily current mode are still labelled as consistent gets.

A consistent get is a block read from memory which may motivate a physical block read from disk plus the read and application of zero or more undo blocks to provide a consistent read (view) of the block as of the time (based on the default isolation level) that the query started.

A db block get is the current mode (version) of the block, which may include uncommitted data created by the current or another session. Db block gets commonly occur when data is being inserted or updated.

Same problem as Kirk – “a consistent get gets a consistent view” … what is “consistent”. The point about isolation levels is an interesting one.

Your explanatory comments on db block get are pretty good – but could go further.

A point that’s worth mentioning about Mark’s post (I’ve modified the URL to point to it directly). He’s quoting a comment he made in 2003 when 8i was still very common, and that’s the last version where tablescans and index fast full scans started with “db block gets” – hence his comment about this action being version dependent.

Jonathan, thanks for the feedback. I agree that my comment of “a consistent get gets a consistent view” is poorly worded – possibly it would have been better to state “a consistent get attempts to build a version of a block as of a specific time, or more accurately, as of a specific SCN through the application of zero, one, two, or more undo blocks. The specific SCN to which the block is rolled back is dependent on the current isolation level specified for the session.”

Does the explanation on pages 12-16 of “Practical Oracle 8i” still apply in more recent releases?

I think the bits on pages 12 – 16 are still sufficiently accurate to give people the right picture. I could add in some details about in-memory undo, though.

If I had to rewrite it I might decide to mention that there are a couple of special cases and point out that LOBs are completely different – but I don’t think I’d want to add much to the amount of information that that section delivers.

As far as the consistent gets go, I think everyone has a reasonably good idea of the basic concept – but I’m not sure that there are many people who have actually thought through the implication of what they’re saying on a multi-user system.

I’m suspect that when I give my explanation – and, like everyone else, I’m not going to be able to claim that it will be correct – most people will say: “so what, it doesn’t make any real difference and I was close enough”, and a few geeky types will say something like “wow, those guys at Oracle Corp. are clever bunnies”.

I think Kirk gets the prize.
I’ll add that DB Block Get (AKA current get) normally happens in DMLs, where the change must apply to the current block and not its past image.

Speaking of confusing nomenclature – in v$transaction, there are two columns: LOG_IO and PHY_IO. I just had an interesting discussion with a colleague who insisted that LOG_IO refers to blocks written to redo_log :)

That’s a point worth making about DML – but Oracle can do db block gets with no intent to change those blocks.

I can sympathise with your colleague’s guess – the name (LOG_IO) makes sense and fits the context of transactions and it is an interesting anomaly that v$transaction doesn’t say anything about redo generated for that transaction. Did you demonstrate that (s)he was wrong, or did you make him/her try (and fail) to prove their assertion.

A follow-up on v$transaction: without first testing, would you be able to say whether the log_io is counting the db block gets, the consistent gets, or both ?

[Addendum: I see that you’ve also got a recent posting on various terms used for block visits. ]

I’ve just done a Google search and found a couple of articles (undated) on the internet claiming that a “db block get” is a “physical read” – so I hope this blog item will stop anyone from making that mistake again.

At the same time I also found an item in an official Oracle 9.2 manual saying that if Oracle tries to do a consistent get and doesn’t have to read any undo then the get is recorded as a db block get. (It’s not, of course, it would typically be recorded as a consistent get and also as an example of “no work – consistent read gets”).

“Quite amused” is good, by the way. I hope no-one gets upset by me picking holes in their offerings.

Senior DBA: FYI, there is a (UK) English expression “I’ve just had a senior moment” which means “I’ve just been very forgetful or done done something particularly stupid.” Everyone gets older but, alas, not everyone gets wiser.

1) The “db block get” is easy to explain – except that when you read undo blocks to apply undo records for read consistency, you HAVE to read the current version – so why is that a “consistent get”.

Jonathan,

That is an interesting question. If I understand correctly, are you trying to say that when Oracle (first) attempts to “access” a data block (say, with the intention of getting a “consistent” image as of a point-in-time) and “realizes” that it needs to read UNDO, then why that “access”(to decide whether UNDO is needed or not) is not considered as “consistent get”?
But then isn’t that supposed to be the way oracle “counts” consistent gets? I always assumed that-
1. “Consistent gets” – Number of times Oracle has to access a data block (either from buffer cache or UNDO) in order to generate a point-in-time image of the data block
2. “DB Block Gets” – Number of times Oracle has to access a data block (from buffer cache) as of NOW.

p.s. Just before posting this, I tried a small test (and got confused…)
Due to “logistical reasons”, I am not able to copy-paste straight from SQL*Plus, but here is what I did in sequence
1. create table T as select * from all_objects where rownum <= 10
2. exec dbms_stats.gather_table_stats(user, 'T')
3. select blocks from user_tables where table_name = 'T' — This showed that T is a single block table
4. set autotrace on statistics
5. select * from T — This resulted in 6 consistent gets (is it due to the single block being visited 6 times?) and 12 "DB Block Gets" (why 12?)

Narenda,
Your posting (all three attempts) got labelled as spam – which is why there was a delay in it appearing. I’ve edited the comment you were referring to to clarify the point that it’s the “current” gets on the undo that were recorded as “consistent” gets.

The example with 8i revolves around two things. One is the point I raised with regard to Charles’ link to Mark Bobak’s OTN item – in 8i a tablescan generally starts with 4 current gets on the segment header. This changes to 2 consistent gets in 9i (which explains the zero you recorded in your next note when testing 10g).

In your case you created the table with a ‘CTAS’ – which preformats the blocks and loads them in a special state. As a side effect (which I can’t explain) of the CTAS, this 4-block access seems to be repeated three times (with a access to a related undo segment header each time) for a total of 12 current gets. But this is only in 8i.

a) Consistent gets : a block image obtained from the applications of 0 to ‘n’ cumulative change vectors (undo) up to a given time (SCN).
b) db block get : a block in memory which is not referenced by any change vector, hence not dirty also.

(a) but (see my update) a consistent block can in a state that doesn’t match any point in time (SCN) in it’s history.

(b) All blocks that have been used will have some change vectors that reference them.

Side note: I’ve got a draft note about the difference between “clean” and “cleaned out” – but “clean” simply means that the block in memory is identical to the block on disc. It is possible to do a db block get for a block that is not a clean block.

“Consistent gets can create versions of a block that have NEVER, EVER, existed at any point in time ”

Is it because a change vector is transaction based, while you may have many transactions taking place at the same time in the block. Later if you re-roll only one transaction, you create a version of the block which reflect only the mutation covered by this transaction. Your block is then a mix of legacy-data – area not in the transaction – and and re-reconstructed – area in transaction.

A ‘db block get’ would then be, by opposition, a block with ALL transactions reconstructed (or present) at a given SCN.

You’ve identified the critical point in the consistent get. When you first arrive at the block it has a history of transactions, some of which may be committed, some uncommitted, and those transactions could have been running concurrently – and you have to reconstruct the right thing.

The db block get, though, doesn’t have to reconstruct anything – it has to be the most recent version of the block, including all changes, whether or not committed.