Nologging到底何时才能生效?

« 使用or展开进行sql调整 | Blog首页 | 如何查询redo logfile的使用率 »

Nologging到底何时才能生效?

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2004/07/nologging_append.html

站内相关文章|Related Articles

Streams散记之一-如何清除流配置

Archive Log Current及Archive Log路线图

如何更改监听器日志文件名称

获得Redo Block Size的非典型方法

隐含参数_disable_logging的几点说明

最初的问题是这个帖子:

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-

历史上的今天...
>> 2007-07-16文章:

Tools:Linux下的SYSSTAT工具

>> 2006-07-13文章:

ITPUB电子杂志第十期-诊断案例专刊出版

>> 2005-07-13文章:

瑞典游记-正章-初到瑞典

瑞典游记-正章-公司印象

------
这篇【Nologging到底何时才能生效?】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key

By eygle on 2004-07-13 10:22 | Comments (5) | Posted to Internal | Edit |Pageviews:

相关文章	随机文章
Streams散记之一-如何清除流配置 Archive Log Current及Archive Log路线图如何更改监听器日志文件名称获得Redo Block Size的非典型方法隐含参数_disable_logging的几点说明	《循序渐进Oracle》第一章连载-之一 Oracle HowTo:如何使用Oracle的Decode函数进行多值判断 CPU Load Very High-超高负载之数据库回顾成都的Oracle 11g发布会幸运的以及更幸运的

搜索本站:

留言 (5)

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

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的产生啊，似乎矛盾哦。谢谢！

Posted by: jieyancai at May 15, 2007 4:29 PM

在归档模式下，对于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

Posted by: jyc at May 15, 2007 4:31 PM

REDO_SIZE这个是表还是视图?
怎么建出来的?
我这里咋没有呢?

Posted by: bookswords at November 22, 2007 1:52 PM

那是自己创建的一个View，我站内有创建脚本。

Posted by: eygle at November 23, 2007 10:05 AM

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

Posted by: eygle at November 23, 2007 10:08 AM

www.eygle.com

Nologging到底何时才能生效?

留言 (5)

发表留言: