玉面飞龙的BLOG

SUPPLEMENTAL_LOG disable IMU

November 9th, 2008 | Categories: Boring | Tags: IMU, Oracle, SUPPLEMENTAL LOG

IMU是 In memory UNDO，10g得新功能,由参数_in_memory_undo控制，默认是Enable的。但IMU与SUPPLEMENTAL_LOG冲突，当因为要使用Streams, Data guard, Log Miner 等功能的时候，需要enable SUPPLEMENTAL_LOG，而关闭了IMU这个功能。

如下例子，在非常空闲的数据库下，普通DML操作并未产生IMU Commit.
NAME VALUE ---------- ----------------------------------- ---------- ---------- ---------- IMU commits 0 IMU Flushes 0 IMU undo allocation size 0

SQL> update t set owner=’X’ where rownum=1;
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from v$sysstat where name like ‘IMU%’;
NAME VALUE
———- —————————-
IMU commits 0
IMU Flushes 0
IMU undo allocation size 0

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUPPLEME SUP SUP SUP SUP
——– — — — —
YES NO NO NO NO

发现SUPPLEMENTAL_LOG_DATA_MIN被打开。
SQL> alter database drop SUPPLEMENTAL LOG DATA; Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUPPLEME SUP SUP SUP SUP
——– — — — —
NO NO NO NO NO

SQL> conn /
Connected.
SQL> update t set owner=’X’ where rownum=1;
1 row updated.

SQL> commit;
Commit complete.

SQL> col name format a45
SQL> set linesize 200
SQL> select * from v$sysstat where name like ‘IMU%’;
STATISTIC# NAME CLASS VALUE STAT_ID
———- ——————————————— ———- ———-
IMU commits 1
IMU Flushes 0
IMU undo allocation size 2384

SQL> /
STATISTIC# NAME CLASS VALUE STAT_ID
———- ————————
IMU commits 1
IMU Flushes 0
IMU undo allocation size 4328

NAME                      VALUE    DESCRIPTION
—————– —————— ————–
_imu_pools              3 in memory undo pools
_in_memory_undo    TRUE        Make in memory undo for top level transactions
关闭SUPPLEMENTAL_LOG后，启动了IMU新功能。

IMU 据说在更新和一致查询都特别频繁的系统上特合适，但具体仍然要测试case by case。

下面测试一个session更新一个表,每更新25行提交一次。

IMU
================================================
SQL> set time on timing on
05:24:05 SQL>
05:24:05 SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.48
05:24:35 SQL>
05:24:35 SQL> declare
05:24:35 2 cc number;
05:24:35 3 begin
05:24:35 4 cc:=0;
05:24:35 5 for i in 1..788928 loop
05:24:35 6 update t set object_name=object_name||’x’ where object_id=i;
05:24:35 7 cc:=cc+1;
05:24:35 8 if mod(cc,25) = 0 then
05:24:35 9 commit;
05:24:35 10 end if;
05:24:35 11 end loop;
05:24:35 12 commit;
05:24:35 13 end;
05:24:35 14 /

PL/SQL procedure successfully completed.

Elapsed: 00:05:04.40
05:29:39 SQL>
05:29:39 SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:23.87
05:30:03 SQL>
05:30:03 SQL> alter system checkpoint;

System altered.

Elapsed: 00:00:05.47
05:30:08 SQL>
05:30:08 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Elapsed: 00:00:00.04
05:30:08 SQL>
05:30:08 SQL> set time on timing on
===================================================================
IMU is Disabled
===================================================================
05:30:08 SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.60
05:30:33 SQL>
05:30:33 SQL> declare
05:30:33 2 cc number;
05:30:33 3 begin
05:30:33 4 cc:=0;
05:30:33 5 for i in 1..788928 loop
05:30:33 6 update t set object_name=object_name||’x’ where object_id=i;
05:30:33 7 cc:=cc+1;
05:30:33 8 if mod(cc,25) = 0 then
05:30:33 9 commit;
05:30:33 10 end if;
05:30:33 11 end loop;
05:30:33 12 commit;
05:30:33 13 end;
05:30:33 14 /

PL/SQL procedure successfully completed.

Elapsed: 00:05:34.85
05:36:08 SQL>
05:36:08 SQL> exec statspack.snap

PL/SQL procedure successfully completed.

从时间上看，IMU使用了5分钟，Disable IMU使用了5分半。CPU上来说，单个进程提高了10%.

从产生的redo来说，IMU比Disable IMU减少了10%~15%的样子，但不确定是否包括SUPPLEMENTAL_LOG被关闭的影响。

Sun-Fire-T200$> grep ‘redo size’ IMU.lst Disable_IMU.lst
Statistic Total per Second per Trans
——————————— —————— ————– ————
IMU.lst: redo size 220,820,048 663,123.3 6,997.1
Disable_IMU.lst:redo size 257,799,024 718,103.1 8,168.8

下面是statspack中IMU mode的相关信息。由于是单个进程测试的，没有涉及并发测试，数据意义不大。

Statistic Total per Second per Trans
——————————— —————— ————– ————
IMU CR rollbacks 0 0.0 0.0
IMU Flushes 12 0.0 0.0
IMU Redo allocation size 49,292 148.0 1.6
IMU commits 29,507 88.6 0.9
IMU contention 1 0.0 0.0
IMU pool not allocated 2,046 6.1 0.1
IMU recursive-transaction flush 1 0.0 0.0
IMU undo allocation size 1,776,432,056 5,334,630.8 56,289.2
IMU- failed to get a private stra 2,046 6.1 0.1
user commits 31,559 94.8 1.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
In memory undo latch 826,374 0.0 0.0 0 31,624 0.0

Pool Name Begin MB End MB % Diff
—— —————————— ————– ————– ——–
shared KTI-UNDO 147.8 147.8 0.00
shared free memory 4,492.6 4,487.1 -0.12