临时表产生REDO过多的bug

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

看一下下面的问题重现：

SQL> CONN TEST/TEST@TESTDATA
+|6t$UP
Q2VU\0已连接。

?g [6Ji^J1cA2\0SQL> SELECT * FROM V$VERSION;

BANNERITPUB个人空间!q/@q?y-m6Z;^,M2VU
----------------------------------------------------------------
dSu"V/x$} V&n0Oracle9iEnterpriseEdition Release 9.2.0.4.0 -Production
*IG4a!~@;RuP0PL/SQL Release 9.2.0.4.0 - Production

YM0nD;J2Q.x0CORE    9.2.0.3.0       Production
U}ng(zJHz0TNS for Linux: Version 9.2.0.4.0 - ProductionITPUB个人空间rc,j+B!d!K-d
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 STATITPUB个人空间$oBK I [zu
SQL> INSERT INTO T_NORMAL SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 100000;

已创建99999行。

ITPUB个人空间#Q|YdjRe
统计信息ITPUB个人空间~9dM"yYJu|1If(`3] T
----------------------------------------------------------ITPUB个人空间.l!ZYX:d~(}
        375  recursive calls
d1E!J,y
s)gVe_H0       1457  db block gets
%V4Hg
\Jckr0        308  consistent gets
B ?2rzJ!Ko4N"M0          3  physical reads
a'{{o d+VP0    1565800  redo sizeITPUB个人空间RYRTUn
        496  bytes sent via SQL*Net to client
d!d2@} {HS&b3P0        573  bytes received via SQL*Net from client
A-ZGg"z&y^8`Bx,T0          3  SQL*Net roundtrips to/from client
]#a!sy~3{.E!X0          7  sorts (memory)ITPUB个人空间uQ*N0};v9{!U8Y.G
          0  sorts (disk)ITPUB个人空间(I(ae^+f3y0YoZ"}e
      99999  rows processed

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

已创建99999行。

ITPUB个人空间-i]t-j9NS|y
统计信息ITPUB个人空间.v0[{"`B"D0wg-c
----------------------------------------------------------
g9Y5ujl{6B%M0          2  recursive calls
|#xX(p]cf\&k0     102288  db block gets
~C$u1y,jpm0        107  consistent getsITPUB个人空间|EF*r(K+l B)s
          2  physical reads
%CJ#P8i\ZY;Z5Q,E0O0   12850376  redo size
3bm*H]$FF{!v;KG/N0        497  bytes sent via SQL*Net to client
O)W[kv0        571  bytes received via SQL*Net from clientITPUB个人空间6NJ*?w"j}
          3  SQL*Net roundtrips to/from clientITPUB个人空间+xSC/K$sgaz+[
          7  sorts (memory)ITPUB个人空间R/I7s;HeJ?SV
          0  sorts (disk)ITPUB个人空间DN/_ f"|9`r
      99999  rows processed

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

SQL> INSERT INTO T_NORMAL VALUES (1);

已创建1行。

!zm`s8rXb0统计信息ITPUB个人空间AV3Ht/kdZ
---------------------------------------------------------ITPUB个人空间`.jx^h
^T
          0  recursive calls
EDU:K Z5Z
L0          1  db block getsITPUB个人空间X2? Z4rL
          1  consistent getsITPUB个人空间ntk:[E]Qt
          0  physical readsITPUB个人空间dY p W`#Y1s l
        232  redo size
7G-gi!E|j0        498  bytes sent via SQL*Net to clientITPUB个人空间x'E~8\3\,k9~
        531  bytes received via SQL*Net from clientITPUB个人空间,t;q*a)}0a5G
          3  SQL*Net roundtrips to/from client
T2k-c Jp~?] N0          1  sorts (memory)
2]{%fkR]4\T0          0  sorts (disk)
;uk
C*RuXA
P(S/x'k#P0          1  rows processed

SQL> INSERT INTO T_TEMP VALUES (1);

已创建1行。

?_V.s1Tx0统计信息ITPUB个人空间rx3V^ V/j;h M
---------------------------------------------------------ITPUB个人空间d4m M i%ju
          0  recursive callsITPUB个人空间^,|:yZe
          1  db block gets
R}%R3i3t3w0          1  consistent getsITPUB个人空间e
s0oKz
          0  physical reads
$`(`j
o3i!^9[B-h;m0        128  redo size
#{$\8x K&cP0        498  bytes sent via SQL*Net to client
lVt4]v3`$v0        529  bytes received via SQL*Net from client
K)RR6tM,m0J0          3  SQL*Net roundtrips to/from clientITPUB个人空间-q$A@)y |6ym5pX
          1  sorts (memory)
r.^6['ZdIE*P0          0  sorts (disk)
&{9n9X*wgfk0          1  rows processed

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

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

SQL> CONN TEST/TEST@TESTRAC
*Ul'R1Jovm0|e2}0已连接。
7r3Jsai r0{pM0SQL> SELECT * FROM V$VERSION;

BANNER
:l$OQG e(~#GBXYo0----------------------------------------------------------------ITPUB个人空间Z'p'wroj#o
Oracle Database10gEnterpriseEdition Release 10.2.0.3.0 - 64biITPUB个人空间-O%h;VnZx
PL/SQL Release 10.2.0.3.0 - Production
,k+g.bq)pbUh W#E0CORE    10.2.0.3.0      Production
1M_'CWO\`5j1NR0TNS for Solaris: Version 10.2.0.3.0 - ProductionITPUB个人空间\fEBW M0S}
j+m
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
$YE c)R1cC'q0SQL> INSERT INTO T_NORMAL SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 100000;

已创建99999行。

3u/?#OIY%m2w0统计信息ITPUB个人空间jB*Y+E4U%QUN
----------------------------------------------------------ITPUB个人空间z'G&r7CImq2c7s-[1A
        770  recursive calls
S-f X*E8a
I0       2476  db block gets
}J:O^-A}0        585  consistent gets
v-D7|1uz-W_5e
T0          0  physical reads
4B9j6[{.r0    1612324  redo sizeITPUB个人空间M'_%sy|5i1H
        369  bytes sent via SQL*Net to clientITPUB个人空间 GG,Py'g'w/~$~
        357  bytes received via SQL*Net from client
sI{1pzU CJ0          3  SQL*Net roundtrips to/from client
$w*~x2Ii
FI,N1Z%O0          3  sorts (memory)ITPUB个人空间!I+jx0Ly(k__w[
          0  sorts (disk)
{%TMf/m0      99999  rows processed

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

已创建99999行。

ITPUB个人空间&yX9otn\G7a
统计信息ITPUB个人空间m!tx2`#uQ
----------------------------------------------------------
b(?.r[B!R0         64  recursive callsITPUB个人空间^
r3d-E.Q^
       1199  db block getsITPUB个人空间 DZKV$P-|V`?
        203  consistent gets
o9|"v3ytoSa#P7r0          0  physical readsITPUB个人空间O9J,D'E,^1b
     278292  redo size
3GU:Ei\&x4[0        388  bytes sent via SQL*Net to client
{2|(L"dL`!k0        355  bytes received via SQL*Net from clientITPUB个人空间tJ-UGI-}`(X+W/}{ ^
          3  SQL*Net roundtrips to/from client
;\ ~o;E{8P1^1G0          3  sorts (memory)ITPUB个人空间'N1W&OY/Ytg"k$d
          0  sorts (disk)
1KYfK#I w#?0      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。