对表进行dml操作时影响产生日志量的几个因素
归档模式
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提示都会大量减少日志的产生。