Oracle数据库减少redo日志产生方式

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle数据库减少redo日志产生方式，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。


说明：红字的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日志

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问