July 3, 2004
Oracle中临时表产生过量Redo的说明
作者:eygle
出处:http://blog.eygle.com
最近,在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
临时表终于回来了!
Posted by eygle at 10:55 PM | Comments (0)
安装10g以后Oracle9i的DBCA无法启动问题之解决
作者:eygle
出处:http://blog.eygle.com
问题描述:
你可以也遇到这样的问题,在Windows上安转了Oracle10g以后，同一主机上的Oracle9i的DBCA可能会无法启动.
类似错误可能如下:
|
E:\Oracle\ora92\bin>dbca Exception in thread "main" java.lang.UnsatisfiedLinkError: initializeCluster at oracle.ops.mgmt.nativesystem.WindowsSystem.initializeCluster(WindowsSystem.java:390) at oracle.ops.mgmt.cluster.GetActiveNodes.initializeCluster(GetActiveNodes.java:260) at oracle.ops.mgmt.cluster.GetActiveNodes$ClusterWareThread.<init>(Compiled Code) at oracle.ops.mgmt.cluster.GetActiveNodes.<init>(GetActiveNodes.java:139) at oracle.ops.mgmt.cluster.GetActiveNodes.create(GetActiveNodes.java:158) at at oracle.sysman.assistants.dbca.backend.Host.checkOPS(Host.java:1815) at oracle.sysman.assistants.dbca.backend.Host.<init>(Host.java:469) at oracle.sysman.assistants.dbca.ui.UIHost.<init>(UIHost.java:185) at oracle.sysman.assistants.dbca.ui.InteractiveHost.<init>(InteractiveHost.java:54) at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:141) at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:85) at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:161) ^C终止批处理操作吗(Y/N)? y |
解决方法:
在 控制面板->系统->高级->环境变量->系统变量 里
找到PATH,暂时把Oracle10g的环境变量删除.
如,修改前:
|
e:\oracle\ora92\jre\1.4.2\bin\client;e:\oracle\ora92\jre\1.4.2\bin;e:\oracle\10.1.0\bin;
|
可以修改为:
|
e:\oracle\ora92\jre\1.4.2\bin\client;e:\oracle\ora92\jre\1.4.2\bin; e:\oracle\ora92\bin;e:\oracle\Report6i\bin;e:\oracle\Form6i\bin; C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin; C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;
|
此时再次运行DBCA即可启动Oracle9i的DBCA.
此后，你可以手工把10g的环境变量添加回去.
Posted by eygle at 9:14 PM | Comments (4)