In-Memory Undo - IMU

Overview

In-Memory UNDO (IMU) became a standard feature starting with the Oracle 10g. Need for the IMU was due the problem related to Segment Management. Segment Management Consumes a lot of resources like memory, cpu and IO. With introduction of IMU, Oracle offloads some of the Segment Management cost that eventually helps in CPU or Memory allocation.

Oracle uses UNDO segment for two main purposes –

1. To allow a transaction to rollback so the database so the database looks just like it did before the transaction started.

2. Whenever a query starts and ends the result will match what the database looked like when the query started.(Read Consistency)

Traditional UNDO Management (Prior 10g)

• Traditional UNDIO Segments uses physical blocks, just like any other segment.

• If a query needs to read an undo block to maintain read-consistency, it still has to go through all the latch and potentially I/O overhead of any other query.

• Additionally, undo block changes also must maintain redo information for recovery purposes.

• This creates too much of overhead on the resources and can impact the performance. Clearly there exists an opportunity for increased performance

In-memory undo

• IMU replaces undo blocks with an in-memory control structure called an IMU node.

• Instead of writing a change to an undo buffer, undo is written to the IMU node and persists in memory even after commit.

• A read-consistent query needs none of the overhead associated with physical blocks, so speed is dramatically improved.

• Since IMUs are not undo segments, their changes do not generate redo. So IMU will reduce the amount of redo an instance generates.

• Tests not only show that using IMU helps in the performance but also show that the queries using IMU were more consistent execution time. It might be because there was less segment management to do.

• Parameter to set In-Memory Undo is _in-memory_undo (default is TRUE).

• Monitoring of IMU can be done through V$SGASTATS, V$SYSSTAT and V$LATCH_CHILDREN

Please refer a very good document presented by Craig A. Shallahamer -

http://www.oracle.com/education/america_eblasts/fy09/q1/craig_shallahamer_all_about_oracle_s_in_memory_undo.pdf