oracle建立临时表备份,Oracle中临时表产生过量Redo的说明

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

临时表终于回来了!

By eygle on 2004-07-03 22:55 |

Comments (0) |

Internal | 36 |