ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 临时表产生REDO过多的bug

临时表产生REDO过多的bug

原创 Linux操作系统 作者:yangtingkun 时间:2008-01-09 17:00:54 0 删除 编辑

今天同事发现一个bug9204INSERT 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 SELECT10203上也没有问题:

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引起的,查询metalinkOracleNote:2874489.8文档中进行了详细的描述:Bug 2874489  Excessive REDO generated for INSERT as SELECT into GLOBAL TEMPORARY TABLES

确认影响版本是9204Oracle9.2.0.510.1.0.2中解决了这个bug

 

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

上一篇: ORA-21561错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10388156