oracle appen,Oracle中关于insert中使用/*+append*/和nologging的总结

关于Nologging和append,一直存在很多误解，经过一系列研究,终于发现了Nologging的真相，

我们来看一下：

1.对于logging 的table处理

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> insert

into test select * from dba_objects;

10470 rows

created.

SQL> select *

from redo_size;

VALUE

----------

1150988

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

-----------

----------

1380

1087596

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> insert

into test select * from dba_objects;

10470 rows

created.

SQL> select *

from redo_size;

VALUE

----------

1143948

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

-----------

----------

1083764

1087660

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> 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> insert

into test select * from dba_objects;

10470 rows

created.

SQL> select *

from redo_size;

VALUE

----------

1144148

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

同样只有append才能减少redo的生成.

b. 归档模式下

SQL> create

table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select *

from redo_size;

VALUE

----------

2270284

SQL> insert

into test select * from dba_objects;

10470 rows

created.

SQL> select *

from redo_size;

VALUE

----------

3357644

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

补充：

如果数据库开启了强记日志模式，在表上修改nologging是没有用的。

开启强记日志模式

SQL> alter database force logging;

SQL> select force_logging from v$database;

FOR

---

YES

关闭强记日志模式

SQL> alter database no force logging;

SQL> select force_logging from v$database;

FOR

---

NO

查看普通表的logging状态

SQL>select table_name,logging from dba_tables;

查看分区表的logging状态

SQL>select table_name,logging from dba_tab_partitions;

总结

非归档

归档

Logging

产生大量日志

产生大量日志

Nologging

产生大量日志

产生大量日志

Append

产生少量日志

产生大量日志

Append+Nologing

产生少量日志

产生少量日志