Insert 中 Append Hint 生效时间 实验

本文主要讨论oracle 中append Hint生效的情况与logging 和nologging的关系

Database 在ARCHIVED 模式下：

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/oradata/archive2/
Oldest online log sequence     22
Next log sequence to archive   24
Current log sequence           24

(1)在非Logging 模式下

SQL> SELECT LOGGING FROM TABS WHERE TABLE_NAME='OBJECT_TEST';

LOG
---
NO

将测试表object_test 插入数据：

SQL> insert into object_test select * from dba_objects;

15907 rows created.

Statistics
----------------------------------------------------------
        719  recursive calls
       1701  db block gets
       1402  consistent gets
        227  physical reads
    1779840  redo size
        673  bytes sent via SQL*Net to client
        622  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      15907  rows processed

将测试表object_test ,采用append方式插入数据：

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

15907 rows created.

Statistics
----------------------------------------------------------
        559  recursive calls
        457  db block gets
        971  consistent gets
        215  physical reads
      28192  redo size
        659  bytes sent via SQL*Net to client
        633  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15907  rows processed


15907 rows created.

结论：以上可见采用append直接插入的方式，大大减少了redo的生成数量。

（2）在logging模式下

SQL> SELECT  LOGGING FROM TABS WHERE TABLE_NAME='OBJECT_TEST';

LOG
---
YES

将测试表object_test ，采用append方式插入数据：

SQL> insert /*+append*/into object_test select * from dba_objects;
15907 rows created.

Statistics
----------------------------------------------------------
        559  recursive calls
        457  db block gets
        971  consistent gets
        215  physical reads
    1788168  redo size
        662  bytes sent via SQL*Net to client
        633  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15907  rows processed

结论：在logging 模式下，即使采用append方式插入也会产生大量redo 日志

Database 在非ARCHIVED 模式下：

设置为非归档模式：

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback off;       --要求先关闭flashback database

SQL> alter database noarchivelog;

SQL> alter database open;

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/oradata/archive2/
Oldest online log sequence     22
Current log sequence           24

(1)在非Logging 模式下

SQL> alter table object_test nologging;

SQL> SELECT LOGGING FROM TABS WHERE TABLE_NAME='OBJECT_TEST';

LOG
---
NO

将测试表object_test 插入数据：

SQL> insert into object_test select * from dba_objects;
15907 rows created.

Statistics
----------------------------------------------------------
         34  recursive calls
       1200  db block gets
       1268  consistent gets
          0  physical reads
    1749108  redo size
        686  bytes sent via SQL*Net to client
        674  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15907  rows processed

将测试表object_test ，采用append方式插入数据：

SQL> insert /*+append*/into object_test select * from dba_objects;
15907 rows created.

Statistics
----------------------------------------------------------
         84  recursive calls
        245  db block gets
        845  consistent gets
          0  physical reads
       4264  redo size
        670  bytes sent via SQL*Net to client
        684  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15907  rows processed

结论：非归档模式中，在nologging 模式下append 生效。

(2)在Logging 模式下

SQL> SELECT LOGGING FROM TABS WHERE TABLE_NAME='OBJECT_TEST';
LOG
---
YES

将测试表object_test 插入数据：

SQL> insert into object_test select * from dba_objects;
15907 rows created.

Statistics
----------------------------------------------------------
         82  recursive calls
       1216  db block gets
       1270  consistent gets
          0  physical reads
    1751560  redo size
        684  bytes sent via SQL*Net to client
        673  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15907  rows processed

将测试表object_test ，采用append方式插入数据：

SQL> insert /*+append*/into object_test select * from dba_objects;
15907 rows created.

Statistics
----------------------------------------------------------
         35  recursive calls
        234  db block gets
        832  consistent gets
          0  physical reads
       2708  redo size
        667  bytes sent via SQL*Net to client
        684  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15907  rows processed

结论：非归档模式中，即使在logging 模式下append 生效，没有产生日志。

结论：1、Logging在归档模式下是生效的，不论是否使用append都产生大量的redo，但是Logging 在非归档模式下，就不生效了，并不总是产生大量redo。

           2、append方式的insert能大量较少redo日志生成。但是前提是Logging不生效（在非归档情况下就不生效）

           3、在insert中，影响redo日志量与归档，Logging/nologging， append 三个相关。

补充：也可以创建视图查看当前session产生的redo，通过比较前后两次的量，知道redo差异；

create or replace view redo_size

as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

SQL> select * from redo_size;

     VALUE
----------
   5389368