表段、索引段上的LOGGING与NOLOGGING

标签: logginginsertoracletabledatabase数据库
7977人阅读 评论(0) 收藏 举报
分类:

--====================================

-- 表段、索引段上的LOGGINGNOLOGGING

--====================================

 

    在有些情况下,对于表段和索引段可以采用记录日志的模式,也可以使用不记录日志的模式。如在对表段、索引段使用数据泵导入时,可以

使用NOLOGGING模式,而使用DATA GUARD或对可用性较高的场景中需要记录日志,甚至使用强制记录日志。本文介绍了在表段,索引段使用

LOGGINGNOLOGGING时产生redo的大小以及DIRECT INSERT APPEND 的使用方法。

 

    NOLOGGING跟数据库的运行模式有关,ii的默认安装都是非归档模式,并且自动归档默认是禁用。在安装gg时,可以选择是否归

档。NOLOGGIING将记录少量日志信息到日志文件。如果数据库级别或表空间级别使用了FORCE LOGGING强制日志记录模式,则该选项无效。

 

一、表段,索引段上使用一般DDLDML时,LOGGINGNOLOGGING情况   

    1.查看数据库的归档模式

        有关设置日志归档模式的问题,请参考:

            Oracle 联机重做日志文件(ONLINE LOG FILE)

            Oracle 归档日志

 

        sys@ORCL> select log_mode,force_logging from v$database;

 

        LOG_MODE     FOR

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

        ARCHIVELOG   NO

       

        sys@ORCL> archive log list;

        Database log mode              Archive Mode

        Automatic archival             Enabled

        Archive destination            /u01/bk/arch

        Oldest online log sequence     50

        Next log sequence to archive   51

        Current log sequence           51  

       

        sys@ORCL> select tablespace_name,logging,force_logging from dba_tablespaces;

 

        TABLESPACE_NAME                LOGGING   FOR

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

        SYSTEM                         LOGGING   NO

        UNDOTBS1                       LOGGING   NO

        SYSAUX                         LOGGING   NO

        TEMP                           NOLOGGING NO

        USERS                          LOGGING   NO

        PERFSTAT                       LOGGING   NO

       

        scott@ORCL> select * from v$version;

 

        BANNER

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

        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

        PL/SQL Release 10.2.0.1.0 - Production

        CORE    10.2.0.1.0      Production

        TNS for Linux: Version 10.2.0.1.0 - Production

        NLSRTL Version 10.2.0.1.0 - Production

       

    2.创建一个查看redo size 的视图redo_size

        CREATE OR REPLACE FORCE VIEW "SYS"."REDO_SIZE"

        AS

          SELECT a.name,b.value

          FROM v$statname a

          JOIN v$mystat b

            ON a.statistic# = b.statistic#

          WHERE a.name = 'redo size';

 

        sys@ORCL> create public synonym redo_size for redo_size;

 

        sys@ORCL> grant select on redo_size to scott;

 

    3.在归档模式下比较表段上的NOLOGGINGLOGGING

        scott@ORCL> select * from redo_size;

 

        NAME                 VALUE

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

        redo size             1644

 

        scott@ORCL> CREATE TABLE tb_obj_nolog NOLOGGING AS SELECT * FROM dba_objects;  --nologging模式创建表

 

        scott@ORCL> select 1644 last,70064 as cur,(70064-1644) diff from dual;--使用nologging模式建表产生的redo size

 

              LAST        CUR       DIFF

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

              1644      70064      68420

 

        scott@ORCL> CREATE TABLE tb_obj_log LOGGING AS SELECT * FROM dba_objects;    --使用logging模式来创建表

 

        sscott@ORCL> select * from redo_size;   --查看当前的redo size

 

        NAME                 VALUE

        --------------- ----------             --查看logging模式产生的redo size -68420=1274048,nologging日志模

        redo size          1344112               --式多出了19倍多

 

        scott@ORCL> select table_name,logging from user_tables where table_name like 'TB_OBJ%';--查看创建表的日志记录模式

 

        TABLE_NAME                     LOG

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

        TB_OBJ_LOG                     YES

        TB_OBJ_NOLOG                   NO

 

    4.基于索引来比较redo size(同样是在归档模式下)

        scott@ORCL> select * from redo_size;    --查看当前的redo_size

 

        NAME                 VALUE

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

        redo size             1140

 

        scott@ORCL> create index idx_tb_obj_log on tb_obj_log(object_id);  --基于表tb_obj_log来创建索引

 

        scott@ORCL> select * from redo_size;      --查看当前的redo_size

 

        NAME                 VALUE

        --------------- ----------                --基于loggiing模式,创建索引产生的redo size 221600-1140=220460

        redo size           221600

 

        scott@ORCL> alter index idx_tb_obj_log rebuild;    --重建索引

             

        scott@ORCL> select 221600 last,448132 cur,448132-221600 diff from dual; 

       

              LAST        CUR       DIFF

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

            221600     448132     226532   --重建索引后产生的redo size 226532,比直接创建时产生的redo size还要多

 

             

        scott@ORCL> alter index idx_tb_obj_log rebuild nologging; --使用nologging重建索引。

                                                --也可以在创建索引的时候直接使用nologging关键字

                                                --如:create index idx_tb_obj_nolog tb_obj_nolog(object_id) nologging

        scott@ORCL> select * from redo_size;    --查看当前的redo size

 

        NAME                 VALUE

        --------------- ----------              --基于nologging日志模式重建索引产生的redo size469160-448132=21028

        redo size           469160

 

    5.非归档模式下的LOGGINGNOLOGGING 

 

        scott@ORCL> drop table tb_obj_log purge;

 

        scott@ORCL> drop table tb_obj_nolog purge;

       

        scott@ORCL> select log_mode,force_logging from v$database;  --切换日志到非归档模式后,下面是查询的结果

 

        LOG_MODE     FOR

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

        NOARCHIVELOG NO

       

        scott@ORCL> select * from redo_size;    --查看当前的redo size

 

        NAME                 VALUE

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

        redo size             1688

        scott@ORCL> create table tb_obj_log as select * from dba_objects;   --创建表对象,使用logging日志记录模式

 

        scott@ORCL> select * from redo_size;   --查看当前的redo size

 

        NAME                 VALUE

        --------------- ----------            --使用logging日志记录模式,创建表对象之后产生的redo size -1688 =68548

        redo size            70236

 

        scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects; --创建表对象,使用nologging日志记录模式

 

        scott@ORCL> select * from redo_size;   --查看当前的redo size

 

        NAME                 VALUE

        --------------- ----------             --使用nologging日志记录模式创建表对象之后产生的redo size135464-70236=65228

 

        redo size           135464

         

    6.小结:

        使用loggingnologging来创建对象或执行DML

            对于非归档模式下,其产生的日志信息(redo size)相差的并不大

            对于归档模式下,logging模式产生的日志将远远大于使用nologging模式产生的日志量

 

二、使用DIRECT INSERT APPEND方式的LOGGINGNOLOGGING情况       

    DIRECT INSERT APPENDOracle插入数据到数据库的一种方式之一。使用APPEND方式来导入,其实是将记录直接存放到高水位线(HWM)之上,

    而不考虑高水位线之下的空闲块。

   

    1.数据库运行在非归档模式下

        a.使用logging模式创建表

            scott@ORCL> select log_mode from v$database;

 

            LOG_MODE

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

            NOARCHIVELOG   

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

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

            redo size             1764

 

            scott@ORCL> create table tb_obj_log as select * from dba_objects where 1=0;

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------     --建表产生的redo23908-1764=22144

            redo size            23908  

 

            scott@ORCL> insert into tb_obj_log select * from dba_objects;

 

            11634 rows created.

            Elapsed: 00:00:00.36

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------    --直接使用insert时产生的redo1281060-23908=1257152

            redo size          1281060  

 

            scott@ORCL> insert /*+ append */ into tb_obj_log select * from dba_objects;

 

            11634 rows created.

            Elapsed: 00:00:00.26

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------    --使用append模式时产生的redo1284740-1281060=3680

            redo size          1284740    --普通insert比使用append insert多产生1257152/3680=341redo

 

        b.使用nologging模式创建表

            scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1=0;

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------     --使用nologging创建空表tb_obj_nolog时产生的日志量1305812-1284740=21072

            redo size          1305812

 

            scott@ORCL> insert into tb_obj_nolog select * from dba_objects; 

 

            11635 rows created.

            Elapsed: 00:00:00.21

            scott@ORCL> select * from redo_size;                          

 

            NAME                 VALUE

            --------------- ----------    --使用普通insert插入记录产生的日志量2562664-1305812=1256852

            redo size          2562664

 

            scott@ORCL> insert /* +append */ into tb_obj_nolog select * from dba_objects;

 

            11635 rows created.

            Elapsed: 00:00:00.18

            scott@ORCL> select * from redo_size;                                     

 

            NAME                 VALUE

            --------------- ----------       --使用append模式时产生的redo 3766404-2562664=1203740

            redo size          3766404     

                                            

        c.redo的比较

            在具有logging属性对象中,使用append模式时产生的redo1284740-1281060=3680

                普通insert比使用append insert多产生/3680=341redo

            在具有nologging属性对象中,使用append insert模式与普通insert模式产生的redo量相差不太大,

                append insert模式为,而普通的insert模式为

           

    2.数据库运行在归档模式下

        a.前期处理

            scott@ORCL> drop table tb_obj_log purge;

 

            scott@ORCL> drop table tb_obj_nolog purge;

           

            sys@ORCL> select log_mode from v$database;

 

            LOG_MODE

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

            ARCHIVELOG

       

        b.创建表对象并进行比较

            scott@ORCL> create table tb_obj_log as select * from dba_objects where 1=0;    --logging模式创建表对象

 

            scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1=0;--nologging模式创建表对象

 

            scott@ORCL> select * from redo_size;    

 

            NAME                 VALUE

            --------------- ----------     --查看当前的redo size 46844

            redo size            46844

 

            scott@ORCL> insert into tb_obj_log select * from dba_objects;    --为表tb_obj_log使用常规insert插入记录

 

            11598 rows created.

            Elapsed: 00:00:00.25

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------       --tb_obj_log使用常规insert插入记录产生的redo size 1299120-46844=1252276

            redo size          1299120

 

            scott@ORCL> insert into tb_obj_nolog select * from dba_objects;   --为表tb_obj_nolog使用常规insert插入记录

 

            11598 rows created.

            Elapsed: 00:00:00.28

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------    --tb_obj_nolog使用常规insert插入记录产生的redo size 2552880-1299120=1253760

            redo size          2552880

 

            scott@ORCL> insert /* +append */ into tb_obj_log select * from dba_objects;--tb_obj_log使用insert append方式 

 

            11598 rows created.

            Elapsed: 00:00:00.20

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------    --tb_obj_log使用insert append插入记录产生的redo size 3750852-2552880=1197972

            redo size          3750852

 

            scott@ORCL> insert /* +append */ into tb_obj_nolog select * from dba_objects;--tb_obj_nolog使用insert append方式 

 

            11598 rows created.

            Elapsed: 00:00:00.18

 

            scott@ORCL> select * from redo_size;

 

            NAME                 VALUE

            --------------- ----------    --tb_obj_nolog使用insert append插入记录产生的redo size 4948764-3750852=1197912

            redo size          4948764

       

        c.redo的比较

            归档模式下,具有nologging特性的表tb_obj_nolog,使用insert append方式插入的速度最快,且日志量最小,为。而

            logging特性的表tb_obj_log使用insert append方式时的日志量为,相差不是很大。对于使用普通的insert插入,则

            产生的日志量差异比较大。

   

    3.小结

        对于表对象插入记录时,使用常规insert 与使用direct insert append方式比较

            在非归档模式下,表对象在使用nologging模式时,两者产生的日志量相差不大,而使用logging模式时,常规insert的日志量远

                大于direct insert append方式。

            在归档模式下,表对象使用logging模式,两者产生的日志量相差不大。而表对象使用nologging模式时,则使用insert append

                将使得性能有所提高。

        在非归档模式下的inesrt append操作将是性能最高的。

           

    4.direct insert append使用时的注意事项     

        a.当使用insert into ... values语句时,不能够使用append方式

        b.append方式为批量插入的记录,因此新插入的记录被存储在hwm 之上,对于hwm之下空闲块将不会被使用。

        c.append方式插入记录后,要执行commit,才能对表进行查询。否则会出现错误:

            ORA-12838: cannot read/modify an object after modifying it in parallel

        d.在归档模式下,表对象具有nologging属性,且以append方式批量添加记录,才会显著减少redo数量。

        e.在非归档模式下,表对象即便具有logging属性,也可减少redo数量。

        f.对于表上具有索引的表对象,如果新增的记录数量为整个表的很少一部分,则直接以append方式批量添加记录,如果原表记录很少,

            实时性要求不是很高,而新增记录很多,可以先删除索引,在使用append方式追加记录,最后再创建索引。

 

三、日志记录模式请参考

日志记录模式(LOGGING 、FORCE LOGGING 、NOLOGGING)

 

四、更多参考

有关闪回特性请参考

        Oracle 闪回特性(FLASHBACK DATABASE)

Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 闪回特性(Flashback Query、Flashback Table)

Oracle 闪回特性(Flashback Version、Flashback Transaction)

 

有关基于用户管理的备份和备份恢复的概念请参考:

        Oracle 冷备份

        Oracle 热备份

        Oracle 备份恢复概念

        Oracle 实例恢复

        Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)

       

    有关RMAN的恢复与管理请参考:

        RMAN 概述及其体系结构

        RMAN 配置、监控与管理

        RMAN 备份详解

        RMAN 还原与恢复

       

    有关Oracle体系结构请参考:

        Oracle 实例和Oracle数据库(Oracle体系结构)

        Oracle 表空间与数据文件

        Oracle 密码文件

        Oracle 参数文件

Oracle 数据库实例启动关闭过程

        Oracle 联机重做日志文件(ONLINE LOG FILE)

        Oracle 控制文件(CONTROLFILE)

        Oracle 归档日志

 

       

           

 

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4858155次
    • 积分:49500
    • 等级:
    • 排名:第62名
    • 原创:610篇
    • 转载:0篇
    • 译文:7篇
    • 评论:706条
    关于我

    • 十年以上数据库运维管理,性能优化经验
    • 提供数据库相关技术支持及数据库培训
    •  全部文章,欢迎扩散,转载请注明出处


    QQ/Weixin:645746311

    DBA交流群:170233858

    打赏作者
      支付宝&微信支付二维码
      支付宝&微信支付
    最新评论