临时表产生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 行。

统计信息
---------------------------------------------------------
          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。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-128064/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-128064/