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.
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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC May 27,2011 |
[...] 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 UTC 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 UTC Sep 7,2011 |
berusadia,
See my reply to your other post: http://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 UTC 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 UTC 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 UTC 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 UTC 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 UTC Apr 25,2013 |