ITPub博客

首页 > Linux操作系统 > Linux操作系统 > LOGGING vs NOLOGGING 及 普通插入和直接插入专题研究

LOGGING vs NOLOGGING 及 普通插入和直接插入专题研究

原创 Linux操作系统 作者:jiuniang012 时间:2009-07-11 13:22:41 0 删除 编辑
Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4 LOGGING vs NOLOGGING 普通插入和直接插入专题研究

前言:

很多人对LOGGING, NOLOGGING概念比较模糊,以及它们在归档和非归档模式下对普通插入和直接插入的速度,产生REDO日志的量的影响上不是很明确,本文将对此进行测试和总结.

总结:

为了清晰明了,我先把总结放在开头.

1.       普通插入不管是在归档还是非归档模式下, 不管是logging还是nologging都产生大量的REDO日志

2.       直接插入只有在归档的logging模式下产生大量的REDO日志,其它模式产生很少的REDO日志.

3.       普通插入归档模式下速度比非归档模式下慢很多.

4.       直接插入在归档logging模式下速度慢其它模式两倍.

 

 

测试:

1. Archivelog mode

   在归档模式下,

   1). LOGGING, NOLOGGING中直接插入对REDO的大小影响比较大,NOLOGGING 产生很少的REDO日志,LOGGING产生很大的REDO日志.

       而对于普通插入来说,影响不大.

   2). LOGGING模式下,Direct insert /*+ append */ 和普通的insert一样会产生大量的REDO日志,只是直接插入的速度要快些.

       SQL> create table t_logging as select * from dba_objects where 1=2;

      

       Table created.

      

       Elapsed: 00:00:00.16

       SQL> select table_name, logging from dba_tables where table_name='T_LOGGING';

       

       TABLE_NAME                     LOG

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

       T_LOGGING                      YES

      

       Elapsed: 00:00:00.08

       SQL> select * from redo_size;

      

            VALUE

       ----------

         17473724

      

       Elapsed: 00:00:00.01

       SQL> insert into t_logging select * from dba_objects; --普通insert花费4.83s并产生5733184REDO日志

      

       50534 rows created.

      

       Elapsed: 00:00:04.83

       SQL> select * from redo_size;

      

            VALUE

       ----------

         23206908

      

       Elapsed: 00:00:00.01

       SQL> select 23206908-17473724 diff from dual;

      

             DIFF

       ----------

          5733184

      

       Elapsed: 00:00:00.01

       SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费1.84s并产生5750044REDO日志

      

       50534 rows created.

      

       Elapsed: 00:00:01.84

       SQL> select * from redo_size;

      

            VALUE

       ----------

         28956952

      

       Elapsed: 00:00:00.01

       SQL> select 28956952-23206908 diff from dual;

      

             DIFF

       ----------

          5750044

      

       Elapsed: 00:00:00.01

       NOLOGGING模式下,Direct insert /*+ append */ 才产生很少的REDO日志, 比普通insert少得多.而且速度要快得多.

       SQL> select * from redo_size;

      

            VALUE

       ----------

         11683020

      

       Elapsed: 00:00:00.01

 

       SQL> create table t_nologging nologging as select * from dba_objects where 1=2;

      

       Table created.

      

       Elapsed: 00:00:00.19

      

       SQL> select table_name, logging from dba_tables where table_name='T_NOLOGGING';

      

       TABLE_NAME                     LOG

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

       T_NOLOGGING                    NO

      

       Elapsed: 00:00:00.07

      

       SQL> insert into t_nologging select * from dba_objects; --普通insert花费5.87s并产生了5757080REDO

      

       50533 rows created.

      

       Elapsed: 00:00:05.87

       SQL> select * from redo_size;

      

            VALUE

       ----------

         17440100

      

       Elapsed: 00:00:00.01

       SQL> select 17440100-11683020 diff from dual;

      

             DIFF

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

                 5757080

      

       Elapsed: 00:00:00.00

       SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.97s并产生11124REDO日志

      

       50533 rows created.

      

       Elapsed: 00:00:00.97

       SQL> select * from redo_size;

      

            VALUE

       ----------

         17451224

      

       Elapsed: 00:00:00.00

             

       SQL> select 17451224-17440100 diff from dual;

      

             DIFF

       ----------

            11124

      

       Elapsed: 00:00:00.00

   

2. NOARCHIVELOG mode

   非归档模式

   1). LOGGING, NOLOGGING不管是对直接插入还是普通插入影响都不大.

       但直接插入在这两种模式下都产生很少的REDO日志,速度也比归档模式下快一点点.

       普通插入在这两种模式下都产生差不多一样的REDO日志,且别直接插入要多. 速度上明显比归档模式下快很多.

   1.1). LOGGING模式下,

        SQL> select * from redo_size;

       

             VALUE

        ----------

             19008

       

        Elapsed: 00:00:00.06

        SQL> create table t_logging as select * from dba_objects where 1=2;

       

        Table created.

       

        Elapsed: 00:00:00.30

        SQL> select table_name, logging from dba_tables where table_name='T_LOGGING';

       

        TABLE_NAME                     LOG

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

        T_LOGGING                      YES

       

        Elapsed: 00:00:00.13

        SQL> insert into t_logging select * from dba_objects; --普通insert花费0.71s并产生5758088REDO日志

       

        50531 rows created.

       

        Elapsed: 00:00:00.71

        SQL> select * from redo_size;

       

             VALUE

        ----------

           5777096

       

        Elapsed: 00:00:00.02

        SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费0.75s并产生11168REDO日志

       

        50531 rows created.

       

        Elapsed: 00:00:00.75

        SQL> select * from redo_size;

       

             VALUE

        ----------

           5788264

       

        Elapsed: 00:00:00.02

        SQL> select 5788264-5777096 diff from dual;

       

              DIFF

        ----------

             11168

       

        Elapsed: 00:00:00.00

    1.2) NOLOGGING 模式下

       SQL> create table t_nologging nologging as select * from dba_objects where 1=2;

 

       Table created.

      

       Elapsed: 00:00:00.21

       SQL> select * from redo_size;

      

            VALUE

       ----------

         11519140

      

       Elapsed: 00:00:00.06

       SQL> select table_name, logging from dba_tables where table_name='T_NOLOGGING';

      

       TABLE_NAME                     LOG

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

       T_NOLOGGING                    NO

      

       Elapsed: 00:00:00.07

      

       SQL> insert into t_nologging select * from dba_objects; --普通insert花费1.35s并产生5735500REDO日志

      

       50532 rows created.

      

       Elapsed: 00:00:01.35

       SQL> select * from redo_size;

       

            VALUE

       ----------

         17254640

      

       Elapsed: 00:00:00.05

       SQL> select 17254640-11519140 diff from dual;

      

             DIFF

       ----------

          5735500

      

       Elapsed: 00:00:00.00

       SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.74s并产生11168REDO日志

      

       50532 rows created.

      

       Elapsed: 00:00:00.74

       SQL> select * from redo_size;

      

            VALUE

       ----------

         17265808

      

       Elapsed: 00:00:00.02

       SQL> select 17265808-17254640 diff from dual;

      

             DIFF

       ----------

            11168

      

       Elapsed: 00:00:00.01

      

 

Summary:

archive mode:

logging

SQL> insert into t_logging select * from dba_objects; --普通insert花费4.83s并产生5733184REDO日志

SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费1.84s并产生5750044REDO日志

 

nologging

SQL> insert into t_nologging select * from dba_objects; --普通insert花费5.87s并产生了5757080REDO

SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.97s并产生11124REDO日志

 

noarchive mode:

logging

SQL> insert into t_logging select * from dba_objects; --普通insert花费0.71s并产生5758088REDO日志

SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费0.75s并产生11168REDO日志

NOLOGGING 模式下

SQL> insert into t_nologging select * from dba_objects; --普通insert花费1.35s并产生5735500REDO日志

SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.74s并产生11168REDO日志

 

参考文档:

http://blog.csdn.net/huanghui22/archive/2006/10/16/1337420.aspx

http://tolywang.itpub.net/post/48/427215

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

下一篇: Logminer 用法
请登录后发表评论 登录
全部评论

注册时间:2009-07-02

  • 博文量
    126
  • 访问量
    211330