Oracle Scratchpad

December 23, 2010

Private Redo

Filed under: redo,undo — Jonathan Lewis @ 10:17 am BST Dec 23,2010

About this time last year I wrote a short answer on OTN about Private Redo Threads and In-Memory Undo. Thanks to a follow-up question a year later I’ve been prompted to supply a link to my original answer because it was actually pretty good: OTN Thread “In Memory Undo”.

Update: If you’re looking at this note and haven’t expanded the view to see the comments, make sure that you do look at them since they include a couple of technical details I described in response to follow-up questions.

22 Comments »

  1. Hi Jonathan,

    I don’t want to be nit picking but I am confused a bit and would like to have a better understanding.

    In your first you say that redo change vectors are combined to a single redo record.
    Somewhat further below you say that inserting 10 rows in a table with 4 indexes would result in 100 undo records. Shouldn’t that be 100 vectors and 50 records?

    regards Hans-Peter

    Comment by Hans-Peter Sloot — December 24, 2010 @ 12:32 pm BST Dec 24,2010 | Reply

  2. Hans-Peter,

    Is this the passage you are thinking about:


      “Say you inserted 10 rows, one at a time, into a table with 4 indexes – this would generate 50 redo records and 50 undo records, and hit the redo latches 50 times. (That’s 5 records per row, one for the table row, one each for each of the indexes – times 10).”

    That’s 50 REDO record and 50 UNDO records.

    But each redo RECORD consists of 2 change VECTORS: one which describes a data block change (i.e. a table or index change) and one which describes an undo block change (i.e. the undo record).

    Comment by Jonathan Lewis — December 27, 2010 @ 7:22 pm BST Dec 27,2010 | Reply

  3. Hans-Peter,

    I think Jonathan was not talking about undo change vectors at all. There are just 100 redo changed vectors packed into 50 redo records and 50 undo records.

    Comment by Pavol Babel — December 28, 2010 @ 11:53 pm BST Dec 28,2010 | Reply

  4. Jonathan,

    Your statement,

    you change a table or index block – this
    requires you to generate a redo change vector

    My question, to which location Oracle put redo change vector before it get copied to redo buffer ?

    Comment by Rendy — December 29, 2010 @ 3:34 pm BST Dec 29,2010 | Reply

    • Rendy,

      If you’re thinking about the construction of the redo change vector before it goes into a private redo buffer then I’d have to guess that Oracle doesn’t bother to create it anywhere first, it can just create it in the private redo buffer.

      Comment by Jonathan Lewis — December 30, 2010 @ 1:00 pm BST Dec 30,2010 | Reply

  5. Jonanthan,

    Sorry for separting this question into another reply, again from your statement,

    The “in memory undo” buffer is actually a buffer for the redo that describes the changes to the undo.

    As of now, my understanding with IMU is undo itself, not the redo, because the redo for the undo is placed in redo strand, CMIIW.

    Thank you.

    Comment by Rendy — December 29, 2010 @ 4:10 pm BST Dec 29,2010 | Reply

    • Rendy,
      If you look at x$kcrfstrand you can see some information about redo threads (buffers), both public and private. In the example below I have 2 public threads and 10 private threads, but four of the private threads have been dynamically disabled so only 6 of the private threads are showing a buffer address.

      select 
      	indx, ptr_kcrf_pvt_strand, strand_size_kcrfa 
      from 
      	x$kcrfstrand
      ;
      
      
            INDX PTR_KCRF STRAND_SIZE_KCRFA
      ---------- -------- -----------------
               0 00                 3494400	public
               1 00                 3494400
               2 20F27C80             66560	private
               3 20F38080             66560	
               4 20F48480             66560
               5 20F58880             66560
               6 20F68C80             66560
               7 20F79080             66560
               8 00                   66560	dynamically disabled
               9 00                   66560
              10 00                   66560
              11 00                   66560
      
      

      If you look at x$ktifp you can see information about the undo buffers, including the address of the buffer and the address (when it’s in use) of the associated private redo buffer. In this example I’ve got one live transaction that has inserted three rows of about 2,000 bytes each into a table, one row at a time. The query shows the start, “current highwater” mark and usage – for the 10 IMU buffers, with one buffer showing the same information for the associated redo buffer. Notice how the redo buffer usage is about 8K while the undo buffer usage is about 1KB:

      select
      	ktifpno,
      	ktifpxcb				tx_addr,
      	ktifpupb				undo_start,
      	ktifpupc				undo_cur,
      	to_number(ktifpupc,'XXXXXXXX') -
      		to_number(ktifpupb,'XXXXXXXX')	undo_usage,
      	ktifprpb				redo_start,
      	ktifprpc				redo_cur,
      	to_number(ktifprpc,'XXXXXXXX') -
      		to_number(ktifprpb,'XXXXXXXX')	redo_usage
      from
      	x$ktifp
      order by
      	ktifpno
      /
      
         KTIFPNO TX_ADDR  UNDO_STA UNDO_CUR UNDO_USAGE REDO_STA REDO_CUR REDO_USAGE
      ---------- -------- -------- -------- ---------- -------- -------- ----------
               0 00       20FCB800 20FCB800          0 00       00                0
               1 20EFB500 20FDC400 20FDC7E8       1000 20F38080 20F3A21C       8604
               2 00       20FED000 20FED000          0 00       00                0
               3 00       20435000 20435000          0 00       00                0
               4 00       20445C00 20445C00          0 00       00                0
               5 00       20456800 20456800          0 00       00                0
               6 00       20467400 20467400          0 00       00                0
               7 00       20478000 20478000          0 00       00                0
               8 00       20488C00 20488C00          0 00       00                0
               9 00       20499800 20499800          0 00       00                0
      

      Just to demonstrate that the undo and redo buffers really are separate, and the “redo for the undo” doesn’t go into the redo buffer. This is what I get after I commmit, and then start a new transaction that deletes those three rows:

         KTIFPNO TX_ADDR  UNDO_STA UNDO_CUR UNDO_USAGE REDO_STA REDO_CUR REDO_USAGE
      ---------- -------- -------- -------- ---------- -------- -------- ----------
               0 00       20FCB800 20FCB800          0 00       00                0
               1 20EFB500 20FDC400 20FDF218      11800 20F38080 20F38428        936
               2 00       20FED000 20FED000          0 00       00                0
               3 00       20435000 20435000          0 00       00                0
               4 00       20445C00 20445C00          0 00       00                0
               5 00       20456800 20456800          0 00       00                0
               6 00       20467400 20467400          0 00       00                0
               7 00       20478000 20478000          0 00       00                0
               8 00       20488C00 20488C00          0 00       00                0
               9 00       20499800 20499800          0 00       00                0
      

      Notice how the undo buffer has now used 11KB while the redo buffer has used only 1KB.

      In passing, you may think that the volumes recorded in these structures are larger than seems necessary. You’d be right (in a way): there are overheads in these buffers that do not get copied into the redo log or undo blocks so, for example, after I committed the insert my session showed the following related stats:

      Name                                                 Value
      ----                                                 -----
      redo entries                                             1
      redo size                                            6,944      -- not 8,604
      
      undo change vector size                                256    -- not 1,000
      IMU commits                                              1
      IMU undo allocation size                             1,000
      
      

      Comment by Jonathan Lewis — December 30, 2010 @ 1:21 pm BST Dec 30,2010 | Reply

  6. [...] couple of days ago I published a link to some comments I had made on OTN about differences in redo generation between 10g and earlier [...]

    Pingback by Redo « Oracle Scratchpad — January 3, 2011 @ 7:40 pm BST Jan 3,2011 | Reply

  7. [...] ought to be slightly less than 1,000 because the first 40 or 50 should be accumulated in the private redo and in-memory undo areas. So we have about 950 redo entries too many; where did they come from [...]

    Pingback by Audit Ouch! « Oracle Scratchpad — May 27, 2011 @ 5:39 pm BST May 27,2011 | Reply

  8. [...] 1 IMU undo allocation size                             1,000 出处:http://jonathanlewis.wordpress.com/2010/12/23/private-redo/      [...]

    Pingback by IMU&PRVS(译) | 数据块的DBA之路 — June 30, 2011 @ 5:10 pm BST Jun 30,2011 | Reply

  9. Hi Jonathan,

    OTN Thread “In Memory Undo”. – in this thread , you explaned very well ..and its really excellent
    One question – the REDO /UNDO are generated (chaneg vectors are prepared) just before when Oracle is ready to change a block in “current mode”- is it correct ? (in ALL Version of oracle)

    I mean

    Oracle will get the block in current mode
    preapre the change vectors for undo/data block
    change the undo block in current mode
    chnage the data block in current mode
    commit

    Thanks for your time ..

    Comment by berusadla — September 7, 2011 @ 10:42 am BST Sep 7,2011 | Reply

  10. [...] Oracle Database Architecture, followed by a deeper dive into Redo. Some of the technical details are here, while almost everything about the presentation can be found in the second chapter of his new [...]

    Pingback by OUGN Spring Seminar Day 3 « The Oracle Instructor — March 25, 2012 @ 5:21 pm BST Mar 25,2012 | Reply

  11. [...] Oracle Database Architecture, followed by a deeper dive into Redo. Some of the technical details are here, while almost everything about the presentation can be found in the second chapter of his new [...]

    Pingback by OUGN Spring Seminar Day 3 | Oracle Administrators Blog - by Aman Sood — March 26, 2012 @ 3:45 am BST Mar 26,2012 | Reply

  12. Hi Jonathan, how are you ?
    I started reading your awesome book “Oracle Core” and now I’m runing the script core_demo_02b.sql in the page 15 of chapter 2 in Oracle Version 11.2.0.1 OEL 6.1 x86_64 .
    The results about redo copy/redo allocation/IN memory undo latch/ redo entries and redo size were not what I expected. There was some modification in Version 11.2.0.1 ? I know that things often change between versions but in this situation the results were more like the version of the Oracle 9i than Oracle 10g. Did I do something wrong?

    “I put the results on the link below to get better understanding. Sorry if this is not useful.”

    http://pastebin.com/skFJ4ga8

    Thank you

    Comment by Wellington Prado — April 23, 2013 @ 12:57 am BST Apr 23,2013 | Reply

  13. Hi Jonathan,

    I think I found the problem I mentioned above! My database uses Oracle Golden Gate and because of this I had to enable Database supplemental logging and this setting disable IMU, Am I right ?

    After I read this paper http://www.teamycc.com/rmoug_2008_conference/PDF/Haisley.pdf I saw some restrictions that disable IMU and I was very sad!. I hope that in the next releases it can improve.

    Thank you!

    Comment by Wellington Prado — April 25, 2013 @ 8:23 pm BST Apr 25,2013 | Reply

    • Wellington,

      Almost certainly correct. There are several features that disable in-memory undo and private redo; supplemental logging is one of the them.

      Stephan Haisley’s paper is an excellent source of information – at the moment the page you’ve cited seems to be unavailable, but anyone who wants more information on the topic should get hold of the paper and read it, it’s got some very interesting extras in it.

      Comment by Jonathan Lewis — June 9, 2013 @ 11:50 am BST Jun 9,2013 | Reply

  14. Dear Jonathan,

    I have read OTN Thread “In Memory Undo”. But I understand it not clearly. Please help me to confirm what I understand is right or wrong ?

    1. When make changes to table/index buffer, each redo change vector will be generated and be put into the private redo buffer (allocated in shared pool)
    2. At the same time, a undo block buffer (allocated in data buffer cache) to hold enough information to rollback the change – the undo record. And the change vector that describles the undo record is written into the “in-memory buffer”.(allocated in shared pool)
    3. After that, the table/index buffer is update to new value.
    4. If commit, Oracle concatenates the two buffers into a single large redo record and writes it into the “public redo buffer”

    Thanks
    K.P

    Comment by K.P — June 19, 2013 @ 9:37 am BST Jun 19,2013 | Reply

    • K.P.
      Stripped to the bare minimum, that’s pretty close – except that you need to reverse the order of 4 and 3.

      The moment that the changes are actually applied to the table, index and undo blocks comes immediately AFTER the copy into the public redo buffer.

      Comment by Jonathan Lewis — June 19, 2013 @ 9:52 am BST Jun 19,2013 | Reply

      • Dear Jonathan,

        Thank you so much for your response soon. I’ve understand this article.

        K.P

        Comment by K.P — June 19, 2013 @ 12:03 pm BST Jun 19,2013 | 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,173 other followers