Nologging到底何时才能生效?

显示全部楼层 · 2005-2-28 12:57:00

最初的问题是这个帖子:
http://www.itpub.net/showthread.php?threadid=239905
请大家仔细看那些测试的例子.
看了Tom的解释,始终觉得牵强.
开始以为可能是bug
经过观察和测试,终于发现了Nologging的秘密

千问 · 2005-2-28 12:57:00

我们知道,Nologging只在很少情况下生效
通常,DML操作总是要生成redo的
这个我们不多说.
我们来看一下测试:
1.Nologging跟数据库的运行模式有关
a.数据库运行在非归档模式下:
[php]
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
----------- ----------
13801087596
SQL> drop table test;
Table dropped.
.
[/php]
我们看到在Noarchivelog模式下,对于常规表的insert append只产生少量redo

千问 · 2005-2-28 12:57:00

b.在归档模式下
[php]
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area235999908 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
----------- ----------
10837641087660
SQL> drop table test;
Table dropped.
[/php]
我们看到在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的
通过Logmnr分析日志得到以下结果:
[php]
SQL> select operation,count(*)
2from v$logmnr_contents
3group by operation;
OPERATION
COUNT(*)
-------------------------------- ----------
COMMIT
17
DIRECT INSERT
10470
INTERNAL
49
START
17

1
.
[/php]

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

千问 · 2005-2-28 12:57:00

2.对于Nologging的table的处理
a. 在归档模式下:
[php]
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
----------- ----------
13801087360
SQL> drop table test;
Table dropped.
[/php]
我们注意到,只有append才能减少redo

千问 · 2005-2-28 12:57:00

b.在非归档模式下:
[php]
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area235999908 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
----------- ----------
13801087568
SQL>

[/php]
同样只有append才能减少redo的生成.
这就是通常大家认识的情况.

千问 · 2005-2-28 12:57:00

biti给出过测试,说明在append模式下不会对数据产生undo
也就是说
在Noarchivelog模式下的append操作将是性能最高的.
等价于对Nologging表的append insert操作
大家可以在你的环境里做个测试,来验证一下这个结果.

千问 · 2005-2-28 12:57:00

最初由 eygle 发布
[B]
在Noarchivelog模式下的append操作将是性能最高的.
等价于对Nologging表的append insert操作
[/B]
谢谢eygle的辛勤劳动，这个应该是正解了

[B]
biti给出过测试,说明在append模式下不会对数据产生undo
[/B]
这个帖子，以前我看过的。我认为append insert下只是undo要比insert下的少，但并不是完全没有undo 产生的。
[php]
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> create table test as select * from all_objects where 1=2;
SQL> select usn,writes from v$rollstat;
USN WRITES
---------- ----------
0 6900
1 101874
223752
350500
451000
549180
635260
723692
823514
922698
1034620
已选择11行。
SQL> insert /*+ append */into test select * from all_objects;
已创建28950行。
SQL> select XIDUSN from v$transaction;
XIDUSN
----------
5
SQL> commit;
提交完成。
SQL> select usn,writes from v$rollstat;
USN WRITES
---------- ----------
0 6900
1 104666
225790
353322
453038
577560
637590
726236
825446
925302
1036552

SQL> select 77560-49180 from dual;
77560-49180
-----------
28380

SQL> insert into test select * from all_objects;
已创建28950行。
SQL> select XIDUSN from v$transaction;
XIDUSN
----------
8
SQL> commit;
提交完成。
SQL> select usn,writes from v$rollstat;
USN WRITES
---------- ----------
0 6900
1 109738
230594
358640
457998
582520
642998
731154
8 169818
930344
1041848
已选择11行。
SQL> select 169818-25446 from dual;
169818-25446
------------
144372
'
[/php]

千问 · 2005-2-28 12:57:00

收藏了。

千问 · 2005-2-28 12:57:00

最初由糖醋兔子发布
[B]
谢谢eygle的辛勤劳动，这个应该是正解了

这个帖子，以前我看过的。我认为append insert下只是undo要比insert下的少，但并不是完全没有undo 产生的。
[php]
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> create table test as select * from all_objects where 1=2;
SQL> select usn,writes from v$rollstat;
USN WRITES
---------- ----------
0 6900
1 101874
223752
350500
451000
549180
635260
723692
823514
922698
1034620
已选择11行。
SQL> insert /*+ append */into test select * from all_objects;
已创建28950行。
SQL> select XIDUSN from v$transaction;
XIDUSN
----------
5
SQL> commit;
提交完成。
SQL> select usn,writes from v$rollstat;
USN WRITES
---------- ----------
0 6900
1 104666
225790
353322
453038
577560
637590
726236
825446
925302
1036552

SQL> select 77560-49180 from dual;
77560-49180
-----------
28380

SQL> insert into test select * from all_objects;
已创建28950行。
SQL> select XIDUSN from v$transaction;
XIDUSN
----------
8
SQL> commit;
提交完成。
SQL> select usn,writes from v$rollstat;
USN WRITES
---------- ----------
0 6900
1 109738
230594
358640
457998
582520
642998
731154
8 169818
930344
1041848
已选择11行。
SQL> select 169818-25446 from dual;
169818-25446
------------
144372
'
[/php] [/B]

即使是 nologging +direct ,redo 也不是完全不产生，logging +direct 下 undo 的减少，是大大地减少，减少的是什么？减少的是数据的undo ，我是特地说了数据本身的undo ，就如同 redo的减少也一样，我一直都说是数据本身的 redo ，这和数据库是否产生redo 和 undo 是不同的概念，比如空间分配的 redo andundo ,这就不是数据本身的变化！

不管表是否处于nologging下，direct insert 都不会对数据产生 UNDO
----------这个说法，不会对数据产生undo 和不会产生undo 是两码事，就好比 nologging+direct 不会对数据产生 redo（但依然有其他的redo）和不会产生 redo 是两码事！
这是我当时的测试数据对比
带索引表，表和索引均是logging状态,测试结果及过程如下
----------------------------常规插入-------direct插入
插入日志生成量----------------8350864--------2364484
插入回滚段生成量--------------2343894--------426838
回滚日志生成量----------------4018204--------76032
回滚本身不存在产生回滚-------------------------------------
请再仔细读一读，参考
http://www.itpub.net/showthread.php?threadid=217094

千问 · 2005-2-28 12:57:00

不是我的错
我引用的时候就说:
biti给出过测试,说明在append模式下不会对[B]数据[/B]产生undo[/COLOR]