|
说明：红字的redo size 即为产生日志的大小
测试环境：rhel6 oracle 11.2.0.4
本次测试在非归档和归档环境下，利用append和nologging方式插入数据 来减少redo size的大小
(注：本次测试仅在测试环境上测试数据的性能问题，生产环境切勿测试)
一、 非归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination/u01/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence 3
Current log sequence 5
1、object_wangx的数据正常插入一张表object_wangx_normal中
SQL> create table object_wangx_normal as select * from object_wangx where 1=2;
Table created.
SQL> set autotrace traceonly statistics;
SQL> insert into object_wangx_normal select * from object_wangx;
435806 rows created.
Statistics
----------------------------------------------------------
115 recursive calls
47865 db block gets
25491 consistent gets
0 physical reads
69165128redo size
863 bytes sent via SQL*Net toclient
979 bytes received via SQL*Netfrom client
3 SQL*Net roundtrips to/fromclient
1 sorts (memory)
0 sorts (disk)
435806 rows processed
SQL> commit;
SQL> SET AUTOTRACE OFF;
2、object_wangx的数据append插入一张表object_wangx_append中
SQL> create table object_wangx_append as select * from object_wangx where 1=2;
Table created.
SQL> set autotrace traceonly statistics;
SQL> insert /*+append*/ into object_wangx_append select * from object_wangx;
435806 rows created.
Statistics
----------------------------------------------------------
199 recursive calls
9538 db block gets
8698 consistent gets
0 physical reads
111196 redo size
850 bytes sent via SQL*Net toclient
991 bytes received via SQL*Netfrom client
3 SQL*Net roundtrips to/fromclient
1 sorts (memory)
0 sorts (disk)
435806 rows processed
SQL> commit;
Commit complete.
SQL> SET AUTOTRACE OFF;
3、object_wangx的数据append插入一张nologgng表object_wangx_append_nologging中
SQL>create table object_wangx_append_nologging as select * from object_wangx where1=2;
Tablecreated.
SQL>alter table object_wangx_append_nologging nologging;
Tablealtered.
SQL>set autotrace traceonly statistics;
SQL>insert /*+append*/ into object_wangx_append_nologging select * from object_wangx;
435806rows created.
Statistics
----------------------------------------------------------
206recursive calls
9536db block gets
8705consistent gets
2physical reads
111020redo size
850bytes sent via SQL*Net to client
1001bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
3sorts (memory)
0sorts (disk)
435806rows processed
SQL>commit;
Commitcomplete.
SQL>SET AUTOTRACE OFF;
4、object_wangx的数据append插入一张nologgng表object_wangx_append_nologging_table中，并在表名后加上 nologging关键字
SQL>create table object_wangx_append_nologging_table as select * from object_wangx where 1=2;
Tablecreated.
SQL> alter table OBJECT_WANGX_APPEND_NOLOGGING_TABLE nologging;
Tablealtered.
SQL>set autotrace traceonly statistics;
SQL> insert /*+append*/ into object_wangx_append_nologging_table nologging select * from object_wangx;
435806rows created.
Statistics
----------------------------------------------------------
169recursive calls
9442db block gets
8645consistent gets
0physical reads
104244 redo size
850bytes sent via SQL*Net to client
1017bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
3sorts (memory)
0sorts (disk)
435806rows processed
SQL>commit;
Commitcomplete.
SQL> SET AUTOTRACE OFF;
二、归档模式
SQL>archive log list;
Database log modeArchive Mode
Automaticarchival Enabled
ArchivedestinationUSE_DB_RECOVERY_FILE_DEST
Oldestonline log sequence 3
Next logsequence to archive 5
Currentlog sequence 5
SQL>select count(1) from object_wangx;
COUNT(1)
----------
782880
1、object_wangx的数据正常插入一张表object_wangx__arh_normal中
SQL> create table object_wangx_arh_normal as select * from object_wangx where 1=2;
Table created.
SQL> set autotrace traceonly statistics;
SQL>insert into object_wangx_arh_normalselect * from object_wangx;
782880 rows created.
Statistics
----------------------------------------------------------
101 recursive calls
57438 db block gets
30722 consistent gets
0 physical reads
83877640redo size
838 bytes sent via SQL*Net toclient
820 bytes received via SQL*Netfrom client
3 SQL*Net roundtrips to/fromclient
1 sorts (memory)
0 sorts (disk)
782880 rows processed
SQL> commit;
Commit complete.
SQL> SET AUTOTRACE OFF;
2、object_wangx的数据append插入一张表object_wangx_arh_append中
SQL> create table object_wangx_arh_append as select * from object_wangx where 1=2;
Table created.
SQL> set autotrace traceonly statistics;
SQL>insert /*+append*/ into object_wangx_arh_append select * from object_wangx;
782880 rows created.
Statistics
----------------------------------------------------------
180 recursive calls
11181 db block gets
10374 consistent gets
0 physical reads
84206664redo size
825 bytes sent via SQL*Net toclient
835 bytes received via SQL*Netfrom client
3 SQL*Net roundtrips to/fromclient
1 sorts (memory)
0 sorts (disk)
782880 rows processed
SQL> commit;
Commit complete.
SQL> SET AUTOTRACE OFF;
3、object_wangx的数据append插入一张nologgng表object_wangx_arg_append_nologging中
SQL>create table object_wangx_arh_append_n as select * from object_wangx where 1=2;
Tablecreated.
SQL>alter table object_wangx_arh_append_n nologging;
Tablealtered.
SQL> set autotrace traceonly statistics;
SQL>insert /*+append*/ into object_wangx_arh_append_n select * from object_wangx;
782880rows created.
Statistics
----------------------------------------------------------
202recursive calls
11181db block gets
10392consistent gets
0physical reads
109612 redo size
825bytes sent via SQL*Net to client
836bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
5sorts (memory)
0sorts (disk)
782880rows processed
SQL>commit;
Commitcomplete.
SQL>SET AUTOTRACE OFF;
4、object_wangx的数据append插入一张nologgng表object_wangx_arh_append_nologging_table中，并在表名后加上 nologging关键字
SQL>create table object_wangx_arh_append_n_t as select * from object_wangx where1=2;
Tablecreated.
SQL>alter table object_wangx_arh_append_n_t nologging;
Tablealtered.
SQL>set autotrace traceonly statistics;
SQL> insert /*+append*/ into object_wangx_arh_append_n_t nologging select * from object_wangx;
782880rows created.
Statistics
----------------------------------------------------------
226recursive calls
11181db block gets
10392consistent gets
0physical reads
109568 redo size
825bytes sent via SQL*Net to client
848bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
5sorts (memory)
0sorts (disk)
782880rows processed
SQL> commit;
Commitcomplete.
SQL>SET AUTOTRACE OFF;
结论：
1、在非归档时，insert单独设置append 即可减少redo log日志，无需设置表为nologging模式
2、在归档时，insert单独设置append不会减少redo log日志，在设置append的基础上 还需要配合设置表为nologging模式才可大量减少redolog日志 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
