Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Jay.

Asked: March 24, 2008 - 12:38 pm UTC

Last updated: November 24, 2009 - 2:30 pm UTC

Version: 10.2

Viewed 1000+ times

You Asked


With the support of multiple block size in a database, how does a single UNDO tablespace store the before-images of data blocks from tablespaces with different blocksizes? Is there any performance impact on this?


and Tom said...

we store logical undo records, not block images

so the size of the undo "block" is not relevant.

for an insert of of a new row, we store "delete this rowid"
for a delete of an existing row, we store "insert this row (.....)"
for an update of an existing row, we store "update current_row to this old_row"

it is not based on blocks, but on changed bytes.

BUT - do not use multiple block sizes for anything other than transporting data from database A to database B where the block size in A is different from B. No silver bullets with this "trick", nothing you want to do in real life. The cases whereby multiple blocksizes are useful are typically limited to benchmarks, old wives tales, and very exceptionally rare conditions.


  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment



Jan, March 27, 2008 - 10:41 am UTC

It seems to be optimised for rollback. But how are the blocks (consistent gets) recontructed from undo logical records for SELECT statments? It must be expensive.
Tom Kyte
March 27, 2008 - 11:01 am UTC

no, rather it is optimized for COMMITTING actually - we expect transactions to commit.

but read consistently is "rolling back" - a read consistent block is just rolled back by applying the undo to it.

We get a block, it is too "new", needs to be rolled back, the transaction history on the block header points us to the first undo bit of data to apply, we apply it - is block still "too new"? YES-do it again (we just rolled back the transaction history as well, we have what we need). NO-you are done, use this block.


Jay, March 27, 2008 - 11:44 am UTC

So to reconstruct a before image, especially that of a few versions ago, Oracle will have to go through all the logic operations (delete, insert, update) in memory everytime it needs it?

I wish I had read your "BUT" section earlier. We have a database which was initially created with 8K blocks. Then when we added several really wide tables (with many columns) we added 32K-block tablespaces. I had to increase SGA to accomodate the new buffer cache. Besides that I hope it won't degrade performance if it doesn't help with it at all.

Tom Kyte
March 27, 2008 - 1:54 pm UTC

Oracle has to apply the undo to the blocks - yes.

undo "block" size not relevant

Carl, July 10, 2008 - 10:53 am UTC

Hello Tomas,

You wrote that "so the size of the undo "block" is not relevant."

Based on that, how can we determine the size (in KB or MB ...) of the UNDO generated by a DML?


Tom Kyte
July 10, 2008 - 12:50 pm UTC

You have to read it in context.

question: ... With the support of multiple block size in a database, how does a single UNDO tablespace store the before-images of data blocks from tablespaces with different blocksizes?

The question asker was under the mistaken belief that we plopped entire before images of blocks in the undo segment

to which I said "not relevant, we do not record BLOCKS there, we record data"

So, to determine the size of undo you generated, you would use used_ublk in v$transaction typically - which of course you need to multiply by the very relevant block size in order to get to bytes...

Amount of UNDO

Carl, July 10, 2008 - 1:49 pm UTC

Hello Tomas,

Based on the value of used_ublk (v$transaction) how a table (having no indexes) that occupy 2.2 GB and 292224 blocks can generate approx. 4GB of UNDO and 529654 undo blocks?

The DML is an UPDATE on the entire table to modify one column only.

Is it normal?

Best regards.

Tom Kyte
July 10, 2008 - 2:21 pm UTC

undo is a data structure, it is a complex data structure.

so sure, no reason why it could not, would not, should not be larger than the input data itself.

High db file sequential read

A reader, November 24, 2009 - 1:11 pm UTC


one of the insert DML statement is taking lot of time {20 sessions running parallel to run this same DML }. The high wait is on "db file sequential read". I was wondering creating an index on a 16K tablespace to reduce this high USER/IO wait. But seeing your answer to the original question i am wondering whether my approach is correct.


Tom Kyte
November 24, 2009 - 2:30 pm UTC

... I was wondering creating an index on a 16K tablespace to reduce this high
USER/IO wait...

umm, that would probably INCREASE it. close your eyes and envision what must happen.

You have a really large index - say it is on last name (not always increasing, sometimes you insert names with A, sometimes with Z sometimes with K - all over the place)

Therefore, any of the leaf blocks could be touched (not like a sequence populated field where the inserts would just go on the right hand side).

And in actuality - ALL of the index blocks are being touched - since you have 20 sessions inserting randomly scattered data.

And they do not all fit in the cache with everything else going on.

If you make them larger - they still all have to fit, but now each is taking twice as much space (true, there are half as many, but that doesn't solve anything).

Now, since the blocks are larger - you likely increase the probability that when you go to look for block 'X' and do not find it - that the cache will be full - and you'll have to flush out some index blocks - which you need to re-read again soon and so on. But now you are paging with 16k blocks, not 8k blocks. So the units of IO are larger. You might wait for fewer numbers of db file sequential reads - but you'll wait LONGER for each one because they are twice as big.

So, what to do?


do not rebuild the index, just set up a keep pool for it and let it have its own memory region. If you make this larger than large enough - you could well reduce the number of total re-reads on the index.

partition the table by some increasing value, locally partition the index that is giving you a problem. For example, suppose the records are all inserted with a default of SYSDATE in some column. Partition the table by that column into weekly partitions (for example, maybe daily, maybe monthly, maybe hourly - I don't know, you figure out what keeps them small enough). You will now have an index partition by week. You only touch the current week in your inserts (they all hit the newest partition). That index segment probably fits into the cache. This will help the inserts - you need to make sure it doesn't hurt the queries!!! If your queries do not utilize this date column in their predicate, we'll have to index range scan ALL of the index partitions.

instead of 20 processes doing insert.... have a single process do a 20 way parallel direct path insert. We maintain the indexes very differently with a parallel direct path insert - each of the insert threads will create a mini index on just their data and then merge that in bulk with the existing index. This will minimize re-reads of blocks in a huge way - because we'll process ALL of the A's for a thread at once, then the B's then the C's and so on

those are three ideas for you to think about..

add to the previous review

A reader, November 24, 2009 - 1:19 pm UTC

The insert is as below

insert into process_Tbl
select col1, col2....
from tbl1, tbl2, tbl3
where clause.

The wait is on index block of tbl1. I am planning to create index on a 16K tbl space.