eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 安装10g以后Oracle9i的DBCA无法启动问题之解决 | Blog首页 | 使用or展开进行sql调整 »

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

 

临时表终于回来了!

 


历史上的今天...
    >> 2017-07-03文章:
    >> 2011-07-03文章:
    >> 2008-07-03文章:
           ORA-00704 与 bootstrap 错误
    >> 2007-07-03文章:
    >> 2006-07-03文章:
           招聘Oracle DBA一名
    >> 2005-07-03文章:
           王小慧作品

无觅

By eygle on 2004-07-03 22:55 | Comments (0) | Internal | 36 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com