![001.gif](/images/edit/face/001.gif)
创建一个查询每步操作产生的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/