Oracle 强制日志何时生效的分析

Normal 0 0 2 false false false MicrosoftInternetExplorer4 关于 Nologging 何时才会生效的问题，需要分数据库是否是在归档模式下运行。在归档模式下， 如果 DB 設置為 no force logging 模式 , 並且 建立的 Table 为 Nologgin g ，则在 Insert 时，加上 append 隐函数，则可以大大减少 redo size 的数量， 没有加 append 隐函数，则跟普通的 insert table 没有区别。如果建立的 Table 没有 Nologging 参数，在执行 Insert 语句时，无论是否加 apend 隐函数，均视为正常 insert ，产生的 redo size 不会减少。在非归档模下，无论建立 Table 时，是否有加 Nologging 参数，执行 Inert 时，使用了 append 隐函数，都可以大大减少 redo size 的数量，不使用 append 隐函数， redo size 数量相当。

 

创建一个查询每步操作产生的redo size的view。创建redo_size view的sql是

CREATE VIEW redo_size

AS

   SELECT VALUE

     FROM v$mystat, v$statname

    WHERE v$mystat.statistic# = v$statname.statistic#

      AND v$statname.NAME = 'redo size';

以下测试为DB在归档模式下进行

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /data/orcl/arch

Oldest online log sequence     150

Next log sequence to archive   152

Current log sequence           152

1.      DB在no force logging模式

   SQL> col force_logging format a20

SQL> select force_logging from v$database;

FORCE_LOGGING

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

NO

创建logging模式表

SQL> select value "a0" from redo_size;

 

        a0

----------

         0

SQL> CREATE TABLE redo_test01  AS SELECT * FROM dba_objects WHERE 1=2;

 

SQL> select value "a1"from redo_size;

 

        a1

----------

     22516

SQL> insert /*+append*/ into  redo_test01 select * from dba_objects;

72813 rows created.

SQL> select value "a2" from redo_size;

       a2

----------

   8593328

SQL> commit;

Commit complete.

A2-a1=8570812

SQL> insert /*+append*/ into  redo_test01 select * from dba_objects;

72813 rows created.

SQL> select value "a3" from redo_size;

        a3

----------

   17140116

SQL> commit;

Commit complete.

A3-a2=8546788

SQL> insert into  redo_test01 select * from dba_objects;

72813 rows created.

SQL> select value "a4" from redo_size;

       a4

----------

   25623484

SQL> commit;

Commit complete.

A4-a3=8483368

SQL> insert into  redo_test01 select * from dba_objects;

72813 rows created.

SQL> select value "a5" from redo_size;

        a5

----------

    34103760

SQL> commit;

Commit complete.

A5-a4=8480276

创建nologging模式表

SQL> select value "a0" from redo_size;

       a0

----------

     0

SQL> CREATE TABLE redo_test02 nologging AS SELECT * FROM dba_objects WHERE 1=2;

Table created.

SQL> select value "a1"from redo_size;

        a1

----------

     22576

SQL> insert /*+append*/ into  redo_test02 select * from dba_objects;

72814 rows created.

SQL> select value "a2" from redo_size;

        a2

----------

     63324

SQL> commit;

Commit complete.

A2-a1=40748

SQL> insert /*+append*/ into  redo_test02 select * from dba_objects;

72814 rows created.

SQL> select value "a3" from redo_size;

       a3

----------

     79964

SQL> commit;

Commit complete.

A3-a2=16640

SQL> insert into  redo_test02 select * from dba_objects;

72814 rows created.

SQL> select value "a4" from redo_size;

      a4

----------

   8562516

SQL> commit;

Commit complete.

A4-a3=8482552

SQL> insert into  redo_test02 select * from dba_objects;

72814 rows created.

SQL> select value "a5" from redo_size;

        a5

----------

  17044204

SQL> commit;

Commit complete.

A5-a4=8481688

 

综上所述,(1)在DB为no force logging模式,并且所建table为logging(默认值),在插入数据时指定apend隐函数与不指定apend隐函数(/*+append*/),产生的redo相当.

(2)在DB为no force logging模式,并且所建table为nologging,在插入数据时指定apend隐函数(/*+append*/),产生较少的redo,若不指定apend隐函数(/*+append*/),则产生较大的redo.

 

2.      DB在force logging模式

SQL> select force_logging from v$database;

FORCE_LOGGING

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

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES

创建logging模式表

SQL> select value "a0" from redo_size;

        a0

----------

         0

SQL> CREATE TABLE redo_test03  AS SELECT * FROM dba_objects WHERE 1=2;

Table created.

SQL> select value "a1"from redo_size;

       a1

----------

     23616

SQL> insert /*+append*/ into  redo_test03 select * from dba_objects;

72812 rows created.

SQL> select value "a2" from redo_size;

        a2

----------

   8594428

SQL> commit;

Commit complete.

A2-a1=8570812

SQL> insert /*+append*/ into  redo_test03 select * from dba_objects;

72812 rows created.

SQL> select value "a3" from redo_size;

       a3

----------

  17141276

SQL> commit;

Commit complete.

A3-a2=8546848

SQL> insert into  redo_test03 select * from dba_objects;

72812 rows created.

SQL> select value "a4" from redo_size;

        a4

----------

  25624000

SQL> commit;

Commit complete.

A4-a3=8482724

SQL> insert into  redo_test03 select * from dba_objects;

72812 rows created.

SQL> select value "a5" from redo_size;

       a5

----------

  34103920

SQL> commit;

Commit complete.

A5-a4=8479920

创建nologging模式表

SQL> select value "a0" from redo_size;

       a0

----------

       0

SQL> CREATE TABLE redo_test04 nologging AS SELECT * FROM dba_objects WHERE 1=2;

Table created.

SQL> select value "a1"from redo_size;

        a1

----------

     25796

SQL> insert /*+append*/ into  redo_test04 select * from dba_objects;

72813 rows created.

SQL> select value "a2" from redo_size;

       a2

----------

   8596608

SQL> commit;

Commit complete.

A2-a1=8570812

SQL> insert /*+append*/ into  redo_test04 select * from dba_objects;

72813 rows created.

SQL> select value "a3" from redo_size;

       a3

----------

  17143368

SQL> commit;

Commit complete.

A3-a2=8546760

SQL> insert into  redo_test04 select * from dba_objects;

72813 rows created.

SQL> select value "a4" from redo_size;

        a4

----------

  25625820

SQL> commit;

Commit complete.

A4-a3=8482452

SQL> insert into  redo_test04 select * from dba_objects;

72813 rows created.

SQL> select value "a5" from redo_size;

        a5

----------

  34106556

SQL> commit;

Commit complete.

A5-a4=8480736

 

综上所述,在DB为force logging模式下,不论所建table为logging(默认值),还是nologging,在插入数据时指定apend隐函数与不指定apend隐函数(/*+append*/),产生的redo相当.

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

转载于:http://blog.itpub.net/25574072/viewspace-691133/