jump to navigation

June Management & Infrastructure meeting June 5, 2009

Posted by mwidlake in Meeting notes, Perceptions.
Tags: , ,
add a comment

Wednesday this week saw the first UKOUG Management & Infrastructure meeting special interest group meeting (MI SIG) of the year – postponed from April due to a clash with the G20 summit. You can see details of the meeting here where {if you are a member of the UKOUG} you can download some of the papers.

I feel the MI SIG has been struggling a little over the last few meetings – too many sales pitch presentations and numbers hit by travel woes (we just fell unlucky) and then the recession – but this meeting felt better. We had a good mix of strong presentations and numbers were up. The number of expected delegates has been very variable over the last 2 weeks, at one point we were full (50 people) but then some dropped out, others registered late. We ended up with 39.

At the last meeting I asked if people wanted more on the management side and the majority did, so I pitched a presentation about how to be a Manger in IT. It would have been relevant to management in other disciplines but IT does have some unusal aspects, one being that there is a much higher percentage of introvert and logical personality traits amongst IT people. Soft Skills and considerations of personality do tend to get short change in technology environments too.

Not only did I pitch a touchy-feely topic but I also went powerpoint-naked. I put up a half dozen intro slides and then turned off the projector and just talked. I was more than a tad anxious that this could have fallen flat and ended up as me spewing random drivel from the front, but the audience took up the topic and started chipping in. It snowballed and became a general discussion. I managed to keep it flowing and mentioned most of the things I wanted to include and also took a lot of input from the audience. Maybe one or two of the links I made to add my intended points were a bit tenuous but heck, it was the first time I’d done a free-form presentation like that for years and years.

Not only was the session a success {phew} but it seemed to set the pattern for the rest of day. We had had some good questions being asked of John Nangle during his opening presentation on Exadata (I’d really like to get my hands on one of those units) but after the free-form session everyone seemed to be talking to each other more and all presenters had questions and little discussions to deal with during their sessions. They all dealt with them well.

We rounded off the meeting with a drink in a local hostelry for those who were inclined and the discussions kept going. The general feeling was that it had been an excellent day with people being a lot more interactive than normal. I know other SIGs use “speed chatting” and other things to help encourage people to talk to other delegates. They have found that such things might not initially be popular {what! you want me to talk to strangers?} but always give the meetings a greater feeling of interaction and delegate feedback is that they are {sometimes reluctanatly} recognised as helpful.

I think I’ll try and have some sort of interactive or ice-breaking aspect at future meetings as it seems to really help the day be a success.

What are Consistent Gets? June 2, 2009

Posted by mwidlake in performance.
Tags:
29 comments

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.

Follow

Get every new post delivered to your Inbox.

Join 161 other followers