_disable_logging对于性能的影响
2024-07-21 02:32:49
供稿：网友
设置了_disable_logging参数,可以禁用日志的生成,从而提高某些测试的性能.以下测试,纯属测试目的,其他内容请参考本站其他文章:http://www.eygle.com/archives/2006/04/more_about_disable_logging.Html禁用日志情况下:SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 286755168 bytes
Fixed Size 731488 bytes
Variable Size 167772160 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> connect eygle/eygle
Connected.
SQL> show parameter disableNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE测试创建100万数据表:SQL> create table test as select * from dba_objects where 1=0;Table created.SQL> set timing on
SQL> begin
2 for i in 1 .. 10000 loop
3 insert into test select * from dba_objects where rownum < 101;
4 commit;
5 end loop;
6 end;
7 /PL/SQL PRocedure sUCcessfully completed.Elapsed: 00:00:40.46
SQL> truncate table test;Table truncated.Elapsed: 00:00:52.72大约时间用了40秒.再看正常日志生成下: SQL> connect / as sysdba
Connected.
SQL> alter system set "_disable_logging"=false;System altered.Elapsed: 00:00:00.05
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 286755168 bytes
Fixed Size 731488 bytes
Variable Size 167772160 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> show parameter disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
SQL> connect eygle/eygle
Connected.
SQL> set timing on
SQL>
SQL> begin
2 for i in 1 .. 10000 loop
3 insert into test select * from dba_objects where rownum < 101;
4 commit;
5 end loop;
6 end;
7 / PL/SQL procedure successfully completed.Elapsed: 00:01:54.04
SQL>
SQL> truncate table test;Table truncated.Elapsed: 00:01:01.56此时大约用时1分54秒. 两者差距为: 114 / 40 = 2.85 倍.我们可以看出两者的差距是显著的.不作过多测试了,就此打住.大家有爱好的可以自己测试一下. 以上测试的环境为:SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
原文地址：http://www.eygle.com/archives/2006/04/disable_logging_performance.html
