[转载]NOLOGGING&APPEND

原文地址:NOLOGGING&APPEND作者:dbafans

在工作中我们一直在为如何优化SQL努力着;为了提高数据INSERT效率,NOLOGGING与APPEND被频繁使用,但是他们什么时候真正生效,对于我来说一直很模糊;今天在群里有人提出这个问题,很多人也是一知半解;虽然网上有不少大师已经总结出了结论,所谓纸上得来终觉浅,借此机会,决定亲自实践研究一番。
1.非归档模式
1.1.表LOGGING属性为:TURE

SQL> archive log list
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     1
当前日志序列           2
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5805956                                                                     
SQL> create table crm.test as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5885568                                                                     
SQL> select 5885568-5805956 t1 from dual;
        T1                                                                      
----------                                                                      
     79612                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49748行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  11458948                                                                     
SQL> select 11458948-5885568 t2 from dual;
        T2                                                                      
----------                                                                      
   5573380                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49748行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  11478864                                                                     
SQL> select 11478864-11458948 t3 from dual;
        T3                                                                      
----------                                                                      
     19916

1.2.表LOGGING属性为:FALSE

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     1
当前日志序列           3
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     17780                                                                     
SQL> create table crm.test nologging as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     97680                                                                     
SQL> select 97680-17780 t10 from dual;
       T10                                                                      
----------                                                                      
     79900                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49751行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5670484                                                                     
SQL> select 5670484-97680 t11 from dual;
       T11                                                                      
----------                                                                      
   5572804                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49751行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5690412                                                                     
SQL> select 5690412-5670484 t12 from dual;
       T12                                                                      
----------                                                                      
     19928

2.归档模式
2.1.表LOGGING属性为:TRUE

SQL> shutdown immediate
ORA-01097: 无法在事务处理过程中关闭 - 请首先提交或回退
SQL> commit;
提交完成。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area  285212672 bytes                                      
Fixed Size                  1248576 bytes                                      
Variable Size             100664000 bytes                                      
Database Buffers          180355072 bytes                                      
Redo Buffers                2945024 bytes                                      
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     1
下一个存档日志序列   2
当前日志序列           2
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     17848                                                                     
SQL> drop table crm.test;
表已删除。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     22580                                                                     
SQL> create table crm.test as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5745552                                                                     
SQL> select 5745552-22580 t4 from dual;
        T4                                                                      
----------                                                                      
   5722972                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49749行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  11318264                                                                     
SQL> select 11318264-5745552 t5 from dual;
        T5                                                                      
----------                                                                      
   5572712                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49749行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  16964808                                                                     
SQL> select 16964808-11318264 t6 from dual;
        T6                                                                      
----------                                                                      
   5646544

 2.2.表LOGGING属性为:FALSE

SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  16974024                                                                     
SQL> create table crm.test nologging as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  17071616                                                                     
SQL> select 17071616-16974024 t7 from dual;
        T7                                                                      
----------                                                                      
     97592                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49750行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  22644348                                                                     
SQL> select 22644348-17071616 t8 from dual;
        T8                                                                      
----------                                                                      
   5572732                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49750行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  22664232                                                                     
SQL> select 22664232-22644348 from dual;
22664232-22644348                                                              
-----------------                                                              
            19884                                                                
SQL> select 22664232-22644348 t9  from dual;
        T9                                                                      
----------                                                                      
     19884

3.根据以上试验整理得到试验结果:
[转载]NOLOGGING&APPEND
4.根据试验结果得出初步结论

①CREATE TABLE在非归档模式下表的LOGING属性对产生REDO大小无太大影响;相反,在归档模式下,   LOGING在TURE与FALSE下产生REDO大小相差很大,显然FALSE下效率高;
②INSERT INTO 在是否归档下及是否LOGGING下产生REDO大小相差无几;
③在非归档及LOGGING为FALSE下,产生REDO最小,效率最高.在归档模式下,表LOGGING为TURE时,与普通INSERT INTO 相差无几;
④⑤⑦与⑥比较:在非归档和归档NOLOGGING下,三种操作中,效率最高,CREATE TABLE次之,普通INSERT最慢。而归档LOGGING下,三种操作相差无几;

5.最终结论
在非归档模式下,无论表是否LOGGING,APPEND都生效,在数据倒换及迁移时,尽量采用APPEND或者CREATE TABLE AS方式;在归档模式下,只有表LOGGING为FALSE时才生效,在数据倒换及迁移时,可以先将表设置为NOLOGGING,并采用APPEND(有风险!完成后需进行全备);

posted @ 2011-10-28 14:04  刘竹青  阅读(107)  评论(0编辑  收藏  举报