What are Consistent Gets?

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.