Oracle 强制日志何时生效的分析-优快云博客

本文探讨了在不同数据库模式下（归档与非归档），设置Nologging及使用append提示对Redo大小的影响。实验表明，在归档模式且数据库处于noforcelogging模式时，对于Nologging表，使用append提示可以显著减少Redo生成；而在forcelogging模式下，无论表是否为Nologging，使用append提示对Redo的影响较小。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Normal 0 0 2 false false false MicrosoftInternetExplorer4 关于Nologging何时才会生效的问题，需要分数据库是否是在归档模式下运行。在归档模式下，如果DB設置為no force logging模式,並且建立的Table为Nologging，则在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

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

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

创建logging模式表

SQL> select value "a0" from redo_size;

----------

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

SQL> select value "a1"from redo_size;

----------

22516

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

72813 rows created.

SQL> select value "a2" from redo_size;

----------

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;

----------

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;

----------

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;

----------

34103760

SQL> commit;

Commit complete.

A5-a4=8480276

创建nologging模式表

SQL> select value "a0" from redo_size;

----------

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

Table created.

SQL> select value "a1"from redo_size;

----------

22576

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

72814 rows created.

SQL> select value "a2" from redo_size;

----------

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;

----------

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;

----------

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;

----------

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

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

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;

----------

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

Table created.

SQL> select value "a1"from redo_size;

----------

23616

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

72812 rows created.

SQL> select value "a2" from redo_size;

----------

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;

----------

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;

----------

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;

----------

34103920

SQL> commit;

Commit complete.

A5-a4=8479920

创建nologging模式表

SQL> select value "a0" from redo_size;

----------

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

Table created.

SQL> select value "a1"from redo_size;

----------

25796

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

72813 rows created.

SQL> select value "a2" from redo_size;

----------

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;

----------

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;

----------

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;

----------

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/