oracle nologging append 注意

a8cz893p50

insert append需要注意的
2010-07-28 11:34

1. append方式添加记录对insert into ... values语句不起作用。 　　2. 以append方式批量插入的记录，其存储位置在hwm 之上，即使hwm之下存在空闲块也不能使用。 　　3. 以append方式插入记录后，要执行commit，才能对表进行查询。否则会出现错误： 　　ORA-12838: 无法在并行模式下修改之后读/修改对象 　　4. 在归档模式下，要把表设置为nologging，然后以append方式批量添加记录，才会显著减少redo数量。在非归档模式下，不必设置表的nologging属性，即可减少redo数量。如果表上有索引，则append方式批量添加记录，不会减少索引上产生的redo数量，索引上的redo数量可能比表的redo数量还要大。 　　用insert append可以实现直接路径加载，速度比常规加载方式快。但有一点需要注意: insert append时在表上加“6”类型的锁，会阻塞表上的所有DML语句。因此在有业务运行的情况下要慎重使用。若同时执行多个insert append对同一个表并行加载数据，并不一定会提高速度。因为每一时刻只能有一个进程在加载(排它锁造成)。 　　SQL> create table test as select * from dba_objects where 1=2; 　　表已创建。 　　SQL> insert into test select * from dba_objects; 　　已创建11344行。 　　SQL> set lines 150 SQL> col object_type for a10 SQL> col object for a30 SQL> col username for a10 SQL> col osuser for a10 SQL> col program for a30 SQL> col sid for 99,999 SQL> col locked_mode for 99 SQL> col spid for 999,999 SQL> select o.object_type,o.owner||'.'||o.object_name object,s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,l.lock ed_mode 2 from v$locked_object l,dba_objects o,v$session s,v$process p 3 where l.object_id = o.object_id 4 and s.sid=l.session_id 5 and s.paddr=p.addr 6 and o.object_name = upper('&obj'); 输入 obj 的值: test 原值    6: and o.object_name = upper('&obj') 新值    6: and o.object_name = upper('test') 　　OBJECT_TYP OBJECT                             SID    SERIAL# SPID         USERNAME   OSUSER     PROGRAM        LOCKED_MODE ---------- ------------------------------ ------- ---------- ------------ ---------- ---------- ------------------------ ------ ----------- TABLE      SYS.TEST                           147         19 784          SYS        CNPEKALT02 sqlplus.exe                  3                                                                                      2\jyu 可以看到,insert 时在表上加的是“3”类型的锁。 　　SQL> rollback; 　　回退已完成。 　　SQL> insert /*+ append */ into test select * from dba_objects; 　　已创建11344行。 　　SQL> set lines 150 SQL> col object_type for a10 SQL> col object for a30 SQL> col username for a10 SQL> col osuser for a10 SQL> col program for a30 SQL> col sid for 99,999 SQL> col locked_mode for 99 SQL> col spid for 999,999 SQL> select o.object_type,o.owner||'.'||o.object_name object,s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,l.lock ed_mode 2 from v$locked_object l,dba_objects o,v$session s,v$process p 3 where l.object_id = o.object_id 4 and s.sid=l.session_id 5 and s.paddr=p.addr 6 and o.object_name = upper('&obj'); 输入 obj 的值: test 原值    6: and o.object_name = upper('&obj') 新值    6: and o.object_name = upper('test') 　　OBJECT_TYP OBJECT                             SID    SERIAL# SPID         USERNAME   OSUSER     PROGRAM        LOCKED_MODE ---------- ------------------------------ ------- ---------- ------------ ---------- ---------- ------------------------ ------ ----------- TABLE      SYS.TEST                           147         19 784          SYS        CNPEKALT02 sqlplus.exe                  6                                                                                      2\jyu 　　而执行insert append时在表上加的是“6”类型的锁。 　　insert append与一般的insert在表上加的锁不一样。insert append加的是exclusive的锁。因此要注意在执行insert append尽快提交，否则会阻塞其它事务对同一张表的DML语句。 此外， ORA-12838:是由于在执行insert append之后没有提交或回滚，接着又执行DML语句造成的。解决办法是在insert append 之后加上commit或rollback。 请看下面的测试： SQL> delete from test; 已删除9831行。 SQL> insert /*+ append */ into test select * from temp_fsum_od; 已创建3277行。 SQL> insert into test select * from temp_fsum_od; insert into test select * from temp_fsum_od             * 第 1 行出现错误: ORA-12838: 无法在并行模式下修改之后读/修改对象 　　SQL> update test set OD_CODE=upper(OD_CODE) ; update test set OD_CODE=upper(OD_CODE)        * 第 1 行出现错误: ORA-12838: 无法在并行模式下修改之后读/修改对象 　　SQL> delete from test; delete from test * 第 1 行出现错误: ORA-12838: 无法在并行模式下修改之后读/修改对象 　　SQL> SQL> insert /*+ append */ into test select * from temp_fsum_od; insert /*+ append */ into test select * from temp_fsum_od * 第 1 行出现错误: ORA-12838: 无法在并行模式下修改之后读/修改对象 注意，我先执行了一个delete语句，又执行了insert append. 这个delete语句并没有造成后面的insert append报错。 但在我执行了一个insert append之后，再执行任何DML语句都会报错。 这说明，在执行了insert append 之后，必须commit或rollback，才能再执行其它DML语句。 但在insert append之前可以执行DML语句，而不会对insert append造成影响。 所以我们注意一点就可以了，即只要业务允许，在执行insert 　　 insert append方法的使用 大家众所周知，向数据库里插入数据有很多种方法，insert、sqlloader、for update等。每种方法都有其不同的特点。 但是每一种方法在其服务器设置不同的情况下也是有不同的执行情况的。例如：insert。 insert 属于DML语言（即数据操作语言，还包括select，delete，update）。网上介绍过一种insert append方法，语句格式为 insert /* +append+ */ into table_name select column_name1…… from table_name2 ; 这种方法据说可以占用很少的redo表空间，占用很少的redo表空间也就是省略了一些归档的时间，这样是可以提高insert的执行效率的！但是经过测试我发现insert append并不是在任何时候都可以节省时间的。 第一种情况：database为archivelog状态，这种情况下，就算你用insert append也是不一定提高插入效率的。但是如果你在建表的时候，将目标表建成nologging的，然后再使用insert append就会很快。 第二种情况：database为noarchivelog状态，这种情况下，如果情况下采用insert方法向表中插入数据，占用的redo空间的大小与archivelog状态下占用的大小是相当的，不论表是否为nologging。但是如果采用insert append方法的话，通过redo的占用值大家可以发现，不论表是否为nologging，所占用的redo的大小都是很小的。也就说明：在数据库为noarchivelog的状态下，采用insert append方法，如果表不是nologging，系统也会自动将表转换为nologging（即在执行insert append之前，先执行一个alter table arch1 nologging;）。 以下为测试的具体过程： -------- 数据库为归档模式 create table arch (status varchar(2)) nologging;        ----- create a nologging table Table created create table arch1 (status varchar(2)) ;                   ----- create a logging table Table created select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size' and b.value > 0;                                                 ----- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                           1332780 insert into arch select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                           1744516   ----- +411736 insert into arch1 select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                           2156000   ------ +411484 insert /*+append*/ into arch select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                           2169864   ----- +13864 insert /*+append*/ into arch1 select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                           2555448   ----- +385584 spool off; -------- 数据库为非归档模式 create table arch (status varchar(2)) nologging;                   ----- create a nologging table Table created create table arch1 (status varchar(2)) ;                             ----- create a logging table Table created ---- view redo engross space                              NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                             33208 insert into arch select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                            444704 ----- +411496 insert into arch1 select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                            856160 ----- +411456 insert /*+append*/ into arch select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                            870024 ----- +13864 insert /*+append*/ into arch1 select 'ok' from dba_objects 29514 rows inserted ---- view redo engross space NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                            884004----- +13980 spool off;