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 the thread because my original answer was actually pretty good. (And the answer to the follow-up is also worth reading).
Update: If you’re looking at this note and haven’t expanded the view to see the comments from readers, make sure that you do look at them since they include a couple of technical details I described in response to further follow-up questions on the OTN thread.
Oracle Scratchpad 
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 GMT Dec 24,2010 |
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 GMT Dec 27,2010 |
So that would be 200 change vectors???
I still don’t understand.
Comment by Hans-Peter Sloot — December 28, 2010 @ 7:59 am GMT Dec 28,2010 |
Hans-Peter,
Does it help if I say: “an undo record is described by a redo change vector” ?
Comment by Jonathan Lewis — December 28, 2010 @ 3:35 pm GMT Dec 28,2010 |
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 GMT Dec 28,2010 |
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 GMT Dec 29,2010 |
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 GMT Dec 30,2010 |
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 GMT Dec 29,2010 |
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 66560If 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 0Just 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 0Notice 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:
Comment by Jonathan Lewis — December 30, 2010 @ 1:21 pm GMT Dec 30,2010 |
[…] 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 GMT Jan 3,2011 |
[…] 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 |
[…] 1 IMU undo allocation size 1,000 出处：https://jonathanlewis.wordpress.com/2010/12/23/private-redo/ […]
Pingback by IMU&PRVS（译） | 数据块的DBA之路 — June 30, 2011 @ 5:10 pm BST Jun 30,2011 |
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 |
berusadia,
See my reply to your other post: https://jonathanlewis.wordpress.com/oracle-core/oc-2-undo-and-redo/
Remember that in all versions of Oracle the redo vectors are copied into the redo buffer before any blocks are changes. This is true in all versions of Oracle; but the sequence of the redo change vectors, and how they are combined into redo records changes in 10g.
Comment by Jonathan Lewis — September 7, 2011 @ 3:44 pm BST Sep 7,2011 |
[…] 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 |
[…] 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 |
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 |
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 |
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 |
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 |
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 |
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 |
[…] presentation ‘Performance and Scalability Improvements in Oracle 10g and 11g’ Jonathan Lewis’ write-ups about private strands and ‘Oracle Core essential internals for DBAs and Developers’. Twitter: @piontekdd, […]
Pingback by Redo a blogpost | Frits Hoogland Weblog — November 15, 2016 @ 4:21 pm GMT Nov 15,2016 |