Oracle中append与Nologging /*+ append */

1.Nologging的设置跟数据库的运行模式有关

a.数据库运行在非归档模式下:

SQL> archive log list; Database log mode No Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/hsjf/archive Oldest online log sequence 155 Current log sequence 157 SQL> @redo SQL> create table test as select * from dba_objects where 1=0; Table created. SQL> select * from redo_size; VALUE ---------- 63392 SQL> SQL> insert into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 1150988 SQL> SQL> insert /*+ append */ into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 1152368 SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual; REDO_APPEND REDO ----------- ---------- SQL> drop table test; Table dropped.

我们看到在Noarchivelog模式下,对于常规表的insert append只产生少量redo

b.在归档模式下

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 235999908 bytes Fixed Size 451236 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> @redo SQL> create table test as select * from dba_objects where 1=0; Table created. SQL> select * from redo_size; VALUE ---------- 56288 SQL> SQL> insert into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 1143948 SQL> SQL> insert /*+ append */ into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 2227712 SQL> select (2227712 -1143948) redo_append,(1143948 -56288) redo from dual; REDO_APPEND REDO ----------- ---------- SQL> drop table test; Table dropped.

我们看到在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的

通过Logmnr分析日志得到以下结果:

SQL> select operation,count(*) 2 from v$logmnr_contents 3 group by operation; OPERATION COUNT(*) -------------------------------- ---------- COMMIT 17 DIRECT INSERT 10470 INTERNAL 49 START 17 1

我们注意到这里是DIRECT INSERT,而且是10470条记录,也就是每条记录都记录了redo.

2.对于Nologging的table的处理

a. 在归档模式下:

SQL> create table test nologging as select * from dba_objects where 1=0; Table created. SQL> select * from redo_size; VALUE ---------- 2270284 SQL> SQL> insert into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 3357644 SQL> SQL> insert /*+ append */ into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 3359024 SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual; REDO_APPEND REDO ----------- ---------- 1380 1087360 SQL> drop table test; Table dropped.

我们注意到,只有append才能减少redo

b.在非归档模式下:

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 235999908 bytes Fixed Size 451236 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> @redo SQL> create table test nologging as select * from dba_objects where 1=0; Table created. SQL> select * from redo_size; VALUE ---------- 56580 SQL> SQL> insert into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 1144148 SQL> SQL> insert /*+ append */ into test select * from dba_objects; 10470 rows created. SQL> select * from redo_size; VALUE ---------- 1145528 SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual; REDO_APPEND REDO ----------- ---------- 1380 1087568 SQL>

 

source:http://hi.baidu.com/taojia/blog/item/74bc6fd9f2caf22611df9bf5.html

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

转载于:http://blog.itpub.net/7583803/viewspace-716276/