锐客网

  1. 首页 > 睿知 > it技术 > >

Oracle中append与Nologging

IT知识nologgingOraclelogging

相逢意气为君饮，系马高楼垂柳边。这篇文章主要讲述Oracle中append与Nologging相关的知识，希望能为你提供帮助。
快速向表中插入大量数据Oracle中append与Nologging2017-05-05 / VIEWS: 304
来源于：http://blog.sina.com.cn/s/blog_61cd89f60102e7gi.html
 
当需要对一个非常大的表INSERT的时候，会消耗非常多的资源，因为update表的时候，oracle需要生成 redo log和undo log; 此时最好的解决办法是用insert, 并且将表设置为nologging; 当把表设为nologging后，并且使用的insert时，速度是最快的，这个时候oracle只会生成最低限度的必须的redo log，而没有一点undo信息。如果有可能将index也删除，重建
      1.alter table table_name nologging;
      2.  insert into table_name  select * from xxxx;  
对此有了更好的解决方法：
    1、alter table nologging;
            注释：Nologging的设置跟数据库的运行模式有关
                  a、查询当前数据库的归档状态：
                            select name,log_mode from v$database;
                            默认为 NOARCHIVELOG 非归档
                  b、nologging在归档模式下有效，非归档模式nologging不起什么作用
                  c、为了提高插入的速度，我们可以对表关闭写log功能。 SQL 如下：
                            sql> alter table table_name NOLOGGING;  
                            插入/修改,完数据后，再修改表写日志：  
                            sql> alter table table_name LOGGING;
                  d、没有写log， 速度会块很多，但是也增加了风险，如果出现问题就不能恢复。       2、drop掉索引约束之类的;
            注释：
    3、 
            注释：
                a. 、append 属于direct insert,归档模式下append+table nologging会大量减少日志,
                          非归档模式append会大量减少日志,append方式插入只会产生很少的undo
              b、综合一下吧：一是减少对空间的搜索；二是有可能减少redolog的产生。所以append方式会快很多，一般用于大数据量的处理
              c、建议不要经常使用append，这样表空间会一直在高水位上，除非你这个表只插不删
              d、oracle append有什么作用？
                      请教一下，oracle中append是做什么用的。
                      insert into table1 select * from table2
                      在使用了append选项以后，insert数据会直接加到表的最后面，而不会在表的空闲块中插入数据。
                      使用append会增加数据插入的速度。
                      的作用是在表的高水位上分配空间,不再使用表的extent中的空余空间
                    append 属于direct insert,归档模式下append+table nologging会大量减少日志,
                    非归档模式append会大量减少日志,append方式插入只会产生很少的undo
                    不去寻找 freelist 中的free block , 直接在table HWM 上面加入数据。
    4、总结
              测试证明：
                    1. 不管哪种模式下append要与nologging方式联用才能达到很好的效果。
                    2. 非归档与归档方式，只用NOLOGGING是不起效果的。
                    3. 非归档下append已达到不错的效果，但不及与nologging的联用方式。
                    4. 归档下单append起不到效果。
                    NOLOGGING插完后最好做个备份。
                    另外，如果库处在FORCELOGGING模式下，此时的nologging方式是无效的。
 
总结得出以下3点结论：
a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。
b、insert /*+ append */  时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。
c、insert /*+ append */  直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。

数据库版本：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE      11.2.0.1.0          Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
【Oracle中append与Nologging】网上说测试时不要使用auto trace来查看redo size，这个值是有偏差的.建议创建一个视图：
SQL> create or replace view redo_size
  2  as
  3  select value 
  4  from v$mystat, v$statname 
  5  where v$mystat.statistic# = v$statname.statistic# 
  6  and v$statname.name = ‘redo size‘;
视图已创建。
一、非归档模式
SQL> archive log list
数据库日志模式                          非存档模式
自动存档                          禁用
存档终点                      USE_DB_RECOVERY_FILE_DEST
最早的联机日志串行        95
当前日志串行                    97
1、nologging表
SQL> create table test1 nologging as select * from dba_objects where 1=0;
表已创建。
SQL> select * from redo_size;
        VALUE
----------
  25714940
SQL> insert into test1 select * from dba_objects;
已创建72753行。
SQL> select * from redo_size;
        VALUE
----------
  34216916
SQL> insert /*+ APPEND */  into test1 select * from dba_objects;
已创建72753行。
SQL> select * from redo_size;
        VALUE
----------
  34231736
SQL> select (34231736-34216916) redo_append , (34216916-25714940) redo_normal from dual;
REDO_APPEND REDO_NORMAL
----------- -----------
          14820        8501976
2、logging表：
SQL> create table test2 as select * from dba_objects where 1=0;
表已创建。
SQL> select * from redo_size;
        VALUE
----------
  34273348
SQL> insert into test2 select * from dba_objects;
已创建72754行。
SQL> select * from redo_size;
        VALUE
----------
  42775336
SQL> insert /*+ APPEND */  into test2 select * from dba_objects;
已创建72754行。
SQL> select * from redo_size;
        VALUE
----------
  42790156
SQL> select (42790156-42775336) redo_append , (42775336-34273348) redo_normal from dual;
REDO_APPEND REDO_NORMAL
----------- -----------
          14820        8501988
二、归档模式下：
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE例程已经关闭。
SQL> startup mount
ORACLE例程已经启动。
Total System Global Area  477073408 bytes
Fixed Size                                  1337324 bytes
Variable Size                        293603348 bytes
Database Buffers                  176160768 bytes
Redo Buffers                              5971968 bytes
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> archive log list
数据库日志模式                        存档模式
自动存档                          启用
存档终点                      USE_DB_RECOVERY_FILE_DEST
最早的联机日志串行        95
下一个存档日志串行    97
当前日志串行                    97
1、nologging表
SQL> select * from redo_size;
        VALUE
----------
        17936
SQL> insert into test1 select * from dba_objects;
已创建72754行。
SQL> select * from redo_size;
        VALUE
----------
    8490972
SQL> insert /*+ APPEND */  into test1 select * from dba_objects;
已创建72754行。
SQL> select * from redo_size;
        VALUE
----------
    8506164
SQL> select (8506164-8490972) redo_append , (8490972-17936) redo_normal from dual;
REDO_APPEND REDO_NORMAL
----------- -----------
          15192        8473036
2、logging表
SQL> select * from redo_size;
        VALUE
----------
    8506780
SQL> insert into test2 select * from dba_objects;
已创建72754行。
SQL> select * from redo_size;
        VALUE
----------
  16979516
SQL> insert /*+ APPEND */  into test2 select * from dba_objects;
已创建72754行。
SQL> select * from redo_size;
        VALUE
----------
  25518172
SQL> select (25518172-16979516) redo_append , (16979516-8506780) redo_normal from dual;
REDO_APPEND REDO_NORMAL
----------- -----------
      8538656        8472736
在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的。

三、insert /*+ append */会阻塞除select以外的DML语句，direct-path insert操作是单独一个事务。
SQL> select count(*) from test2;
  COUNT(*)
----------
      291016
SQL> insert into test2 select * from dba_objects;
已创建72754行。
SQL> select count(*) from test2;
  COUNT(*)
----------
      363770
SQL> insert /*+ APPEND */ into test2 select * from dba_objects;
已创建72754行
同一个session下：
SQL> select count(*) from test2;
select count(*) from test2
*
第  1  行出现错误:
ORA-12838:  无法在并行模式下修改之后读/修改对象
SQL> commit;
提交完成。
SQL> select count(*) from test2;
  COUNT(*)
----------
      436524
SQL> insert /*+ APPEND */ into test2 select * from dba_objects;
已创建72754行。
SQL> shutdown immediate
ORA-01097:  无法在事务处理过程中关闭  -  请首先提交或回退
SQL> select    * from v$mystat where rownum< 2;
              SID STATISTIC#            VALUE
---------- ---------- ----------
              224                    0                    1
SQL> select KADDR,TYPE,LMODE from v$lock where sid=224;
KADDR                        TY            LMODE
---------------- -- ----------
0000000071BAE180 TM                    6
0000000070CB11B8 TX                    6
另外开启一个会话，就会发现只能select，其他DML全部阻塞。
关键词：sql  select  append  from  redo  insert  模式  nologging  test  归档
相关推荐：
Complex query adjustment
Oracle 18.3 版本参数比较12.2 看新特性优化诊断增强
Direct IOT
Creating Oracle HCC (Hybrid Columnar Compression) on your Laptop
Direct-Path INSERT and NOLOGGING with Oracle 12.2
Attribute clustering….super cool
干货-ORACLE里的常用命令
Insert插入不同的列数量，统计信息对比
oracle批量插入优化方案















































































































































    推荐阅读


    上一篇：android注解处理技术APT

    下一篇：精英审判下载|精英审判app下载