In-Memory undo (IMU)

Yong Huang

最初由 wing hong 发布
[B]
If we have a cold backup,  I think we can open the database without the undo tablespace ,  but no much else we can do since we cannot  use the system rollback segment for other DML.  

(And if we have hot backup , since the files are fuzzy, we must need to do rollback anyhow)
[/B]

You probably meant "do roll forward" when you said "do rollback".

It's interesting to see your proof (not quoted here) that you *can* open a database without the undo tablespace. I should have tried that. Indeed you can't use system rollback segment for your DML. But can't you create a non-system rollback segment (or undo tablespace)? My goal is to backup a database without undo and create it after you restore, much like Oracle has improved to the point of not needing temporary tablespace in backup.

My guess about IMU being the feature that CR blocks are never written is just a guess. Probably a wrong one. You can ignore it,

Yong Huang

wing hong

>> You probably meant "do roll forward" when you said "do rollback".


No. I did mean rolllback. Sorry  I didn't make my self  clear.  what I am trying to say is :

since it is a hot backup, it is quite possible a  dirty block is already in the datafile  but that is part of  a roll back transaction. so in this case when we open a databaes, after we roll forward, then we must roll back. in this case , we must have the undo tablespace intact.




>>>  But can't you create a non-system rollback segment (or undo tablespace)? My goal is to backup a database without undo and create it after you restore, much like Oracle has improved to the point of not needing temporary tablespace in backup.

I think we can create a undo tablespace after this . since the database is already open.
( I kind of remember I did this test before, but I will double check later)
but  this is different with temp tablespace, since it only works with  COLD backup. and since most of the  times,  we have to use hot backup to do the cloning etc, we can't take the  db down.  so the use of this is not much.

玉面飞龙

谁的production enable了这个功能？

观察到redo size明显减少了吗？

Yong Huang

原帖由 玉面飞龙 于 2008-11-7 02:59 发表
谁的production enable了这个功能？

观察到redo size明显减少了吗？

You mean, when in-memory undo is enabled, redo size is reduced? I think it's by design. See

http://www.freelists.org/archives/oracle-l/01-2008/msg00000.html

_in_memory_undo is true by default. I haven't checked which other parameter, if any, indirectly sets this parameter.

Yong Huang

玉面飞龙

good link.

When enabling  supplemental_log for shareplex , log miner, streams, logical standby etc, it will disable in memory undo automatically.

Yong Huang

原帖由 玉面飞龙 于 2008-11-10 07:36 发表
good link.

When enabling  supplemental_log for shareplex , log miner, streams, logical standby etc, it will disable in memory undo automatically.

I probably misunderstand you. But this shows that in memory undo is still enabled:

SQL> select ksppstvl from x$ksppsv where indx = (select indx from x$ksppi where ksppinm = '_in_memory_undo');

KSPPSTVL
-----------------------------------------
TRUE

SQL> alter database add supplemental log data;

Database altered.

SQL> select ksppstvl from x$ksppsv where indx = (select indx from x$ksppi where ksppinm = '_in_memory_undo');

KSPPSTVL
------------------------------------------
TRUE

I'm running 10.2.0.1 on my laptop.

Yong Huang

netbanker

for stream environment which we need supp. logging, it is true as well

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 10 16:27:43 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select ksppstvl from x$ksppsv where indx = (select indx from x$ksppi where ksppinm = '_in_memory_undo');

KSPPSTVL
--------------------------------------------------------------------------------
TRUE

eagle_fan

I agree with 玉面飞龙. After add supplemental log, the parameter is still true, but actually it is disabled.

SQL> alter database add supplemental log data;

Database altered.

SQL> select n.name, s.value from v$statname n, v$sesstat s
  2  where n.statistic# = s.statistic#
  3  and s.sid = (select sid from v$mystat where rownum = 1)
  4  and n.name in ('IMU Flushes','IMU commits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
IMU commits                                                               1
IMU Flushes                                                               1

SQL> update t set owner = 'X' where rownum = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select n.name, s.value from v$statname n, v$sesstat s
  2   where n.statistic# = s.statistic#
  3   and s.sid = (select sid from v$mystat where rownum = 1)
  4   and n.name in ('IMU Flushes','IMU commits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
IMU commits                                                               1
IMU Flushes                                                               1

SQL> select ksppstvl from x$ksppsv where indx = (select indx from x$ksppi where ksppinm = '_in_memory_undo');

KSPPSTVL
-----------------------------------------------------------------------------------------------------------------------------------------------
TRUE

SQL> !ora params _in_memory_undo

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


SQL>  alter database drop supplemental log data;

Database altered.

SQL> update t set owner = 'X' where rownum = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select n.name, s.value from v$statname n, v$sesstat s
  2  where n.statistic# = s.statistic#
  3  and s.sid = (select sid from v$mystat where rownum = 1)
  4  and n.name in ('IMU Flushes','IMU commits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
IMU commits                                                               2
IMU Flushes                                                               1

[ 本帖最后由 eagle_fan 于 2008-11-28 15:07 编辑 ]

eagle_fan

原帖由 Yong Huang 于 2008-11-8 02:34 发表


You mean, when in-memory undo is enabled, redo size is reduced? I think it's by design. See

http://www.freelists.org/archives/oracle-l/01-2008/msg00000.html

_in_memory_undo is true by default. I haven't checked which other parameter, if any, indirectly sets this parameter.

Yong Huang

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.

Yong Huang

原帖由 eagle_fan 于 2008-11-28 01:06 发表
I agree with 玉面飞龙. After add supplemental log, the parameter is still true, but actually it is disabled.
...

Thanks for the correction. I checked my database, all the stats like 'IMU%' in v$sysstat and also the stats in v$latch for "In memory undo latch". Supplemental logging does in fact turn off IMU. Metalink Note:428167.1 clearly says "turning supplemental logging on disables IMU".

Another feature of the database that turns off IMU is RAC. Craig Shallahamer's paper "All About Oracle’s In-memory undo" talks about it. In RAC, the entry "KTI-UNDO" doesn't even exist in v$sgastat.

Yong Huang

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