ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 强制日志何时生效的分析

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

原创 Linux操作系统 作者:scott8486 时间:2011-03-28 15:57:37 0 删除 编辑
Normal 0 0 2 false false false MicrosoftInternetExplorer4 关于Nologging何时才会生效的问题,需要分数据库是否是在归档模式下运行。在归档模式下,如果DB設置為no force logging模式,並且建立的TableNologging,则在Insert时,加上append隐函数,则可以大大减少redo size的数量,没有加append隐函数,则跟普通的insert table没有区别。如果建立的Table没有Nologging参数,在执行Insert语句时,无论是否加apend隐函数,均视为正常insert,产生的redo size不会减少。在非归档模下,无论建立Table时,是否有加Nologging参数,执行Inert时,使用了append隐函数,都可以大大减少redo size的数量,不使用append隐函数,redo size数量相当。

 

创建一个查询每步操作产生的redo sizeview。创建redo_size viewsql

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.      DBno 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)DBno force logging模式,并且所建tablelogging(默认值),在插入数据时指定apend隐函数与不指定apend隐函数(/*+append*/),产生的redo相当.

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

 

2.      DBforce 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

 

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

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-03-28

  • 博文量
    42
  • 访问量
    137249