nologging与append测试

最近学到append和nologging，觉得有点晕，baidu了一番，自己做了一些测试，结果如下：

1 归档与否的影响
（1）归档：
    logging+append        --跟普通插入差不多的redo量
    nologging+append      --append比普通插入少非常多redo
（2）未归档：
    logging+append        --append比普通插入少非常多redo   
    nologging+append      --跟上面一样

 总结：非归档模式 直接append就可以
          归档模式必须nologgong+append才有效果

2 append锁定测试
  append会锁定表，阻塞所有对该表的DML操作（包括自己），自己无法查询该表（其他session可以）
  commit后恢复正常，所以不能在业务运行时使用，插入完后应尽快commit或rollback。
  （据说由于排他锁影响，设置并行也不能加快插入速度，这个还未测）
       
3 介质恢复测试
       logging+append
       logging+正常插
       nologging+appeng
       nologging+正常插
  总结：
   没commit时都可以正常回滚；

   commit后，介质恢复时，nologging+appeng插入的数据查询时报错，单纯nologging不会报错 
   所以commit后应介质备份（应该是可以只备份操作表对应的数据文件），否则之前的备份无法对该表介质恢复


测试：
1 归档与否的影响
create view redo_size as select b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';   --建查当前redo视图

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
（1）归档
   ① logging+append
SQL> select table_name,logging from user_tables where table_name='T';

TABLE_NAME                     LOG
------------------------------ ---
T                              YES

SQL> select * from redo_size;

     VALUE
----------
     35544

SQL> insert into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
   8495016

SQL> insert /*+ append */ into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
  16984164

SQL> select (16984164-8495016) redo_append,(8495016-35544) redo from dual;

REDO_APPEND       REDO
----------- ----------
    8489148    8459472     --跟普通插入差不多的redo量      

②nologgong+append
SQL> drop table t purge;

Table dropped.

SQL> create table t as select * from dba_objects where 1=0;

Table created.

SQL> alter table t nologging;

Table altered.

SQL> select table_name,logging from user_tables where table_name='T';

TABLE_NAME                     LOG
------------------------------ ---
T                              NO

SQL> select * from redo_size;

     VALUE
----------
  17041104

SQL> insert into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
  25500800

SQL> insert /*+ append */ into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
  25515560

SQL> select (25515560-25500800) redo_append,(25500800-17041104) redo from dual;

REDO_APPEND       REDO
----------- ----------
      14760    8459696   --append比普通插入少非常多redo
    
（2）未归档
drop table t purge;
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence           7

 ① logging+append 

SQL> create table t as select * from dba_objects where 1=0;

Table created.

SQL> select table_name,logging from user_tables where table_name='T';

TABLE_NAME                     LOG
------------------------------ ---
T                              YES

SQL> select * from redo_size;

     VALUE
----------
     41292

SQL> insert into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
   8501456

SQL> insert /*+ append */ into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
   8516276

SQL> select (8516276-8501456) redo_append,(8501456-41292) redo from dual;

REDO_APPEND       REDO
----------- ----------
      14820    8460164      --append比普通插入少非常多redo       
②nologgong+append
SQL> drop table t purge;

Table dropped.

SQL> create table t as select * from dba_objects where 1=0;

Table created.

SQL> alter table t nologging;

Table altered.

SQL> select table_name,logging from user_tables where table_name='T';

TABLE_NAME                     LOG
------------------------------ ---
T                              NO

SQL> select * from redo_size;

     VALUE
----------
   8572444

SQL> insert into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
  17031984

SQL> insert /*+ append */ into t select * from dba_objects;

72453 rows created.

SQL> select * from redo_size;

     VALUE
----------
  17046864

SQL> select (17046864-17031984) redo_append,(17031984-8572444) redo from dual;

REDO_APPEND       REDO
----------- ----------
      14880    8459540          --跟logging情况差不多


2 append锁定测试
session 1:
SQL> drop table t purge;

Table dropped.

SQL>  create table t as select * from dba_objects where object_id=3;

Table created.

SQL> insert /*+ append */ into t select * from dba_objects where object_id<=20;

19 rows created.

SQL> insert /*+ append */ into t select * from dba_objects where object_id<=20; 
insert /*+ append */ into t select * from dba_objects where object_id<=20
                          *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> insert into t select * from dba_objects where object_id<=20;
insert into t select * from dba_objects where object_id<=20
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> update t set object_id=5 where object_id=10;
update t set object_id=5 where object_id=10
       *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> delete from t where object_id=6;
delete from t where object_id=6
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> select * from t where rownum<=2; 
select * from t where rownum<=2
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel    
                 
另起一session 2:
  insert into t select * from dba_objects where object_id<=20;   --下面3个操作都HANG住  
  update t set object_id=5 where object_id=100;
  delete from t where object_id=100;
  
  select * from t where rownum<=2;                                         --正常返回结果   

转到session 1:
  commit;
  
  insert into t select * from dba_objects where object_id<=20;    --4个操作都正常
  update t set object_id=5 where object_id=10;
  delete from t where object_id=6;   

  select * from t where rownum<=2;                                                      

转到session 2:
  insert into t select * from dba_objects where object_id<=20;     --3个操作都正常       
  update t set object_id=5 where object_id=100;
  delete from t where object_id=100; 
    
3 介质恢复测试
改成归档模式
SQL> select file_id,tablespace_name from dba_data_files;
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
         4 USERS
         3 UNDOTBS1
         2 SYSAUX
         1 SYSTEM
         5 EXAMPLE

  rman target /
  backup datafile 4;
（1）未commit介质恢复测试
   create table t1 tablespace users as select * from dba_objects where rownum<=20;
   create table t2 tablespace users as select * from dba_objects where rownum<=20;
   create table t3 tablespace users as select * from dba_objects where rownum<=20;
   create table t4 tablespace users as select * from dba_objects where rownum<=20;

   alter table t3 nologging;
   alter table t4 nologging;

SQL> select table_name,logging from user_tables where table_name in('T1','T2','T3','T4');  
TABLE_NAME                     LOG
------------------------------ ---
T1                             YES
T2                             YES
T3                             NO
T4                             NO

   insert /*+ append */ into t1 select * from dba_objects where rownum<=50;
   insert into t2 select * from dba_objects where rownum<=50;
   insert /*+ append */ into t3 select * from dba_objects where rownum<=50;
   insert into t4 select * from dba_objects where rownum<=50;


   alter system checkpoint;   --不加这个效果也一样
   shutdown abort;

   rman target /
   startup mount;
   restore tablespace users;
   recover tablespace users;
   open database;

SQL> select count(*) from t1;       --所有都能正常回滚
  COUNT(*)
----------
        20
SQL> select count(*) from t2;
  COUNT(*)
----------
        20
SQL> select count(*) from t3;
  COUNT(*)
----------
        20
SQL> select count(*) from t4;
  COUNT(*)
----------
        20   
（2）commit后介质恢复测试
   create table t1 tablespace users as select * from dba_objects where rownum<=20;
   create table t2 tablespace users as select * from dba_objects where rownum<=20;
   create table t3 tablespace users as select * from dba_objects where rownum<=20;
   create table t4 tablespace users as select * from dba_objects where rownum<=20;

   alter table t3 nologging;
   alter table t4 nologging;

SQL> select table_name,logging from user_tables where table_name in('T1','T2','T3','T4') order by 1;
TABLE_NAME                     LOG
------------------------------ ---
T1                             YES
T2                             YES
T3                             NO
T4                             NO

   insert /*+ append */ into t1 select * from dba_objects where rownum<=50;
   insert into t2 select * from dba_objects where rownum<=50;
   insert /*+ append */ into t3 select * from dba_objects where rownum<=50;
   insert into t4 select * from dba_objects where rownum<=50;

   commit;

    alter tablespace users offline;    
    restore datafile 4;
    recover datafile 4;
    alter tablespace users online; 

SQL> select count(*) from t1;
  COUNT(*)
----------
        70
SQL> select count(*) from t2;
  COUNT(*)
----------
        70
SQL> select count(*) from t3;
select count(*) from t3
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 604)
ORA-01110: data file 4: '/u01/app/oracle2/oradata/orcl2/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL> select count(*) from t4;
  COUNT(*)
----------
        70


参考文献：http://blog.itpub.net/185801/viewspace-936/
                http://www.blogjava.net/xzclog/archive/2010/10/28/336383.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12234249/viewspace-1157997/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12234249/viewspace-1157997/