oracle常规与直接路径插入(append)区别

from:http://space.itpub.net/?uid-9240380-action-viewspace-itemid-753150

SQL> create  table t_test(a int);

Table created

SQL>  insert into t_test select 1 from dual connect by level<=9e5;

900000 rows inserted

SQL> commit;

Commit complete

--查询表区分配信息,共计26 extent
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 0 8
T_TEST 1 8
T_TEST 2 8
T_TEST 3 8
T_TEST 4 8
T_TEST 5 8
T_TEST 6 8
T_TEST 7 8
T_TEST 8 8
T_TEST 9 8
T_TEST 10 8
T_TEST 11 8
T_TEST 12 8
T_TEST 13 8
T_TEST 14 8
T_TEST 15 8
T_TEST 16 128
T_TEST 17 128
T_TEST 18 128
T_TEST 19 128

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 20 128
T_TEST 21 128
T_TEST 22 128
T_TEST 23 128
T_TEST 24 128
T_TEST 25 128

26 rows selected

--删除部分数据
SQL> delete from t_test where rownum<=100000;

100000 rows deleted

SQL> commit;

Commit complete

---为表分配的26个extent并未因删除数据而free
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 0 8
T_TEST 1 8
T_TEST 2 8
T_TEST 3 8
T_TEST 4 8
T_TEST 5 8
T_TEST 6 8
T_TEST 7 8
T_TEST 8 8
T_TEST 9 8
T_TEST 10 8
T_TEST 11 8
T_TEST 12 8
T_TEST 13 8
T_TEST 14 8
T_TEST 15 8
T_TEST 16 128
T_TEST 17 128
T_TEST 18 128
T_TEST 19 128

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 20 128
T_TEST 21 128
T_TEST 22 128
T_TEST 23 128
T_TEST 24 128
T_TEST 25 128

26 rows selected

---常规路径插入
SQL> insert into t_test select 1 from dual connect by level<=1000;

1000 rows inserted

SQL> commit;

Commit complete

----常规路径插入会在hwm之下搜索可重用的数据块,并未分配新的extent,仍是26个extent
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 0 8
T_TEST 1 8
T_TEST 2 8
T_TEST 3 8
T_TEST 4 8
T_TEST 5 8
T_TEST 6 8
T_TEST 7 8
T_TEST 8 8
T_TEST 9 8
T_TEST 10 8
T_TEST 11 8
T_TEST 12 8
T_TEST 13 8
T_TEST 14 8
T_TEST 15 8
T_TEST 16 128
T_TEST 17 128
T_TEST 18 128
T_TEST 19 128

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 20 128
T_TEST 21 128
T_TEST 22 128
T_TEST 23 128
T_TEST 24 128
T_TEST 25 128

26 rows selected

---删除部分数据
SQL> delete from t_test where rownum<=200000;

200000 rows deleted

SQL> commit;

Commit complete

SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 0 8
T_TEST 1 8
T_TEST 2 8
T_TEST 3 8
T_TEST 4 8
T_TEST 5 8
T_TEST 6 8
T_TEST 7 8
T_TEST 8 8
T_TEST 9 8
T_TEST 10 8
T_TEST 11 8
T_TEST 12 8
T_TEST 13 8
T_TEST 14 8
T_TEST 15 8
T_TEST 16 128
T_TEST 17 128
T_TEST 18 128
T_TEST 19 128

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 20 128
T_TEST 21 128
T_TEST 22 128
T_TEST 23 128
T_TEST 24 128
T_TEST 25 128

26 rows selected

---直接路径插入
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=10000;

10000 rows inserted

SQL> commit;

Commit complete

---直接在hwm之上分配extent,并未重用hwm之下已删除的空间
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 0 8
T_TEST 1 8
T_TEST 2 8
T_TEST 3 8
T_TEST 4 8
T_TEST 5 8
T_TEST 6 8
T_TEST 7 8
T_TEST 8 8
T_TEST 9 8
T_TEST 10 8
T_TEST 11 8
T_TEST 12 8
T_TEST 13 8
T_TEST 14 8
T_TEST 15 8
T_TEST 16 128
T_TEST 17 128
T_TEST 18 128
T_TEST 19 128

SEGMENT_NAME EXTENT_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST 20 128
T_TEST 21 128
T_TEST 22 128
T_TEST 23 128
T_TEST 24 128
T_TEST 25 128
T_TEST 26 128

27 rows selected

SQL>

----再测试常规与 直接路径插入在产生redo方面的区别
SQL> create table t_test(a int);

Table created.

SQL> set autot exp stat
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot trace exp stat
SQL> insert into t_test select 1 from dual connect by level<=9e5;

900000 rows created.

Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time

-------------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01

| 1 | LOAD TABLE CONVENTIONAL | T_TEST | | |

|* 2 | CONNECT BY WITHOUT FILTERING| | | |

| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(LEVEL<=9e5)

Statistics
----------------------------------------------------------
1093 recursive calls
16025 db block gets
2900 consistent gets
0 physical reads
12302340 redo size
835 bytes sent via SQL*Net to client
815 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
900000 rows processed

SQL> set autot off
SQL> select count(*) from t_test;

COUNT(*)
----------
900000

SQL> truncate table t_test;

Table truncated.

SQL> set autot trace exp stat
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=9e5;

900000 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
845 recursive calls
2114 db block gets
191 consistent gets
2 physical reads
62320 redo size
822 bytes sent via SQL*Net to client
832 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
900000 rows processed

小结:直接路径插入产生的redo size大大小于常规路径插入

---测试常规路径与直接路径插入持锁信息区别

---11会话是执行常规与直接路径插入的会话
SQL> select * from v$lock where sid=11;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08 11 AE 100 0 4 0 24507 0
000007FF62C56A68 000007FF62C56AC0 11 TO 5003 1 3 0 1097 0

----在11会话进行 常规路径插入
SQL> insert into t_test select 1 from dual connect by level<=9e5;

900000 rows created.

---再次查询常规路径插入之后的持锁信息,多2条记录,持锁模式为3和6级锁,即行级锁和表级排它锁
SQL> /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08 11 AE 100 0 4 0 24521 0
000007FF62C56A68 000007FF62C56AC0 11 TO 5003 1 3 0 1111 0
000000000D5B62A0 000000000D5B6300 11 TM 67162 0 3 0 3 0
000007FF5ED150A0 000007FF5ED15118 11 TX 589827 707 6 0 3 0

SQL> /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08 11 AE 100 0 4 0 24540 0
000007FF62C56A68 000007FF62C56AC0 11 TO 5003 1 3 0 1130 0

----释放常规路径插入事务 
SQL> rollback;

Rollback complete.

-----运行直接路径插入
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=9e5;

900000 rows created.

SQL>

----查询直接路径插入之后的持锁信息,新增2条记录全是6级锁,全是表级排它锁
SQL> /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08 11 AE 100 0 4 0 24783 0
000007FF62C56A68 000007FF62C56AC0 11 TO 5003 1 3 0 1373 0
000000000D5B62A0 000000000D5B6300 11 TM 67162 0 6 0 49 0
000007FF5ED150A0 000007FF5ED15118 11 TX 65551 703 6 0 49 0

小结:常规路径插入持锁与直接路径插入持锁级别不同,在编写代码要充分考虑,不然会影响业务的并发
再回到上述的错误:
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
因为加在表级排它锁,即便在自己会话也不能操作自己.


----直接路径插入的一些操作限制:尤为重要

---如果违反任何一个操作限制,oracle自动转变化常规路径插入,不会报错
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then  OracleDatabase executes  conventional INSERT serially
without returning any message, unless otherwise noted:

---单一事务中,可以存在多个直接路径插入;但是,一个dml操作变更了某个表或者分区或索引之后,这个事务中其它的dml语句不能访问这个表或者分区或索引
You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a
particular table, partition, or  index, no other DML statement in the transaction can access that table, partition, or index.

---直接路径插入之前可以查询表,分区,索引,之后,不能访问这些对象;
Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.

----如果在同一个事务中,任何串行或并行操作想去访问某个正处于直接路径插入的对象.数据库返回错误,并拒绝此操作
If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the
database returns an error and rejects the statement.

---直接路径插入的目标对象不能是cluster
The target table cannot be of a cluster.

---同上,不能包含对象列
The target table cannot contain object  type columns.

----如果iot未分区,不能用于直接路径插入;或者它有一个映射表,或它被一个物化视图引用
Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.

----直接路径插入到iot的一个分区或者仅有一个分区的iot,会串行执行,即使iot开启了并行模式或者你指定了append提示;
Direct-path INSERT into a single partition of an index-organized table (IOT), or into a partitioned IOT with only one partition, will be done serially,
---但是,直接路径插入到分区的iot会开启并行模式,只要未使用分区扩展的名字且iot有多个分区
even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will
honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.

----用于直接路径插入的目标表不能定义触发器或者外键引用关系
The target table cannot have any triggers or referential integrity constraints defined on it.

---用于直接路径插入的目标表不能用于复制
The target table cannot be replicated.

----如果某个事务包含直接路径插入,不能用于分发(分布式环境)
A transaction containing a direct-path INSERT statement cannot be or become distributed.

---下面测试直接与常规路径插入生产undo的区别
---常规路径插入
SQL> insert into t_test select 1 from dual connect by level<=9e5;

900000 rows created.

---占用undo blocks 8855
SQL> select used_ublk,ubablk from v$transaction where addr=(select taddr from v$
session where sid=11);

USED_UBLK UBABLK
---------- ----------
271 8855

SQL> rollback;

Rollback complete.

---直接路径插入
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=9e5;

900000 rows created.

---占用undo blocks 7825,节约了1000多
SQL> select used_ublk,ubablk from v$transaction where addr=(select taddr from v$
session where sid=11);

USED_UBLK UBABLK
---------- ----------
2 7825

--小结:二者在undo产生方面也有区别

-------附：eggle的Nologging到底何时才能生效?-------里面与append相关的研究----

最初的问题是这个帖子:

http://www.itpub.net/showthread.php?threadid=239905

请大家仔细看那些测试的例子.

看了Tom的解释,始终觉得牵强.
开始以为可能是bug
经过观察和测试,终于发现了Nologging的秘密

---

我们知道,Nologging只在很少情况下生效
通常,DML操作总是要生成redo的

这个我们不多说.

关于Nologging和append,一直存在很多误解.
经过一系列研究,终于发现了Nologging的真相.

我们来看一下测试:

1.Nologging的设置跟数据库的运行模式有关

a.数据库运行在非归档模式下:

SQL> archive log list;  Database log mode              No Archive Mode  Automatic archival             Enabled  Archive destination            /opt/oracle/oradata/hsjf/archive  Oldest online log sequence     155  Current log sequence           157    SQL> @redo  SQL> create table test as select * from dba_objects where 1=0;    Table created.    SQL> select * from redo_size;         VALUE  ----------       63392    SQL>   SQL> insert into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     1150988    SQL>   SQL> insert /*+ append */ into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     1152368    SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual;    REDO_APPEND       REDO  ----------- ----------         1380    1087596    SQL> drop table test;    Table dropped.        

我们看到在Noarchivelog模式下,对于常规表的insert append只产生少量redo

b.在归档模式下

SQL> shutdown immediate  Database closed.  Database dismounted.  ORACLE instance shut down.  SQL> startup mount  ORACLE instance started.    Total System Global Area  235999908 bytes  Fixed Size                   451236 bytes  Variable Size             201326592 bytes  Database Buffers           33554432 bytes  Redo Buffers                 667648 bytes  Database mounted.    SQL> alter database archivelog;    Database altered.    SQL> alter database open;    Database altered.    SQL> @redo  SQL> create table test as select * from dba_objects where 1=0;    Table created.    SQL> select * from redo_size;         VALUE  ----------       56288    SQL>   SQL> insert into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     1143948    SQL>   SQL> insert /*+ append */ into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     2227712    SQL> select (2227712 -1143948) redo_append,(1143948 -56288) redo from dual;    REDO_APPEND       REDO  ----------- ----------      1083764    1087660    SQL> drop table test;    Table dropped.        

我们看到在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的

通过Logmnr分析日志得到以下结果:

SQL> select operation,count(*)    2  from v$logmnr_contents     3  group by operation;    OPERATION                          COUNT(*)  -------------------------------- ----------  COMMIT                                   17  DIRECT INSERT                         10470     INTERNAL                                 49  START                                    17                                            1          

我们注意到这里是DIRECT INSERT,而且是10470条记录,也就是每条记录都记录了redo.

2.对于Nologging的table的处理

a. 在归档模式下:

SQL> create table test nologging as select * from dba_objects where 1=0;    Table created.    SQL> select * from redo_size;         VALUE  ----------     2270284    SQL>   SQL> insert into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     3357644    SQL>   SQL> insert /*+ append */ into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     3359024    SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual;    REDO_APPEND       REDO  ----------- ----------         1380    1087360    SQL> drop table test;    Table dropped.          

我们注意到,只有append才能减少redo

b.在非归档模式下:

SQL> shutdown immediate  Database closed.  Database dismounted.  ORACLE instance shut down.  SQL> startup mount  ORACLE instance started.    Total System Global Area  235999908 bytes  Fixed Size                   451236 bytes  Variable Size             201326592 bytes  Database Buffers           33554432 bytes  Redo Buffers                 667648 bytes  Database mounted.  SQL> alter database noarchivelog;    Database altered.    SQL> alter database open;    Database altered.    SQL> @redo  SQL> create table test nologging as select * from dba_objects where 1=0;    Table created.    SQL> select * from redo_size;         VALUE  ----------       56580    SQL>   SQL> insert into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     1144148    SQL>   SQL> insert /*+ append */ into test select * from dba_objects;    10470 rows created.    SQL> select * from redo_size;         VALUE  ----------     1145528    SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual;    REDO_APPEND       REDO  ----------- ----------         1380    1087568    SQL>            

同样只有append才能减少redo的生成.

这就是通常大家认识的情况.

-The End-