IMU or In-Memory Undo * Good references is Craig Shallahamer's paper "All About Oracle's In-memory undo" www.oracle.com/education/america_eblasts/fy09/q1/craig_shallahamer_all_about_oracle_s_in_memory_undo.pdf and Jonathan Lewis's message http://forums.oracle.com/forums/message.jspa?messageID=3974313#3974313 * Related patent "Method and mechanism for implementing in-memory transaction logging records" www.freepatentsonline.com/6981004.html * Main parameter is _in_memory_undo. But the value true doesn't mean IMU is definitely enabled. o "turning supplemental logging on disables IMU", regardless this parameter (See Note:428167.1). o In RAC, IMU is disabled. o Since private redo strands is technology close to IMU, supplemental logging disables private strands too; Ref: Bug 8680790. o Flashback database disables IMU; Ref: http://www.teamycc.com/RMOUG_2008_Conference/PDF/Haisley.pdf o Redo logfile size smaller than 50 MB turns off IMU (Ref: Lv Haibo's Oracle Internals book, p.300. The book also mentions two other cases: too much redo for IMU or private redo strand area, failure to acquire IMU latch on first trial) Other related parameters are: _imu_pools (3 since 10gR1) _recursive_imu_transactions (false since 10gR1) _db_writer_flush_imu (true since 10gR1) * Statistics related to IMU are all named like 'IMU%' * There's latch "In memory undo latch", which has its children. * Steve Adams, "some redo generation will not be seen in the stats for the session responsible" www.freelists.org/archives/oracle-l/01-2008/msg00000.html * According to Craig Shallahamer's paper, current IMU memory allocation in shared pool has a name "KTI-UNDO". I'm not sure what "I" in "KTI" means, maybe in-memory. (Not sure because Oracle code has functions called ktiNonImuPool(Allocate|Free) in addition to ktiImuPool(Allocate|Free). See Yurichev's Oracle function list at conus.info/oracle/oracle-10.2.0.4-linux/kti.txt) Things related to KTI are (a) event 30047 (kti tracing; level may be number of buffers in size of 512 bytes, Ref: Bug 5387979) (see also: event 30048 is for IMU auto-tuning); (b) lock type IM (see v$lock_type); (c) fixed tables: X$KTIFB X$KTIFF X$KTIFP X$KTIFV When IMU is used, you'll see non-zero numbers in KTIFFFLC column of X$KTIFF (e.g. "Commit flushes" goes up by 1 every 5 seconds). As soon as you turn off IMU by setting _in_memory_undo to false with alter system, these tables are emptied. But when you set it back to true, the original numbers are shown again and "Commit flushes" was still incrementing, as long as you didn't bounce the database in between. * It's not clear to me whether Craig's article or the quoted patent answers the question whether redo size will be reduced with IMU. The patent says "A benefit of this approach [i.e. IMU] is that it significantly reduces the costs for writing redo information to disk, since the entire set of redo information items can be written as a large block or as a single record, rather than requiring separate I/O operations for writing individual redo records." It means with IMU, multiple otherwise smaller redo writes can be bundled into a larger one. But it doesn't say if the total redo size in bytes is different. I suppose if this bundling action reduces 'redo wastage', then the total redo size will really be reduced. I did a small test with and without IMU. The session statistic 'redo size' doesn't appear to be consistent in different runs. Maybe my data change is too small. According to EagleFan's test (see http://www.itpub.net/thread-323700-3-1.html, #msg 23 and down), total redo size does not decrease. * IMU may temporarily cause LogMiner to not be able to drill redo. See ./Logmnr.txt. * IMU does not reduce undo size: Test in 10.2.0.4 (must be non-RAC): delete from test; rollback; select a.name, b.value from v$statname a, v$mystat b where a.statistic#=b.statistic# and (name like '%undo%' or name like '%rollback%') and value>0 order by 1; Run the above twice and take the second set of values. Then alter session set "_in_memory_undo" = false; --will freeze "IMU undo allocation size" and run the above again. My test shows that "rollback changes - undo records applied" increased by the same amount in the two runs. So did "undo change vector size". * More discussion: http://www.itpub.net/thread-1332575-1-1.html