对表进行dml操作时影响产生日志量的几个因素

本文链接：https://blog.youkuaiyun.com/orcldb/article/details/8270515

本文探讨了在归档模式和非归档模式下，通过改变表的logging属性和使用append提示对DML操作的影响。实验表明，在归档模式中，将表设为nologging并使用append插入能显著减少日志量；而在非归档模式下，仅使用append提示即可有效降低日志生成。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

归档模式
SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u02/archive_log
Oldest online log sequence     29
Next log sequence to archive   31
Current log sequence           31
1.创建测试表hat_objects
SQL> create table hat_objects as select * from all_objects where 1<>1;

Table created

2.打开执行计划
SQL> set autotrace traceonly statistics;
3.插入数据
SQL> insert into hat_objects select * from all_objects;

40685 rows created.

Statistics
----------------------------------------------------------
       5877 recursive calls
       5779 db block gets
      80216 consistent gets
      0 physical reads
    4684152 redo size
   675 bytes sent via SQL*Net to client
   585 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
      2 sorts (memory)
      0 sorts (disk)
      40685 rows processed

4.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;

40685 rows created.

Statistics
----------------------------------------------------------
       5278 recursive calls
   774 db block gets
      79281 consistent gets
      0 physical reads
    4692132 redo size
   660 bytes sent via SQL*Net to client
   599 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
      1 sorts (memory)
      0 sorts (disk)
      40685 rows processed

注：直接insert与使用append提示insert产生的日志大小相差无几，未减少日志的产生。

5.设置hat_objects为nologging模式
SQL> alter table hat_objects nologging;

Table altered.

6.插入数据
SQL> insert into hat_objects select * from all_objects;

40685 rows created.

Statistics
----------------------------------------------------------
       5232 recursive calls
       5104 db block gets
      80000 consistent gets
      0 physical reads
    4630652 redo size
   676 bytes sent via SQL*Net to client
   585 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
      2 sorts (memory)
      0 sorts (disk)
      40685 rows processed

7.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;

40685 rows created.

Statistics
----------------------------------------------------------
       5278 recursive calls
   776 db block gets
      79281 consistent gets
      0 physical reads
      19648 redo size
   662 bytes sent via SQL*Net to client
   599 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
      1 sorts (memory)
      0 sorts (disk)
      40685 rows processed

注：当表hat_objects设置为nologging模式后，使用append提示后insert，日志的产生量明显比直接

insert少。

非归档模式
SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /u02/archive_log
Oldest online log sequence     29
Current log sequence           31
1.创建测试表
SQL> create table hat_objects as select * from all_objects where 1<>1;

Table created.

2.插入数据
SQL> insert into hat_objects select * from all_objects;

40685 rows created.

Statistics
----------------------------------------------------------
       6773 recursive calls
       6276 db block gets
      80723 consistent gets
      2 physical reads
    4737072 redo size
   680 bytes sent via SQL*Net to client
   585 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
   21 sorts (memory)
      0 sorts (disk)
      40685 rows processed

3.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;

40684 rows created.

Statistics
----------------------------------------------------------
       5278 recursive calls
   762 db block gets
      79281 consistent gets
      0 physical reads
      18332 redo size
   664 bytes sent via SQL*Net to client
   599 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
      1 sorts (memory)
      0 sorts (disk)
      40684 rows processed

注：直接insert与使用append提示insert产生的日志大小相差很大，减少了日志的产生。

4.设置表hat_objects为nologging模式
SQL> alter table hat_objects nologging;

Table altered.

5.插入数据
SQL> insert into hat_objects select * from all_objects;

40684 rows created.

Statistics
----------------------------------------------------------
       5454 recursive calls
       5104 db block gets
      80005 consistent gets
      0 physical reads
    4630688 redo size
   680 bytes sent via SQL*Net to client
   585 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
      6 sorts (memory)
      0 sorts (disk)
      40684 rows processed

6.使用提示插入数据
SQL> insert/*+ append */ into hat_objects select * from all_objects;

40684 rows created.

Statistics
----------------------------------------------------------
       5278 recursive calls
   782 db block gets
      79281 consistent gets
      0 physical reads
      19648 redo size
   664 bytes sent via SQL*Net to client
   598 bytes received via SQL*Net from client
      4 SQL*Net roundtrips to/from client
      1 sorts (memory)
      0 sorts (disk)
      40684 rows processed

注：当表hat_objects设置为nologging模式后，使用append提示后insert，日志量比直接insert少，

但与表hat_objects设置为logging模式相比较，同样使用append提示产生的日志量差不多。

总结：在归档模式下，只有将表设置为nologging并且使用append提示才能大量减少日志的产生；在非

归档模式下，无论是否将表设置为nologging，只要使用append提示都会大量减少日志的产生。