CSDN博客

img eygle

Oracle中临时表产生过量Redo的说明

发表于2004/7/4 12:41:00  2567人阅读

最近,在Oracle9i中你用过临时表吗?

它是否给你带来了性能提高?你注意过么?

好了言归正传.

我们知道临时表在DML操作中可以减少redo的生成,从而在保存中间结果集时可以带来较大的性能提高.

可是,如果你注意到了,在Oracle9i里,临时表可能比常规表还要产生更多的redo:

[oracle@jumper oracle]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.3.0 - Production on Sat Jul 3 16:37:01 2004

Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production ----请注意版本
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> create table ccc1? (c1 number);

Table created.

SQL> set autotrace trace stat
SQL> insert into ccc1 select rownum from dba_objects;
10468 rows created.


Statistics
----------------------------------------------------------
???????? 73? recursive calls
??????? 175? db block gets
????? 25623? consistent gets
???????? 36? physical reads
???? 157336? redo size --------------------常规表产生的redo,大约154K
??????? 622? bytes sent via SQL*Net to client
??????? 547? bytes received via SQL*Net from client
????????? 3? SQL*Net roundtrips to/from client
????????? 2? sorts (memory)
????????? 0? sorts (disk)
????? 10468? rows processed


SQL> create global temporary table ccc2 (c1 number) ;

Table created.

SQL> set autotrace trace stat
SQL> insert into ccc2 select rownum from dba_objects;

10468 rows created.


Statistics
----------------------------------------------------------
??????? 135? recursive calls
????? 10897? db block gets
????? 25653? consistent gets
??????? 115? physical reads
??? 1476012? redo size ---------------------这是临时表,产生了大约1.4M的redo
??????? 622? bytes sent via SQL*Net to client
??????? 547? bytes received via SQL*Net from client
????????? 3? SQL*Net roundtrips to/from client
????????? 2? sorts (memory)
????????? 0? sorts (disk)
????? 10468? rows processed

这使得在Oracle9i的这些版本中,使用临时表的意义不大了.

?

今天down了个Oracle9205的patch,测试一下在Oracle9205中的情况:

?

E:/Oracle/ora92/bin>sqlplus eygle/eygle 
SQL*Plus: Release 9.2.0.5.0 - Production on 星期六 7月 3 17:37:22 2004 
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 
连接到: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production ---注意版本 
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production 
?
SQL> create table ccc1 (c1 number) tablespace eygle; 
表已创建。 
SQL> set autotrace trace stat 
SQL> insert into ccc1 select rownum from dba_objects; 
已创建6188行。 
Statistics 
---------------------------------------------------------- 
50 recursive calls 
157 db block gets 
3416 consistent gets 
0 physical reads 
92764 redo size --------------------------常规表大约产生90K的redo 
611 bytes sent via SQL*Net to client 
547 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
2 sorts (memory) 
0 sorts (disk) 
6188 rows processed 
SQL> drop table ccc1; 
表已丢弃。 
SQL> create global temporary table ccc2 (c1 number) ; 
表已创建。 
SQL> set autotrace trace stat 
SQL> insert into ccc2 select rownum from dba_objects; 
已创建6188行。 
Statistics 
---------------------------------------------------------- 
9 recursive calls 
87 db block gets 
3402 consistent gets 
0 physical reads 
16844 redo size -------------------------临时表这时只产生了16K的redo 
614 bytes sent via SQL*Net to client 
547 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
2 sorts (memory) 0 sorts (disk) 
6188 rows processed 
SQL>

在9205中Oracle修正了这个bug.

如果你从来没有遇到这个bug,那么你是幸运的:)

?

在buglist中,找到了以下说明:

2874489: Excessive REDO generated for INSERT as SELECT into GLOBAL TEMPORARY TABLES.

Fixed: 9205

?

临时表终于回来了!

?

2004年7月3日 星期六 17:53

If you have any question,please mail to eygle@itpub.net .

?

0 0

相关博文

我的热门文章

img
取 消
img