oracle nolgging,Nologging到底何时才能生效?

Nologging到底何时才能生效?

最初的问题是这个帖子:

http://www.itpub.net/showthread.php?threadid=239905

请大家仔细看那些测试的例子.

看了Tom的解释,始终觉得牵强.

开始以为可能是bug

经过观察和测试,终于发现了Nologging的秘密

我们知道,Nologging只在很少情况下生效

通常,DML操作总是要生成redo的

这个我们不多说.

关于Nologging和append,一直存在很多误解.

经过一系列研究,终于发现了Nologging的真相.

我们来看一下测试:

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

----------- ----------

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>

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

----------- ----------

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

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

这就是通常大家认识的情况.

-The End-

By eygle on 2004-07-13 10:22 |

Comments (7) |

Internal | 38 |

7 Comments

为什么我的测试结果有不同呢？

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Oracle Label Security option

JServer Release 9.2.0.8.0 - Production

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /orahome/oracle/oradata/sczy/archive

Oldest online log sequence 125

Next log sequence to archive 127

Current log sequence 127

SQL> conn jyc/jyc

Connected.

SQL> drop table test;

Table dropped.

Elapsed: 00:00:03.82

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

Table created.

Elapsed: 00:00:00.12

SQL> select * from sys.redo_size;

VALUE

----------

44412

Elapsed: 00:00:00.01

SQL> insert into test select * from dba_objects;

25359 rows created.

Elapsed: 00:00:01.85

SQL> select * from sys.redo_size;

VALUE

----------

2894412

Elapsed: 00:00:00.01

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

25359 rows created.

Elapsed: 00:00:01.76

SQL> select * from sys.redo_size;

VALUE

----------

5795272

Elapsed: 00:00:00.00

SQL> select (5795272-2894412) redo_append,(2894412-44412) redo from dual;

REDO_APPEND REDO

----------- ----------

2900860 2850000

Elapsed: 00:00:00.01

对于Nologging的table的处理

append并没有减少redo的产生啊，似乎矛盾哦。谢谢！

在归档模式下，对于Nologging的table的处理，我的测试结果是：append并没有减少redo，难道有什么问题？(aix5.2,oracle9.2.0.8)

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Oracle Label Security option

JServer Release 9.2.0.8.0 - Production

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /orahome/oracle/oradata/sczy/archive

Oldest online log sequence 125

Next log sequence to archive 127

Current log sequence 127

SQL> conn jyc/jyc

Connected.

SQL> drop table test;

Table dropped.

Elapsed: 00:00:03.82

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

Table created.

Elapsed: 00:00:00.12

SQL> select * from sys.redo_size;

VALUE

----------

44412

Elapsed: 00:00:00.01

SQL> insert into test select * from dba_objects;

25359 rows created.

Elapsed: 00:00:01.85

SQL> select * from sys.redo_size;

VALUE

----------

2894412

Elapsed: 00:00:00.01

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

25359 rows created.

Elapsed: 00:00:01.76

SQL> select * from sys.redo_size;

VALUE

----------

5795272

Elapsed: 00:00:00.00

SQL> select (5795272-2894412) redo_append,(2894412-44412) redo from dual;

REDO_APPEND REDO

----------- ----------

2900860 2850000

Elapsed: 00:00:00.01

REDO_SIZE这个是表还是视图?

怎么建出来的?

我这里咋没有呢?

CREATE OR REPLACE VIEW redo_size

AS

SELECT VALUE

FROM v$mystat, v$statname

WHERE v$mystat.statistic# = v$statname.statistic#

AND v$statname.NAME = 'redo size'

/

迄今为止，我只是发现你的测试是否忽略了一个条件database的nologging模式，才会产生这个测试结果呢，好像实际限制条件不止归档模式影响吧？请给我这个解释，呵呵，我只想知道我的实验结果是否正确

我测试也区别不大，归档和非归档，logging和nologging都测试过了。

咋回事？