签到成功

知道了

CNDBA社区CNDBA社区

Oracle DML NOLOGGING

2016-11-25 16:02 1547 0 原创 Oracle 性能优化
作者： dave
本文链接：http://www.cndba.cn/Dave/article/1582

一． NOLOGGING 说明

在对大表插入数据的时候，经常会用到nologging 选项。Nologging 并不是不产生redo，nologging  +  direct 只是不会对数据产生 redo（但依然有其他的redo）。

同理 logging +  direct 下 undo 也是大大地减少， 减少的是 数据的undo ，这里强调的是 数据本身的undo ，就如同 redo的减少也一样，是 数据本身的 redo ，这和数据库是否产生  redo 和 undo 是不同的概念，比如空间分配的 redo and undo ,这就不是数据本身的变化。

 

 

在非归档模式下, 对于nologging 和 logging模式，只有使用 append，才不会对数据生成redo。 

在归档模式下,只有将表置于nologging 模式，并且使用append 才不会对数据生成redo. 

 



二． 归档模式下的示例

两个查询用的脚本
--new.sql
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

--diff.sql
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

 

数据库运行在归档模式
SQL> archive log list

数据库日志模式            存档模式

自动存档             启用

存档终点            d:/archivelog

最早的联机日志序列     125

下一个存档日志序列   127

当前日志序列           127


2.1  Create TABLE

SQL> @?/new.sql

 OLD_VALUE

----------

   8535492
SQL> create table T_NOLOG nologging as select * from all_objects;

表已创建。
SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -    8535492) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

     83496

注：REDO SIZE=83496


SQL> @?/new.sql

 OLD_VALUE

----------

   8618988

SQL> create table T_LOG logging as select * from all_objects;http://www.cndba.cn/Dave/article/1582

表已创建。
SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -    8618988) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

   8491836

注：REDO SIZE=8491836

总结：通过上面的2个例子，可以看出用nologging 创建表，不会对数据生成redo，仅对数据字典生成redo. 

      create table with nologging... not generate redo, just generate for data dictionary 


2.2  DELETE

SQL> @?/new.sql

 OLD_VALUE

----------

  17110824
SQL> DELETE FROM T_NOLOG;

已删除71711行。
SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   17110824) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

  26592364

注：REDO SIZE=26592364


SQL> @?/new.sql

 OLD_VALUE

----------

  43703188

SQL> DELETE FROM T_LOG;

 

已删除71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   43703188) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

  26592560


注：REDO SIZE=26592560


2.3 INSERT

SQL> @?/new.sql

 OLD_VALUE

----------

  70295748

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   70295748) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

   8169900


注：REDO SIZE=8169900


SQL> @?/new.sql

 OLD_VALUE

----------

  78465648

SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   78465648) OLD_VALUE from v$mystat, v$statname wherehttp://www.cndba.cn/Dave/article/1582

 OLD_VALUE

----------

   8169796


注：REDO SIZE=8169796


2.4  UPDATE

SQL> @?/new.sql

 OLD_VALUE

----------

  86635444

SQL> UPDATE T_NOLOG  SET OBJECT_ID=1;

已更新71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   86635444) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

  24323896


注：REDO SIZE=24323896


SQL> @?/new.sql

 OLD_VALUE

----------

 110959340

SQL> UPDATE T_LOG SET OBJECT_ID=1;

已更新71712行。http://www.cndba.cn/Dave/article/1582

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  110959340) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

  36490988


注：REDO SIZE=20911424

总结： insert/update/delete 的DML 操作，在logging和nologging上没有区别
   On DML insert/update/delete redo size with nologging not difference... with logging. 


2.5  Show case "APPEND" hints

2.5.1  table NOLOGGING and not use APPEND hints 

SQL> @?/new.sql

 OLD_VALUE

----------

 147450328

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  147450328) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

   8325816

注： REDO SIZE=8325816

SQL> @?/new.sql

 OLD_VALUE

----------

 155776144

SQL>  INSERT /*+ APPEND */ INTO T_NOLOG  SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  155776144) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

     14852


注：REDO SIZE=14852，采用append后，redo size大幅减小



2.5.2  table LOGGING, and use APPEND hints 

SQL> @?/new.sql

 OLD_VALUE

----------

 155790996

SQL>  INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  155790996) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

   8406068


注：REDO SIZE=8640396， redo size 没什么变化

将表改为nologging 模式，在查看 


SQL> @?/new.sql

http://www.cndba.cn/Dave/article/1582

 OLD_VALUE

----------

 164200200

SQL>  INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  164200200) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

     15012

注：REDO SIZE=27956

总结： 通过实验看出，

对于logging 模式， 使用append hint 在生成redo 上没有什么变化

对于nologging模式，使用append hint 对数据没有生成redo，仅对数据字典生成了redo.
APPEND hints on table "logging" not difference (generate redo). 
If  "alter table nologging"  before, and then insert (append)... it's work with nologging (not generate redo, just redo for data dictionary). 


三． 非归档模式下的示例


SQL> archive log list

数据库日志模式             非存档模式

自动存档             禁用

存档终点            d:/archivelog

最早的联机日志序列     129

当前日志序列           131

3.1  Create TABLE 

SQL> @?/new.sql

 OLD_VALUE

----------

    113788

SQL> create table T_NOLOG nologging as select * from all_objects;

表已创建。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -     113788) OLD_VALUE from v$mystat, v$statname wherehttp://www.cndba.cn/Dave/article/1582

 OLD_VALUE

----------

     87244

注：redo size=87244


SQL> @?/new.sql

 OLD_VALUE

----------

    201032

SQL> create table T_LOG logging as select * from all_objects;

表已创建。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -     201032) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

     81324

注： redo size=81324

 

总结： 在非归档模式下，create table 在nologging 和 logging 模式差别不大。
 When create table (noarchive mode) LOGGING table not different NOLOGGING table 

3.2  DML -- DELETE 

SQL> @?/new.sql

 OLD_VALUE

----------

    282356http://www.cndba.cn/Dave/article/1582

SQL> DELETE FROM T_NOLOG;

已删除71711行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -     282356) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------http://www.cndba.cn/Dave/article/1582

  26591628

 

注： redo size=26591628


SQL> @?/new.sql

 OLD_VALUE

----------

  26873984

SQL> DELETE FROM T_LOG;

已删除71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   26873984) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

  26590272

注： redo size= 26590272

 

 

3.3  DML --  INSERT
SQL> @?/new.sql

 OLD_VALUE

----------http://www.cndba.cn/Dave/article/1582

  53464256

SQL>  INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   53464256) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

   8170184
注：redo size = 8170184


SQL> @?/new.sql

 OLD_VALUE

----------

  61634440

SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

http://www.cndba.cn/Dave/article/1582

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   61634440) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

   8169840
注： redo size= 8169840

 

 

3.4 DML -- UPDATE


SQL> @?/new.sql

 OLD_VALUE

----------

  69804280

SQL> UPDATE T_NOLOG SET OBJECT_ID=1;

已更新71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   69804280) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

  20164888

注： redo size = 20164888

SQL> @?/new.sql

 OLD_VALUE

----------

  89969168

SQL> UPDATE T_LOG SET OBJECT_ID=1;

已更新71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -   89969168) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

  21674776

 

注： redo size=21674776

总结： 对于 INSERT/UPDATE/DELETE的DML 操作， nologging 和 logging 模式没有什么区别

  On DML INSERT/UPDATE/DELETE not different between NOLOGGING and LOGGING 

3.5  INSERT /*+ APPEND */ 


SQL> @?/new.sql

 OLD_VALUE

----------

 111643944

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  111643944) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

   8325816

 

注： redo size=8325816


SQL> @?/new.sql

 

 OLD_VALUE

----------

 119969760

SQL> INSERT /*+ APPEND */ INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  119969760) OLD_VALUE from v$mystat, v$statname where

 OLD_VALUE

----------

     14896

 

注：redo size=14896， 减小很多


SQL> @?/new.sql

 OLD_VALUE

----------

 119984656

SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  119984656) OLD_VALUE from v$mystat, v$statname where

 

 OLD_VALUE

----------

   8325832

注： redo size= 8325832


SQL> @?/new.sql

 OLD_VALUE

----------

 128310488

SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

已创建71712行。

SQL> @?/diff

原值    1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where

新值    1: select (value -  128310488) OLD_VALUE from v$mystat, v$statname where

 

 OLD_VALUE

----------

     14880

注：redo size=14880

 

总结： 对于非归档模式，对于nologging 和 logging模式，只有使用 append，才不会对数据生成redo。 

   对于归档模式，只有将表置于nologging 模式，并且使用append 才不会对数据生成redo. 

一点注意的地方：

                如果直接加载的表上有索引，Oracle不会像加载数据的方式那样来处理索引的数据，但是它同样需要维护一个索引，这个成本很高，同时会生成很多的redo。

                所以当使用直接加载时，通常是针对一些数据量非常大的表。如果这些表存在索引，将会带来很大的性能影响，这时可以考虑先将索引disable或者drop掉，等加载数据后，之后在重新建立索引。

 


On NoArchive Mode, Don't mind tables be nologging/logging...  just use /*+ APPEND */ ,that will not generate redo (just data dictionary) 

 

 

 

 

On Archive Mode, TABLEs must be nologging... and use  /*+ APPEND */, that will not generate redo (just data dictionary) 

 

NOLOGGING: Oracle will generate a minimal number of redo log entries in order to protect the data dictionary, and the operation will probably run faster. Logging can be disabled at the table level or the tablespace level.

If it is done at the tablespace level then we create indexes or tables in this tablespace; they will be in NOLOGGING mode.
A table or an index can be created with NOLOGGING mode or it can be altered using ALTER TABLE/INDEX NOLOGGING.

NOLOGGING is active in the following situations and while running one of the following commands but not after that.

- DIRECT LOAD (SQL*Loader)
- DIRECT LOAD INSERT (using APPEND hint)
- CREATE TABLE ... AS SELECT
- CREATE INDEX
- ALTER TABLE MOVE
- ALTER TABLE ... MOVE PARTITION
- ALTER TABLE ... SPLIT PARTITION
- ALTER TABLE ... ADD PARTITION (if HASH partition)
- ALTER TABLE ... MERGE PARTITION
- ALTER TABLE ... MODIFY PARTITION, ADD SUBPARTITON, COALESCE SUBPARTITON, REBUILD UNUSABLE INDEXES
- ALTER INDEX ... SPLIT PARTITION
- ALTER INDEX ... REBUILD
- ALTER INDEX ... REBUILD PARTITION

Logging is stopped only while one of the commands above is running.

So if a user runs this:  ALTER INDEX new_index NOLOGGING.

The actual rebuild of the index does not generate redo (all data dictionary changes associated with the rebuild will do) but after that any DML on the index will generate redo this includes direct load insert on the table which the index belongs to.

All the following statements will generate redo despite the fact the table is in NOLOGGING mode:
- INSERT INTO new_table_nolog_test ...,
- UPDATE new_table_nolog_test SET ...,
- DELETE FROM new_table_nolog_test ..

The following will not generate redo (except from dictionary changes and indexes):
- INSERT /*+APPEND+/ ...
- ALTER TABLE new_table_nolog_test MOVE ...
- ALTER TABLE new_table_nolog_test MOVE PARTITION ...

 

 

 

 

 

 

 

http://www.cndba.cn/Dave/article/1582

 

 

 

 

整理自网络

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

Blog： http://blog.csdn.net/tianlesoftware 

网上资源： http://tianlesoftware.download.csdn.net 

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx 

DBA1 群：62697716(满); DBA2 群：62697977

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2237
    原创
  • 3
    翻译
  • 544
    转载
  • 185
    评论
  • 访问：6562719次
  • 积分：4240
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ