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

作者：eygle |English Version 【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2004/07/temp_table.html

站内相关文章|Related Articles

Oracle9iR2 NF:压缩表技术
Oracle9i中如何重建与切换临时表空间
Cache-low rba 与 on-disk rba - 恢复笔记
Linux / Unix 下文件删除、句柄 与空间释放问题
如何调整Oracle Redo Logfile日志文件的大小

---

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

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

---

 

好了言归正传.

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

 

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

 

[oracle@jumper oracle]$ sqlplus eygle/eygleSQL*Plus: Release 9.2.0.3.0 - Production on Sat Jul 3 16:37:01 2004Copyright (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 optionsJServer Release 9.2.0.3.0 - ProductionSQL> 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 processedSQL> create global temporary table ccc2 (c1 number) ;  Table created.SQL> set autotrace trace statSQL> 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/eygleSQL*Plus: Release 9.2.0.5.0 - Production on 星期六 7月 3 17:37:22 2004Copyright (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 optionsJServer Release 9.2.0.5.0 - ProductionSQL> create table ccc1  (c1 number) tablespace eygle;表已创建。SQL> set autotrace trace statSQL> 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 processedSQL> drop table ccc1;表已丢弃。SQL> create global temporary table ccc2 (c1 number) ;表已创建。SQL> set autotrace trace statSQL> 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 processedSQL>

 

在9205中Oracle修正了这个bug.

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

 

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

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

Fixed: 9205

 

临时表终于回来了!