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