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).
[Updated: 12th June] I’ve published my definitions in a separate note. But this is the link to the blog that started this thought running.
Oracle Scratchpad 
Consistent get = logical read (multi version etc..)
DB block gets = physical read (what we have to do for the first)
just my 2c$
bye
Comment by Nicola — June 9, 2009 @ 10:07 pm BST Jun 9,2009 |
Nicola,
You win the prize for getting in first – but I’m sorry, that’s definitely wrong. “db block gets” are not the same as “physical reads”.
Comment by Jonathan Lewis — June 9, 2009 @ 10:11 pm BST Jun 9,2009 |
Don’t know why it would take a couple of paragraphs. Tom Kyte nailed it in 3 sentences:
A consistent get is a block gotten in read consistent mode (point in time mode). It MAY or MAY NOT involve reconstruction (rolling back).
Db Block Gets are CURRENT mode gets — blocks read “as of right now”.
Comment by Kirk — June 9, 2009 @ 11:42 pm BST Jun 9,2009 |
Sorry,
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.
[Edited for clarification on undo blocks]
Comment by Jonathan Lewis — June 10, 2009 @ 8:08 am BST Jun 10,2009 |
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.
I think that Mark Bobak’s explanation is hard to beat.
Comment by Charles Hooper — June 9, 2009 @ 11:53 pm BST Jun 9,2009 |
Charles,
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.
Comment by Jonathan Lewis — June 10, 2009 @ 8:16 am BST Jun 10,2009 |
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?
Comment by Charles Hooper — June 10, 2009 @ 10:55 am BST Jun 10,2009 |
Charles,
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”.
The (I hope) correct explanation in this case isn’t really important, it’s just interesting.
Comment by Jonathan Lewis — June 10, 2009 @ 11:42 am BST Jun 10,2009 |
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 :)
Comment by prodlife — June 10, 2009 @ 1:27 am BST Jun 10,2009 |
Chen,
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. ]
Comment by Jonathan Lewis — June 10, 2009 @ 8:34 am BST Jun 10,2009 |
Chen,
I didn’t know the answer to the question about log_io myself, so I’ve just done a quick check. FYI – on 10.2.0.3 it seems to be “db block gets” + “db block changes”.
Comment by Jonathan Lewis — June 11, 2009 @ 6:40 pm BST Jun 11,2009 |
“consistent gets” & “db block gets” are block get requests in “consistent” (as of SCN) and “current” (most recent version) modes accordingly which required CBC latch get.
Comment by Timur Akhmadeev — June 10, 2009 @ 8:08 am BST Jun 10,2009 |
Timur,
Interesting point there – alluding to the block visits that are neither “consistent gets” nor “db block gets”.
Comment by Jonathan Lewis — June 10, 2009 @ 8:23 am BST Jun 10,2009 |
My senior DBA taught me that Consistent get are logical read (say from buffer/cache) while DB block gets are access to disk.
Now looking on the net I get a bit confused as it seems that both can be done from “RAM” and the idea of “consistent” is not related to cache.
Confused but…quite amused! ^_^
Comment by lascoltodelvenerdi — June 10, 2009 @ 8:27 am BST Jun 10,2009 |
Lascoltodlvenerdi,
That point of confusion could be why 10g has three separate statistics about db block gets:
Comment by Jonathan Lewis — June 10, 2009 @ 8:36 am BST Jun 10,2009 |
Well,
I started using Oracle since V7.
I’m “confused” by the definition that my senior DBA give me years ago: consistent=from ram db, block gets=from disk.
I’m thinking that “senior” was about his age… lol! ^_^
Seriously he taught me a lot of things, some were 100% right others a bit less.
Must check those “db block gets%” for our next Oracle 11g server.
Comment by lascoltodelvenerdi — June 10, 2009 @ 9:31 am BST Jun 10,2009 |
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.
Comment by Jonathan Lewis — June 10, 2009 @ 11:49 am BST Jun 10,2009 |
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?)
Comment by Narendra — June 10, 2009 @ 9:16 am BST Jun 10,2009 |
Can you post complete output of Statistics session? Maybe that was due to recursive queries?
Comment by Timur Akhmadeev — June 10, 2009 @ 11:11 am BST Jun 10,2009 |
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.
Comment by Jonathan Lewis — June 10, 2009 @ 11:21 am BST Jun 10,2009 |
When I carried out the same test on a 10g database, “DB Block gets” were reported as 0 (which makes sense).
Comment by Narendra — June 10, 2009 @ 9:38 am BST Jun 10,2009 |
The 1st and 3rd notes are seems to be reasonable to me. About the 2nd I’m not sure – do you mean direct-path operations?
Comment by Timur Akhmadeev — June 11, 2009 @ 11:27 am BST Jun 11,2009 |
Timur,
No – I was thinking about operations that were on view, in public, in the buffer cache.
Comment by Jonathan Lewis — June 11, 2009 @ 12:17 pm BST Jun 11,2009 |
Well, that’s interesting – how to change a data block in DB’s cache without generating redo.
Comment by Timur Akhmadeev — June 11, 2009 @ 12:53 pm BST Jun 11,2009 |
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.
Comment by B. Polarski — June 11, 2009 @ 12:07 pm BST Jun 11,2009 |
Brian,
(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.
Comment by Jonathan Lewis — June 11, 2009 @ 12:25 pm BST Jun 11,2009 |
“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.
Comment by B. Polarski — June 11, 2009 @ 2:29 pm BST Jun 11,2009 |
Brian,
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.
Comment by Jonathan Lewis — June 11, 2009 @ 4:46 pm BST Jun 11,2009 |
[…] 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 […]
Pingback by Consistent Gets – 2 « Oracle Scratchpad — June 12, 2009 @ 8:45 pm BST Jun 12,2009 |
[…] data since it is the version of the block as it exists at that instant. See these blog posts: https://jonathanlewis.wordpress.com/2009/06/09/quiz-night/ […]
Pingback by Statspack/AWR Report Resources « Charles Hooper's Oracle Notes — December 14, 2009 @ 2:11 pm GMT Dec 14,2009 |