表在nologging与logging模式下执行append插入，redo大小差别

最新推荐文章于 2022-12-05 14:07:56 发布

songyb

最新推荐文章于 2022-12-05 14:07:56 发布

阅读量827

点赞数

分类专栏： Oracle Concept 文章标签：日志 append archive noarchivelog logging nologging

本文链接：https://blog.csdn.net/songyongbo_107/article/details/9199437

版权

Oracle Concept 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

下图为表在noarchivelog&archive、nologging&logging模式下，带index与不带index时，执行append插入所产生的redo大小差别汇总，到底哪种模式下产生日志最小呢？请仔细看下图：

==========================================================================================
ArchiveLog    Table Mode            index Mode            Insert Mode           		result
----------- 	------------- 				----------------- 		----------            ----------
noarchivelog	logging    						index 								append                 8,579,384
noarchivelog	no logging 						index 								append                 9,181,052
noarchivelog	logging    						no index 							append                    85,796
noarchivelog	logging 	 						index 								no append             21,203,404
noarchivelog	no logging 						no index 							append                    82,176
noarchivelog	no logging 						index 								no append             31,990,384
noarchivelog	logging 	 						no index 							no append              5,740,408
noarchivelog	no logging 						no index 							no append              8,416,268
------------------------------------------------------------------------------------------
archivelog		logging    						index 								append                 7,803,312
archivelog		no logging 						index 								append                13,537,620
archivelog		logging    						no index 							append                 5,810,724
archivelog		logging 	 						index 								no append             20,159,860
archivelog		no logging 						no index 							append                    82,248
archivelog		no logging 						index 								no append             20,006,884
archivelog		logging 	 						no index 							no append              5,739,100
archivelog		no logging 						no index 							no append              8,640,828
==========================================================================================

下面是测试：

--noarchive_redo.sql执行结果
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     16
Current log sequence           18
SQL> @noarchive_redo.sql
SQL> --**noarchivelog**logging**index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size                 50544

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

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size               8629928        8,579,384

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**noarchivelog**no logging**index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> --alter table t1 nologging;
SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size               8663892

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

50625 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size              17844944        9,181,052

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**noarchivelog**logging**no index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> --alter table t1 nologging;
SQL> --create index t1_idx on t1(object_name);
SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size              17872888

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

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size              17958684           85,796

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**noarchivelog**logging**index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size              17988968

SQL> insert into t1 select * from dba_objects;

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size              39192372       21,203,404

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**noarchivelog**no logging**no index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size              39222248

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

50623 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size              39304424           82,176

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**noarchivelog**no logging**index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size              39336740

SQL> insert into t1 select * from dba_objects;

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size              71327124       31,990,384

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**noarchivelog**logging**no index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size              71355752

SQL> insert into t1 select * from dba_objects;

50623 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size              77096160        5,740,408

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**noarchivelog**no logging**no index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size              77127604

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

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size              85543872        8,416,268

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>

--archive_redo.sql执行结果
SQL> @redo.sql
SQL> --**archivelog**logging**index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             358404892

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

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             366208204        7,803,312

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**archivelog**no logging**index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> --alter table t1 nologging;
SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             366243316

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

50625 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             379780936       13,537,620

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**archivelog**logging**no index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> --alter table t1 nologging;
SQL> --create index t1_idx on t1(object_name);
SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             379808948

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

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             385619672        5,810,724

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**archivelog**logging**index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             385651228

SQL> insert into t1 select * from dba_objects;

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             405811088       20,159,860

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**archivelog**no logging**no index**append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             405840964

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

50623 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             405923212           82,248

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**archivelog**no logging**index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             405957776

SQL> insert into t1 select * from dba_objects;

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             425964660       20,006,884

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**archivelog**logging**no index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             425993216

SQL> insert into t1 select * from dba_objects;

50623 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             431732316        5,739,100

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> --**archivelog**no logging**no index**no append
SQL> create table t1 tablespace users as select * from dba_objects where 1=0;

Table created.

SQL> alter table t1 nologging;

Table altered.

SQL> create index t1_idx on t1(object_name);

Index created.

SQL> mystat.sql
SQL> set echo off

NAME                      VALUE
-------------------- ----------
redo size             431765652

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

50624 rows created.

SQL> commit;

Commit complete.

SQL> mystat2.sql
SQL> set echo off

NAME                          V DIFF
-------------------- ---------- ----------------
redo size             440406480        8,640,828

SQL> drop table t1;

Table dropped.

SQL> alter system checkpoint;

System altered.

SQL>