Oracle Scratchpad

June 12, 2009

Consistent Gets – 2

Filed under: Infrastructure — Jonathan Lewis @ 8:45 pm GMT Jun 12,2009

How would you describe “db  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 dissection.

Before supplying my descriptions, I’d like to point out that there are (at least) two other mechanisms that Oracle uses to visit data blocks in the buffer cache: re-visiting pinned buffers, and doing a fast cleanout on commit. These buffer visits can be seen in the statistics: “buffer is pinned count” and “commit cleanouts successfully completed”. (The statistic “commit cleanouts” tells you how many times Oracle tried to do a commit cleanout and there are various statistics to explain the failures.)

Whan I asked the question originally, I pointed out that it is possible for Oracle to change a block without recording any type of “get” and without generating any undo or redo; that’s the “commit cleanout”. Strictly speaking I wasn’t telling the exact truth, the cleanout is completed by the next person to update the block, and it’s the next person that generates the relevant undo and redo, so technically you could argue that the commit cleanout is the source of some undo and redo, even though its appearance is delayed.

So, back on point:

A db block get is used when Oracle has to see the most recent version of a block, including all the uncommitted changes – but “commit cleanouts” are not considered to be db block gets even though the block has to be the most recent version of the block.

The two commonest reasons for db block gets are:

  •  access to a block to change the data – you should only change the latest version of the block and …
  •  access to index blocks to check uniqueness and  referential integrity constraints – behind the scenes, your session  needs to see uncommitted changes from other sessions (even though you, the end-user aren’t allowed to see these changes) in case you are trying to make a change that will become illegal when the other session commits. This conflict is often the cause of mode 4 waits on TX enqueues. [Paragraph updated in response to a comment below.]

A consistent get is any other “latch protected” block visit (i.e. excluding visits to pinned buffers) that does not need to see the latest version of the block.

The two commonest reasons for consistent gets are:

  • access to an undo block to find records that can be used to construct a “read-consistent” version of a data block
  • access to a version of  a data block showing only the changes that had been committed as at a given point in time (as indicated by a system change number (SCN)).

Of course, when all you want to do is give the right sort of impression about what’s going on, the “traditional” descriptions are adequate approximations: “a db block get access the latest version of a block”, “a consistent read uses undo to produce a version of a block as at an earlier point in time”. But it’s nice to be able to add more precision when you see unexpected patterns in the numbers in a Statspack or AWR report.

Here, for example, is a thought that’s often overlooked – and it’s a point I made in the earlier posting. A “read-consistent” block may be in a state that never existed in the past. Consider this scenario:

  • Session 1 updates row 1 in the block, but does not commit.
  • Session 2 updates row 2 in the block, and commits
  • Session 3 queries the block

Session 3 has to apply undo records to a copy of the block to reverse out the change to row 1. The “read-consistent” copy of the block includes the change to row 2, but not the change to row 1 – it is in a state that never existed in the past.

Once you’ve realised the significance of this simple scenario you can understand why it’s possible to see extreme numbers of consistent gets when several processes mix short and long transactions to update a small section of a database – the processes are constantly thrashing through the undo segments to create versions of a handful of blocks to see what the data would look like if the changes made by other sessions weren’t there. This effect can become particularly serious with code that does “select for update” followed a little later by “update …”

One final thought – if you’re about to update a block you acquire it with a db block get to make sure you’ve got the latest version. But before you update it you have to create a read-consistent copy to check that

  • the target rows you can see in the current block existed when you started your update
  • there aren’t any  “missing” rows that were target rows when you started the update but don’t look like targets in the current block because they have been deleted or updated by other transactions (which may or may not have committed) in the interim.

When you start to think carefully about read-consistency you sometimes wonder how the technical wizards at Oracle Corp. managed to get it working at all !

27 Comments »

  1. [...] 12th June] I’ve published my definitions in a separate note. But this is the link to the blog that started this thought running. Comments [...]

    Pingback by Quiz Night « Oracle Scratchpad — June 12, 2009 @ 9:28 pm GMT Jun 12,2009 | Reply

  2. you said ;
    The two commonest reasons for db block gets are (a) accessing a block to change the data – you should only change the latest version of the block – and (b) access to blocks to check uniqueness and referential integrity constraints – you need to see uncommitted changes from other sessions in case you are trying to make a change that will become illegal when the other session commits.

    Aren’t they same ? At the end, you try to change data for both case, don’t you ?

    Another question, Is there a way we wont get db block get for any data change ?

    Comment by coskan — June 12, 2009 @ 10:02 pm GMT Jun 12,2009 | Reply

  3. Coskan,
    I added a few words to clarify things.

    1) If you insert a row in a child table you get “db block gets” on the block, a segment header block (if it’s start of transaction) and an undo block (if it’s the first time the undo block has been used in that transaction). Those have to be “db block gets” because you are about to change the blocks.

    BUT you will also check the primary key index of the parent table to check the referential integrity – and those will be “db block gets” as well, even though you won’t be making any changes to that index.

    2) As clarified – a “commit cleanout” does not do a “db block get” (or any sort of get), but it changes the block.

    Comment by Jonathan Lewis — June 12, 2009 @ 10:12 pm GMT Jun 12,2009 | Reply

  4. You have not linked to the original blog entry. I suspect it is because the author is shown to be very ignorant about db_block_gets?… but I won’t mind if you are pointing the finger at me :-)

    Comment by mwidlake — June 13, 2009 @ 9:47 am GMT Jun 13,2009 | Reply

  5. Jonathan,

    You wrote: “The “read-consistent” copy of the block includes the change to row 2, but not the change to row 1 – it is in a state that never existed in the past.”

    The apparent contradiction in terms stems from imprecise language used to describe the block of interest (“read-consistent copy of the block”). What one is interested in is a read-consistent subset of rows(or a partial row) that happens to reside in a block rather than the entire block as there may be multiple “consistent” blocks depending on what consistent subset of rows we are interested in.

    Of course, the Oracle reconstruction process to obtain old committed rows will produce blocks with consistent rows plus some garbage that can be ignored, as well as the fact that no such “consistent” block has ever existed, except for the performance implications you described so well above.

    Comment by Val — June 13, 2009 @ 3:28 pm GMT Jun 13,2009 | Reply

    • Val,

      I think you could refine your comment further by saying that we see a block in three ways – (a) the individual rows, (b) the set of rows for each possible transaction on the block, and (c) the entire set of rows in the block.

      Read-consistency is an operation that has to handle an entire block but is really only interested in subsets of the rows -the ones relevant to particular transactions.

      As so often, confusion arises (as you point out) by imprecise use of language that leads to inaccurate thinking.

      Comment by Jonathan Lewis — June 14, 2009 @ 6:17 pm GMT Jun 14,2009 | Reply

  6. Jonathan,

    Regarding ‘consistent get’, Does oracle use the same previous data block in memory from first current block cloned while hunting down other undo segment until first older/equal SCN found ?

    Bundit

    Comment by Bundit — June 13, 2009 @ 4:00 pm GMT Jun 13,2009 | Reply

    • Bundit,

      I’m not quite sure that I understand your question – but you may be thinking about cases where one sessions produces a CR block and another session wants an even older version.

      There are cases where a session can pick up a CR copy and roll it back further (and I believe the “single transaction” flag on the buffer header identifies those cases), but in some cases it will have to start with the current version again.

      There are several variations on the strategy for how a session manages to get to its required version as efficiently as possible.

      Comment by Jonathan Lewis — June 14, 2009 @ 6:20 pm GMT Jun 14,2009 | Reply

  7. “Strictly speaking I wasn’t telling the exact truth, the cleanout is completed by the next person to update the block, and they generate the relevant undo and redo”

    It’s true for all cleaned blocks? or just for dirty blocks, that were written by DBWR before COMMIT?
    When all blocks were cleaned by COMMIT in SGA, no undo and redo is generated by the next person, I think..

    Comment by radino — June 14, 2009 @ 4:45 pm GMT Jun 14,2009 | Reply

  8. The two commonest reasons for consistent gets are (a) accesses to undo blocks to find records that can be used to construct a “read-consistent” version of a data block
    But then what about the question that you raised in earlier post? Why is access to UNDO blocks recorded as “consistent get” when they are actually read as they exist i.e. CURRENT mode?
    Also, for those using 8i, even a simple SELECT can generate “DB block Gets” even when there are no referential integrity constraints, for the reasons that you mentioned in earlier post.
    (I know the “world” is supposed to be talking about 10g and above by default, but I believe there are still many “shops” that continue to still use 8i and will observe this behaviour, which has changed partially in 9i and completely in 10g, as you suggested in previous post).

    Comment by Narendra — June 15, 2009 @ 7:29 am GMT Jun 15,2009 | Reply

    • Narenda,

      I don’t know if Oracle ever uses older copies of undo blocks (e.g. in RAC, where the current copy may be on a different node; or to allow another user to add records to the block while you are reading them to find older records) but there are cases where at least in principle the block doesn’t have to be the current block.

      Regarding the db block gets on select. This is presumably the delayed block cleanout effect you are thinking of – which fits the definition because Oracle is changing the block and therefore ought to be doing a db block get. The surprise isn’t that 8i showed db block gets for this operation, it’s that later versions don’t.

      I’ve just run a select statement on 10g which produced 20,000 db block changes and the corresponding 20,000 redo records without reporting any db block gets. We can only assume that the rationale follows that of the commit cleanout.

      Comment by Jonathan Lewis — June 15, 2009 @ 9:38 am GMT Jun 15,2009 | Reply

  9. Regarding the db block gets on select. This is presumably the delayed block cleanout effect you are thinking of – which fits the definition because Oracle is changing the block and therefore ought to be doing a db block get.
    Jonathan,
    Thanks for your response. However, as I had mentioned in comment on your previous post, I had observed the DB Block Gets for ALL executions of the SELECT and not just initial (after creating data). Also, I had created very small no. of rows (max 1 block). My understanding was Delayed Block Cleanout will cause excessive redo generation (only for subsequent initial SELECT). Does it mean Delayed Block Cleanout will result in “db block gets” as well? I am struggling a bit to understand the correlation between delayed block cleanout & db block gets.

    Comment by Narendra — June 15, 2009 @ 3:05 pm GMT Jun 15,2009 | Reply

    • Narenda,

      I’ve added to your comment a link back to your previous comment so that people can see the example.

      The point I made there was that the db block gets you see are a very special case (in fact they may well be a bug) due to the version (8i) and the create table as select (CTAS) you are using.

      Create a larger table with 100 blocks instead of just one and the number of db block gets will still be 12 – they are all accesses to the segment header block.

      That particular bit of activity has nothing to do with delayed block cleanout – and there’ll be a short note about cleanout and related topics in a few days.

      You just have to recognise that sometimes there are special cases for reasons we cannot guess.

      Comment by Jonathan Lewis — June 15, 2009 @ 9:42 pm GMT Jun 15,2009 | Reply

  10. [...] Lewis- Consistent gets 2-How to remap_tablespace on original export import Chen Shapira [...]

    Pingback by Blogroll Report 12/06/09 – 19/06/09 « Coskan’s Approach to Oracle — June 23, 2009 @ 11:23 am GMT Jun 23,2009 | Reply

  11. [...] LIO: (a.k.a Logical I/O) – a visit to a block that is in the buffer cache, initiated by a search though a cache buffers chain while holding a latch. (See also PIO). For further reading about logical I/Os, see this note. [...]

    Pingback by Glossary « Oracle Scratchpad — June 27, 2009 @ 8:12 pm GMT Jun 27,2009 | Reply

  12. “you need to see uncommitted changes from other sessions in case you are trying to make a change that will become illegal when the other session commits.”

    I am confused! I thought that uncommitted changes in other sessions were absolutely not ever visible. I am missing something glaring, I know, but what?!

    Comment by Nigel — June 29, 2009 @ 3:55 pm GMT Jun 29,2009 | Reply

    • Nigel,

      I’ve modified the wording to clear this up. Your session is allowed to run code behind the scenes that sees the uncommitted changes even though you, as an end-user, don’t have access to those changes.

      Comment by Jonathan Lewis — June 29, 2009 @ 6:34 pm GMT Jun 29,2009 | Reply

      • That is very intersting! It also blows apart my understanding of how Oracle works! I had, rather simplistically, believed that SCN was King and that first past the post was the “winner” in transactions. I had no idea that (relatively) complex stuff like that you describe went on. Is this in the Oracle Docs.?

        Comment by Nigel — July 13, 2009 @ 1:43 pm GMT Jul 13,2009 | Reply

  13. [...] #4 An absolutely confusing definition of consistent gets, which are different from current mode gets. The consistent get, while coming from memory, may have involved one or more physical disk reads. The “db block gets” statistic is actually the same thing as a current mode get. 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. A current mode get will contain both committed and uncommitted data since it is the version of the block as it exists at that instant. See these blog posts: http://jonathanlewis.wordpress.com/2009/06/09/quiz-night/ http://jonathanlewis.wordpress.com/2009/06/12/consistent-gets-2/ [...]

    Pingback by Statspack/AWR Report Resources « Charles Hooper's Oracle Notes — December 14, 2009 @ 2:14 pm GMT Dec 14,2009 | Reply

  14. [...] of index blocks. The process running the query will do a lot of work reading undo blocks to create read consistent copies of the blocks it needs to [...]

    Pingback by Scalability Conflict « Oracle Scratchpad — July 30, 2010 @ 5:52 pm GMT Jul 30,2010 | Reply

  15. [...] have better execution plan against the Cost-Based. Using RULE vs CHOOSE the result of the consistent gets is 70% smaller than the CHOOSE. [...]

    Pingback by Oracle: Checking Object Index Leaf Blocks « — November 13, 2010 @ 5:44 am GMT Nov 13,2010 | Reply

  16. [...] References: Quick Tips Consistent Gets – 2 [...]

    Pingback by What is consistent gets and db block gets? | Xing AiMing's Home Page - 邢爱明的个人网站 — June 15, 2011 @ 7:00 am GMT Jun 15,2011 | Reply

  17. [...] 翻译自:http://jonathanlewis.wordpress.com/2009/06/12/consistent-gets-2/ [...]

    Pingback by 一致性读2-翻译jonathanlewis的一篇blog | dbafree首页 — June 28, 2011 @ 2:06 am GMT Jun 28,2011 | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers