jump to navigation

What are Consistent Gets? June 2, 2009

Posted by mwidlake in performance.
Tags:
trackback

For those of you who just want to know what Consistent Gets are or what DB Block Gets are and the difference, look here. This is a blog though, so now I will whiter on about how I know this and what I learnt looking into it. Some of it was what I learnt about me. Some of it was what I learnt about not trusting everything on the web, even if it is repeated.

I recently gave a presentation on Tuning for non-experts, aimed at giving people new to tuning some insights into getting going (derived from a course I occasionally run). As the presentation progressed, I stumbled with the statistics section of Explain Plan.

Here is an example, similar to the one I put in the presentation:-


Statistics
-----------------------------------------
3 recursive calls
0 db block gets
729 consistent gets
15 physical reads
0 redo size
1648 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
23 rows processed

I said
– Recursive calls was from parsing.
– Consistent Gets was reads from memory (sga).
{and then came the stumble}
– DB_Block Gets was physical reads from the I/O subsystem.
No, that’s wrong. I corrected myself and said
– Physical Reads is physical reads from the I/O subsystem.

I am not sure if the audience noticed, but I paused and internally I had a private conversation – “What are DB Block gets? I know consistent gets are gets from the block buffer cache. What is the difference? What in hell am I talking about?

I did what you have to try and do when you hit a moment like that when presenting and kept going. {If the confusion gelled in my head I would go back and explain. If it did not gel, I’d risk experts knowing I had messed up for the sake of a smooth presentation}. It didn’t gel. I finished the presentation, went home and I looked at it. And was vexed.

I’ve been tuning Oracle databases and SQL for one and a half decades and I am not bad at it. In fact, I’m pretty good at it. But I found myself looking at a very, very fundamental aspect of perhaps THE most common Oracle tuning tool and I realised I did not know exactly what Consistent Gets and DB Block Gets were.
When I tune I look at the plan and I look at the number of Consistent Gets, Physical Reads and Sorts going up and down. It works for me.

SO, what is the difference between DB Block Gets, physical reads and Consistent Gets? A quick scan of the documentation confirmed what I thought I already knew:-

- Physical Reads are the number of actual reads from the I/O subsystem.

- Consistent Gets and DB Block gets together are the number of block reads from Block Buffer Cache.

But what ARE Consistent Gets and DB Block Gets and what is the difference?
Google did not immediately help and the documentation on Explain Plan in the Performance Tuning Guide did not either. They basically all said:
– Consistent Gets were from the buffer cache.
– DB Block Gets were CURRENT blocks requested from the buffer cache.

What is CURRENT? I’m selecting data that has not changed, it is current, but it all shows as Consistent Gets?

So I looked slightly differently. I know these are used in calculating the cache hit ratio, so I look up the cache hit ratio:-

1 – ((‘physical reads cache’) / (‘consistent gets from cache’ + ‘db block gets from cache’)

And from the documentation about V$SYSSTAT:-
consistent gets from cache – Number of times a consistent read was requested for a block from the buffer cache.
db block gets from cache – Number of times a CURRENT block was requested from the buffer cache.

GRRRRRR. What is the point of a description that just repeats the words in the name of something? “Fellmonger – A Fellmonger is a Monger of Fells”*. What is the point of a description that says “x is a Wibble” and not being clear about Wibble?

I dug deeper on the net and got this:-

“consistent gets is the blocks in consistent mode (sometimes reconstructed using information from RBS). So this reconstruction from RBS takes more resources (reads actually), which will end up as high consistent gets.

db block gets is the blocks in current mode (whatever it is NOW).”

Great, The answer!

Errr, is it? I always see lots of consistent gets {and I think of it as logical reads from memory}. The data has not been changed in a while. I don’t think the information needs reconstructing.
I do not think this information off the web is right.
Reading the above, I would expect to see lots of DB Block Gets as I DO want the information which is current, as it is ”NOW”, as the person said.

The expert is trying to help, and we all get things wrong, but he is not quite on the mark I think {it turns out he was more on the mark than many others though}

I dug more and I kept getting more of the same stuff, over and over again. As an example:-
{link removed, I’m not trying to target any individual here}

Someone posts the question about what are consistent gets. Someone replies saying the same thing, often with different words, about “DB Block Gets being got NOW” and “Consistent Gets are where work has had to be done to get the correct image of the data. {My italics}.
Usually, the original question asker , confused, says “but the data has not changed” or “why do I see so much reconstruction” and the original person backs away – “it is not always that all the data is changed”, “I am not an expert on the exact details of get types”.
People don’t really know. Right now, I don’t know. I used to think I did but I don’t. The question responders are simply quoting from someone else who answered the question before.

But then I find a reference to Ask Tom. He knows. Usually :-)
This is the start of the thread

It is a long thread, packed full of great stuff.

WHOA! suddenly my memory is kicking in and reminding me of stuff I knew 10,15 years ago {one of the issues of being an old duffer}. In amongst other stuff, Tom is answering my question.

From his entry:-

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”.

So, all those references to Consistent Gets being where the data is reconstructed had taken part of the answer, not understood it and an Oracle Urban Myth is brought into being.

Tom probably puts it better, but it is buried in a lot of {very interesting} other stuff, so here is my take on it.

A Consistent Get is where oracle returns a block from the block buffer cache but has to take into account checking to make sure it is the block current at the time the query started.

Oracle fetches pretty much all of its data that way. All your data in your database, the stuff you create, the records about customers or orders or samples or accounts, Oracle will ensure that what you see is what was committed at the very point in time your query started. It is a key part to why Oracle as a multi-user relational database works so well.
Most of the time, of course, the data has not changed since your query started or been replaced by an uncommitted update. It is simply taken from the block buffer cache and shown to you.

A Consistent Get is a normal get of normal data. You will see extra gets if Oracle has to construct the original record form the rollback data. You will probably only see this rarely, unless you fake it up.

But I am still not clear on CURRENT mode.

More reading, through the Ask Tom thread and a couple of other places, and it becomes more clear. This is Oracle internally getting data where it does not have to bother checking for reconstructing the data from rollback information. Oracle can just get whatever is currently correct. It seems to be always internal information, such as the segment headers for a table – info on where a table’s blocks are held. But don’t hold me to that, I can’t prove it is always internal.

What does seems to be true is that different version of Oracle report different numbers of DB Block Gets for the same action. Also, different options in Oracle (ASSM, IOTs maybe) alter the number of DB Block Gets reported.
Even Tom Kyte seems to throw his hands in the air and say “look, sometimes DB Block Gets get reported, sometimes they don’t, but you can’t do much about them as they are needed for Oracle to internally handle the structures holding your data”.

In summary.
Consistent Gets – a normal reading of a block from the buffer cache. A check will be made if the data needs reconstructing from rollback info to give you a consistent view but most of the time it won’t.
DB Block Gets – Internal processing. Don’t worry about them unless you are interested in Oracle Internals and have a lot of time to spend on it.
Physical Reads – Where Oracle has to get a block from the IO subsystem

From a performance perspective:-
Reducing Physical IO {if there is any} is very often a very good way to make a statement run a lot faster.
Reducing Consistent Gets is often a good way to make a statement run faster.
Reducing DB Block Gets is probably beyond what you can do (except as a side effect of changes to the execution plan). It won’t make a lot of difference.

It took me 1/4 of a day to get to the bottom of that. Hopefully this page will save someone else that 1/4 day.

* A Fellmonger is someone who removes hair from animal skins before tanning them. It was a big deal when we all wore animal skins to stop us dying of exposure.

About these ads

Comments»

1. Neil Chandler - June 2, 2009

I have been a DBA since I was thin and didn’t have any grey hair, and I didn’t know that I didn’t know that.

If you have a copy of that performance tuning for non-experts, I might have to try to bribe it out of you using the medium of alcohol.

Hopefully see you at the M&I SIG, providing I get this system working properly between now and tomorrow and get home to get some sleep.

2. B. Polarski - June 10, 2009

I always under the impression that ‘DB Block Gets’ was similar to ‘last version of the block’ + plus the fact that a markers tells this bloc has not been visited by any DML process (dirty).

In this respect a ”DB Block Gets’ is a block whose image is guaranteed in sync with the one in disk. And the description of the statistics : ‘db block gets from cache – Number of times a CURRENT block was requested from the buffer cache’ makes also more sense for the work ‘requested’. The system requested the Last version of the block and got it from memory.

mwidlake - June 10, 2009

Thanks for the feedback B. Polarski.
If you think about it, any block that is read into the SGA and has an SCN (System change number) that is less than when your query began would count as “last version of the block” and would be counted as a DB Block Get if your impression is correct. But you get very few “DB Block Gets”.

Also, if a “DB Block Get” was one where it was “guaranteed to be in synch with the disk”, this would be most blocks read most of the time, once oracle has checked if there has been any change to it since it was read. Which is part of what a Consistent Get is, checking to make sure the block you are fetching from memory has changed and then when. If it has changed it has to make it consistent with the point in time you read it.
I think whether the block has been written back to disk is not that important to Oracle, it is whether information about the change has been written to the online redo log. Oracle is “lazy” in flushing back blocks to disk for performance reasons. I think of it as the DB Writer processes scurry along behind the database activity, writing it all to disk.

3. Consistent Gets, db block gets and DML « Martin Widlake’s Yet Another Oracle Blog - June 11, 2009

[...] Tags: db block gets, explain plan, performance trackback A few posts back I talked about consistent gets, db block gets and how I had realised I had got by for years without fully undertand…. I then described how I went about learning [...]

4. Seka Mukasa - June 11, 2009

Excellent piece Martin!! I’ll freely admit I didn’t know the difference until now. Interestingly though, what is the scenario with read only tablespaces? There is no possibility of reconstruction here, so will it be reported as consistent get or db block gets?

mwidlake - June 11, 2009

Thanks for the feeback Seka. That is a very good point about read only tablespaces. My guess is that it’s still going to be a consistent get, but I’m all of 51% sure of that. I’m intrigued now and I don’t like the fact I’m having to guess, I’m going to try it out and see. If you buy me a pint I’ll tell you the answer next week… :-)

5. Richard - April 22, 2010

Great stuff! I’ve been looking for this for 5 years now ;-). Every time I found conflicting info I just gave up, so thanks for being thorough.

6. emrah - January 12, 2011

Hi Martin,

Nice article.

The db block gets still remains a mystery.

In 11g I take a table read only and perform a full table scan.

sql> select * from my_read_only_table;

guess what! 0 db block gets. All of the logical reads are consistent gets.

7. Paresh - April 15, 2011

Very informative post Martin. At last I think I know know the real description of Consistent Gets and DB Block Gets, till someone comes with even a real realer info (joke). This looks like a definitive explanation.

Thanks

8. Sumeet - April 18, 2011

Very well researched & articulated . One point remains – though . For all practical tuning purposed can we safely ignore “db block gets from cache” ?

mwidlake - May 15, 2011

Hi Sumeet,

I’m probably going to regret saying this but….Yes, you can safely ignore DB block gets for tuning purposes.

UNLESS you see a significant percentage of them – If you see more than say 15 or 10% of the value of consistent gets, you may well want to ask why. It is not covered in this blog post but I did look more into DB Block gets and came to the conclusion that if there are lots of them then you might want to take extra care that the actual response time of the query is OK. Something odd could be going on and that could result in performance issues as you are reading stuff that Oracle knows is in memory and is not changed on disc. Like you are selecting back data you have just changed.

9. What is consistent gets and db block gets? | Xing AiMing's Home Page - 邢爱明的个人网站 - June 15, 2011

[...] on 2011/06/15 by adam An excerpt from What are Consistent Gets? Consistent Gets – a normal reading of a block from the buffer cache. A check will be made if the [...]

10. garrymoore - November 24, 2011

hi to all mwidlake.wordpress.comers this is my frst post and thought i would say hello to you all –
thanks speak soon
g moore

11. dual101 - January 7, 2012

Excellent article.

12. William Gaynor - January 29, 2012

I’ve been trying to calculate a % read miss ratio, inclusive of cache and direct reads. Such as % read miss is good for understanding the effective of introducing SSD technologies. The greater the read miss % the more effective SSDs might be for storing Oracle database data. Other factors are also important, but they are another story, Disclaimer: I work to EMC as an Oracle SME.

Starting insight is how Oracle recommends calculating the cache hit rate:
Form Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Part Number B14211-03
1 – ((‘physical reads cache’) / (‘consistent gets from cache’ + ‘db block gets from cache’)

Where:

consistent gets from cache — Number of times a consistent read was requested for a block from the buffer cache.
db block gets from cache — Number of times a CURRENT block was requested from the buffer cache.
physical reads cache –Total number of data blocks read from disk into buffer cache.

Over time Oracle has differentiated “from cache” vs. “direct.”

So logical reads = consistent gets + db block gets
= consistent gets from cache + consistent get direct + db block gets from cache + db block gets direct
Ditto, physical reads = physical reads from cache + physical reads direct

Having looked at a few AWR/Statspack reports these number add up, give half a decimal point or so.

consistent gets = consistent gets from cache + consistent get direct
db block gets = db block gets from cache + db block gets direct
and logical reads = consistent gets + db block gets

So it looks like my %read misses should be:
% Percentage physical reads to total reads = 100*(physical reads per sec / (logical reads per sec + physical read per sec).
bg

13. William Gaynor - January 30, 2012

Actually it looks like my %read misses should be:
% Percentage physical reads to total reads = 100*(physical reads per sec / logical reads per sec)

14. Brian Paffen - April 25, 2012

I usually only pay attention to the explain plan, physical reads and where the sorts happened. It just hit me while reading a tuning book that I didn’t clearly know the difference between db block gets and consistent gets either. I just knew that they were both coming from the buffer cache, which isn’t a bad thing. Your blog was the first post I read when I Googled, so thanks for the easy lesson. You definitely saved me some time.

mwidlake - April 25, 2012

Glad to have helped Brian.

If you want to really understand about consistent gets and just generally how the oracle DB works, I suggest you get a copy of Jonathan Lewis’s book “Oracle Core” – I wish he’d written it and I read it about 10 years ago!

Brian Paffen - April 25, 2012

Just picked it up and added it to my nightly reading list. Thanks much for the tip.

15. Greg - April 29, 2012

I had read the the Tom Kyte article before arriving here. I think many of the comments made here still show one confusion that Tom had to work hard to put across. That is, that Consistent Gets are gets made in “Consistent Mode” and DB Block gets are gets made in “Current Mode”. Its the “intention of the get” that puts it in one category or the other, not the outcome.

mwidlake - April 29, 2012

Hi Greg,

Thanks for that. Yes, you sum it up very nicely (I do tend to go on in my blog posts).

A “consistent get” is where oracle must ensure the data is as it looked at a point in time. This may involve reconstructing the data – but most of the time, it does not.

A DB Block get is where Oracle knows there will be no need whatsoever to reconstruct what the data would have looked like at a given time. Oracle just wants what the block is now, as that is the information Oracle needs.

If anyone wants to understand all of this more, I can do no better than suggest getting Jonathan Lewis’s latest book, Oracle Core.

16. Mark Fleming - September 5, 2012

Thanks for that. If i may add … The word ‘consistent’ relates to ‘read consistent image’ (ie. image made up of both the table data and the undo segment data together, in order to present the state of data as of the last commit). All this talk (not yours) about ‘point in time’ and ‘as of right now’ just confuses a simple issue.

17. Buntri Guy - September 12, 2012

Thanks for the article mwidlake . Now it makes sense.

18. Marcus - September 24, 2012

Excellent summary of this topic. It’s always hard to get good and helpful information on topics like these. It’s that typical stuff that almost everbody can explain up to 95% – but the final details are almost impossible to find.

I really had to laugh out loud when I read the sentence about the fellmonger. It’s something I’ve also been complaining about for a long time: people “explaining” something by just repeating the word to be explained and putting in a few fill words to form a complete sentence – I could scream every time I see it. Unfortunately I’d say that’s exactly what you find in every second online help file…

So, perfect: Now we know what a block get is AND we learned about fellmongers! ;-)

19. aiglefin - October 12, 2012

Hi Yannick,

Thanks you for your conclusion.
I almost read 90% of this article with the reference to Ask Tom which contribute to give me another headache; after i driil down and try to understand the examples. I have one question : how explain you the effect of arraysize on the value of gets consistent reads.

Best Regards!

20. felice - October 25, 2012

Excellent summary.Thanks.

21. pavol - August 19, 2013

excellent article. thank you very much for sharing

22. FG - July 7, 2014

Nice information.I also feel that at Oracle Docs the definition is used to explain the definition itself,sometimes.(as in this case)

23. Chan - August 7, 2014

Thank you. It is really helpful.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 156 other followers

%d bloggers like this: