_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
