oracle 模式_Oracle实验--insert /*+APPEND*/与insert不同模式下产生redo大小

概述

在非归档模式下，INSERT语句采用HINT /*+ APPEND*/的方式确实比普通INSERT语句产生的redo要少很多。而归档模式呢？表是不是也有区别？下面做个实验来测试下~

---

insert /*+ append */注意事项：

a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。

b、insert /*+ append */ 时会对表加锁(排它锁)，会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3(row exclusive)，其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6(exclusive)，这使其他DML在直接路径加载期间将被阻塞。

c、insert /*+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。

---

1.环境介绍

数据库版本：

SQL> select * from v$version;

这里选了其中一台测试数据库服务器来演示。

网上说测试时不要使用auto trace来查看redo size，这个值是有偏差的.建议建立一个视图：

SQL> create or replace view redo_size asselect valuefrom v$mystat, v$statnamewhere v$mystat.statistic# = v$statname.statistic#and v$statname.name = 'redo size';

---

实例演示：

1 非归档模式

SQL> archive log list;

1.1 nologging表

SQL> create table test1 nologging as select * from dba_objects where 1=0;SQL> select * from redo_size;SQL> insert into test1 select * from dba_objects; SQL> select * from redo_size;SQL> insert /*+ APPEND */ into test1 select * from dba_objects;SQL> select * from redo_size;SQL> select (10416364-10393068) redo_append ,(10393068-65740) redo_normal from dual;

1.2 logging表：

SQL> create table test2 as select * from dba_objects where 1=0;SQL> select * from redo_size;SQL> insert into test2 select * from dba_objects;SQL> select * from redo_size;SQL> insert /*+ APPEND */ into test2 select * from dba_objects;SQL> select * from redo_size;SQL> select (20807336-20782980) redo_append , (20782980-10455852) redo_normal from dual;

2 归档模式下：

SQL> shutdown immediateSQL> startup mountSQL> alter database archivelog;SQL> alter database open;SQL> archive log list

2.1 nologging表

SQL> select * from redo_size;SQL> insert into test1 select * from dba_objects;SQL> select * from redo_size;SQL> insert /*+ APPEND */ into test1 select * from dba_objects;SQL> select * from redo_size;SQL> select (10343044-10318716) redo_append , (10318716-21484) redo_normal from dual;

2.2 logging表

SQL> select * from redo_size;SQL> insert into test2 select * from dba_objects;SQL> select * from redo_size;SQL> insert /*+ APPEND */ into test2 select * from dba_objects;SQL> select * from redo_size;SQL> select (31025676-20639888) redo_append , (20639888-10343044) redo_normal from dual;

在归档模式下,对于常规表的insert append产生和insert同样的redo。

此时的insert append实际上并不会有性能提高,但是此时的append是生效了的。

---

非归档模式下，无论是nologging表还是logging表，表的insert append产生的redo远远小于insert同样的redo。

归档模式下，对于nologging表，表的insert append产生的redo远远小于insert同样的redo。而对于logging表的insert append产生和insert同样的redo。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~