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

365qb7927q

　　Oracle中临时表产生过量Redo的说明
　　最近,在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
　　
　　临时表终于回来了!
　　
　　
　　
　　from:http://www.eygle.com/faq/temp_table.htm