今天同事发现一个bug，9204上INSERT INTO SELECT方式插入临时表，造成的REDO比插入普通表还多。

看一下下面的问题重现：

SQL> CONN TEST/TEST@TESTDATA已连接。
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> CREATE TABLE T_NORMAL (ID NUMBER);

表已创建。

SQL> CREATE GLOBAL TEMPORARY TABLE T_TEMP (ID NUMBER);

表已创建。

SQL> SET AUTOT ON STAT
SQL> INSERT INTO T_NORMAL SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 100000;

已创建99999行。

统计信息
----------------------------------------------------------
375 recursive calls
1457 db block gets
308 consistent gets
3 physical reads
1565800 redo size
496 bytes sent via SQL*Net to client
573 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
99999 rows processed

SQL> INSERT INTO T_TEMP SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 100000;

已创建99999行。

统计信息
----------------------------------------------------------
2 recursive calls
102288 db block gets
107 consistent gets
2 physical reads
12850376 redo size
497 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
99999 rows processed

同样的插入语句，临时表产生的REDO居然比普通表还要多，这显然有问题。

SQL> INSERT INTO T_NORMAL VALUES (1);

已创建 1 行。

统计信息
---------------------------------------------------------
0 recursive calls
1 db block gets
1 consistent gets
0 physical reads
232 redo size
498 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> INSERT INTO T_TEMP VALUES (1);

已创建 1 行。

统计信息
---------------------------------------------------------
0 recursive calls
1 db block gets
1 consistent gets
0 physical reads
128 redo size
498 bytes sent via SQL*Net to client
529 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

问题似乎只发生在INSERT INTO SELECT的方式下，普通插入的时候，临时表产生的REDO是要小于普通表的。

而且即使是INSERT INTO SELECT在10203上也没有问题：

SQL> CONN TEST/TEST@TESTRAC已连接。
SQL> 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

SQL> CREATE TABLE T_NORMAL (ID NUMBER);

表已创建。

SQL> CREATE GLOBAL TEMPORARY TABLE T_TEMP (ID NUMBER);

表已创建。

SQL> SET AUTOT ON STAT
SQL> INSERT INTO T_NORMAL SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 100000;

已创建99999行。

统计信息
----------------------------------------------------------
770 recursive calls
2476 db block gets
585 consistent gets
0 physical reads
1612324 redo size
369 bytes sent via SQL*Net to client
357 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
99999 rows processed

SQL> INSERT INTO T_TEMP SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 100000;

已创建99999行。

统计信息
----------------------------------------------------------
64 recursive calls
1199 db block gets
203 consistent gets
0 physical reads
278292 redo size
388 bytes sent via SQL*Net to client
355 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
99999 rows processed

基本可以断定，这个问题是bug引起的，查询metalink，Oracle在Note:2874489.8文档中进行了详细的描述：Bug 2874489 Excessive REDO generated for INSERT as SELECT into GLOBAL TEMPORARY TABLES

确认影响版本是9204，Oracle在9.2.0.5和10.1.0.2中解决了这个bug。

博主，好！我最近在部署一个stream replication的应用。应该说基本上没问题了，但是有一些细节我想请教你！我的MSN:bartholomu.zhengwx@hotmail.com。谢谢

我对stream不是很熟，建议你找xzh2000