ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Nologging到底何时才能生效考试大全(zt)

Nologging到底何时才能生效考试大全(zt)

原创 Linux操作系统 作者:tolywang 时间:2007-11-21 00:00:00 0 删除 编辑
最初的问题是这个帖子:

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 /opthttp://www.examda.com/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. [Page]

我们看到在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.[Page]

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的生成.
这就是通常大家认识的情况.

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

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13127060