临时表产生REDO过多的bug

今天同事发现一个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 行。

统计信息

www.ixdba.net

---------------------------------------------------------
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