In-Memory undo (IMU)

Yong Huang

原帖由 eagle_fan 于 2008-11-28 03:15 发表

I doubt it. When the IMU notes are aged out, it still needs to write the undo information to UNDO segments. I believe these writes have to be recorded in redo.

IMU doesn't mean *no* redo. I was thinking maybe it can *reduce* redo. But now I'm not sure. Steve Adams' message is "some redo generation will not be seen in the stats for the session responsible". I think he means the redo *appears* to be less than it really should be, but whether actually it's less is a different issue Steve didn't answer.

It's not clear to me whether Craig's article at
http://www.oracle.com/education/ ... _in_memory_undo.pdf
or the quoted patent at
http://www.freepatentsonline.com/6981004.html
answers the question. 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.

Yong Huang

[ 本帖最后由 Yong Huang 于 2008-11-28 16:45 编辑 ]

Yong Huang

原帖由 Yong Huang 于 2008-11-28 09:37 发表

In RAC, the entry "KTI-UNDO" doesn't even exist in v$sgastat.

Yong Huang

I got that v$sgastat entry from Craig's article. He says it represents how much SGA is allocated for IMU. I'm not exactly sure what "I" in "KTI" means, maybe in-memory. (Not sure because Oracle code has a function called "ktiNonImuPoolFree" in addition to "ktiImuPoolFree".) Things related to KTI are event 30047, IM lock type (see v$lock_type), and fixed tables:
X$KTIFP
X$KTIFF
X$KTIFB
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). It seems to be an interesting table if anybody wants to study. 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. Of course on RAC, IMU is always disabled regardless the parameter.

Yong Huang

eagle_fan

原帖由 Yong Huang 于 2008-11-28 23:40 发表


IMU doesn't mean *no* redo. I was thinking maybe it can *reduce* redo. But now I'm not sure. Steve Adams' message is "some redo generation will not be seen in the stats for the session responsible". I think he means the redo *appears* to be less than it really should be, but whether actually it's less is a different issue Steve didn't answer.

It's not clear to me whether Craig's article at
http://www.oracle.com/education/ ... _in_memory_undo.pdf
or the quoted patent at
http://www.freepatentsonline.com/6981004.html
answers the question. The patent says "A benefit of this approach  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.

Yong Huang

I didn't mean "No" redo. Let me explain more about my thoughts.

In traditional undo mode, redo has two major parts:

1. data block changes
2. Undo block changes.

For IMU, you don't see the second part in v$sesstat since it doesn't write to undo segments at that time. Instead it stores these undo information in shared pool. If you check v$sesstat, you will see the redo size is reduced. I did a test before, the redo size in v$sesstat of a single row update  was reduced by 17%(the percentage depends on your DML). But when the undo information in IMU nodes are flushed to undo segments, they still have to be recorded in redo. I don't think oracle will reduce the bundling write size since the undo entry in undo segments should be same as traditional undo mode. So the total redo size don't change.  

BTW: I don't see visible CPU benefits of IMU in our production system. (10.2.0.3 version, typical OLTP system).

Yong Huang

原帖由 eagle_fan 于 2008-11-28 23:31 发表

I didn't mean "No" redo. Let me explain more about my thoughts.

In traditional undo mode, redo has two major parts:

1. data block changes
2. Undo block changes.

For IMU, you don't see the second part in v$sesstat since it doesn't write to undo segments at that time. Instead it stores these undo information in shared pool. If you check v$sesstat, you will see the redo size is reduced. I did a test before, the redo size in v$sesstat of a single row update  was reduced by 17%(the percentage depends on your DML). But when the undo information in IMU nodes are flushed to undo segments, they still have to be recorded in redo. I don't think oracle will reduce the bundling write size since the undo entry in undo segments should be same as traditional undo mode. So the total redo size don't change.  

BTW: I don't see visible CPU benefits of IMU in our production system. (10.2.0.3 version, typical OLTP system).

So you're saying that when people say IMU reduces redo, that's just because they didn't wait long enough for the "undo block changes" part of the redo gets flushed. When it's flushed, the total size jumps up to the level comparable to that for non-IMU. Very good point. I wonder, though, Have you checked 'redo wastage' with and without IMU on a typical OLTP database? Would that come down with IMU?

At the end of Craig's paper, he shows a benchmark and concludes that IMU reduces CPU time but not statistically significant elapsed time. I give more credit to real-life systems than a benchmark. But it helps to examine why a crafted benchmark does not reproduce what's happening in real-life.

Yong Huang

eagle_fan

I don't see visible redo reduction either on the production system.

oracledba

这个东西起码是不应该推到production应用，感觉这个东西就是benchmark使用，cache一批flush一次，应该不会减少redo，不然undo真的可能永远都不可见了

icedmocha

I have no idea why IMU is not working on my database. Solaris 10 (SPARC64bits) + Oracle 10.2.0.3
non-rac, supplemental logging is not eabled

08:45:10 oracle[SQLPLUS]@sprrprd1[fraespappb31]> !uname -a
SunOS fraespappb31 5.10 Generic_142900-12 sun4u sparc SUNW,SPARC-Enterprise

08:45:15 oracle[SQLPLUS]@sprrprd1[fraespappb31]> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


08:45:40 oracle[SQLPLUS]@sprrprd1[fraespappb31]> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

08:46:37 oracle[SQLPLUS]@sprrprd1[fraespappb31]> select startup_time from v$instance;

STARTUP_TIME
-----------------
20101106 19:13:19


08:45:57 oracle[SQLPLUS]@sprrprd1[fraespappb31]> select NAME,value from v$sysstat where name like '%IMU%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
IMU commits                                                               0
IMU Flushes                                                               0
IMU contention                                                            0
IMU recursive-transaction flush                                           0
IMU undo retention flush                                                  0
IMU ktichg flush                                                          0
IMU bind flushes                                                          0
IMU mbu flush                                                             0
IMU pool not allocated                                                    0
IMU CR rollbacks                                                          0
IMU undo allocation size                                                  0
IMU Redo allocation size                                                  0
IMU- failed to get a private strand                                       0

13 rows selected.

08:47:44 sys[SQLPLUS]@sprrprd1[fraespappb31]> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%&par%';08:47:45   2  08:47:45   3  08:47:45   4  08:47:45   5  08:47:45   6
Enter value for par: in_memory_undo
old   6: AND x.ksppinm LIKE '%&par%'
new   6: AND x.ksppinm LIKE '%in_memory_undo%'

NAME
--------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------
_in_memory_undo
TRUE
Make in memory undo for top level transactions


08:47:50 sys[SQLPLUS]@sprrprd1[fraespappb31]> /
Enter value for par: imu_pools
old   6: AND x.ksppinm LIKE '%&par%'
new   6: AND x.ksppinm LIKE '%imu_pools%'

NAME
--------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------
_imu_pools
3
in memory undo pools


08:48:02 sys[SQLPLUS]@sprrprd1[fraespappb31]> /
Enter value for par: flush_imu
old   6: AND x.ksppinm LIKE '%&par%'
new   6: AND x.ksppinm LIKE '%flush_imu%'

NAME
--------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------
_db_writer_flush_imu
TRUE
If FALSE, DBWR will not downgrade IMU txns for AGING


08:48:07 sys[SQLPLUS]@sprrprd1[fraespappb31]> select count(*) from X$KTIFP
08:49:10   2  ;

  COUNT(*)
----------
         0


Can someone explain this? please~

thanks for your help

Yong Huang

> I have no idea why IMU is not working on my database. Solaris 10 (SPARC64bits) + Oracle 10.2.0.3
> non-rac, supplemental logging is not eabled

There's one more restriction I know of: flashback database. Check to see if you have it on:

select flashback_on from v$database;

Ref: Slide 26 of http://www.teamycc.com/RMOUG_2008_Conference/PDF/Haisley.pdf

Yong Huang

icedmocha

thanks, flashback has been turned on in my database

newdelete

基本看不懂，mark一下。