insert /*+ append */ into一定会比insert into好吗？

    在速度上面大家都清楚直接加载插入数据确实比insert into快多了，但是如果在业务很麻烦的表这样做，估计会付出很大的性能甚至夯机的代价，
也许开发追求快，但是作为DB，要考虑在性能的基础上来追求卓越的速度

SQL> explain plan for insert /*+ append */ into t_app select * from t_app;
Explained.
SQL> select * from  table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3648959745
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |       | 23616 |  3643K|    99   (2)| 00:00:02 |
|   1 |  LOAD AS SELECT    | T_APP |       |       |            |          |===>直接加载的对表的操作是LOAD AS SELECT
|   2 |   TABLE ACCESS FULL| T_APP | 23616 |  3643K|    99   (2)| 00:00:02 |
----------------------------------------------------------------------------

SQL> explain plan for insert into t_noapp select * from t_noapp;
Explained.
SQL> select * from  table(dbms_xplan.display());
Plan hash value: 3980433360
------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         | 25301 |  3903K|    99   (2)| 00:00:02 |
|   1 |  LOAD TABLE CONVENTIONAL | T_NOAPP |       |       |            |          |====>普通insert对表的操作：LOAD TABLE CONVERTIONAL
|   2 |   TABLE ACCESS FULL      | T_NOAPP | 25301 |  3903K|    99   (2)| 00:00:02 |
------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
13 rows selected.

======================================================================================
SQL> alter session set events '10704 trace name context forever, level 10';
Session altered.

SQL> insert into /*+ append */ t_app select * from t_app;
26662 rows created.

SQL> commit;
Commit complete.

==>trac报告
Trace file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_2590.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home
System name:    Linux
Node name:    vm010148
Release:    2.6.18-194.el5
Version:    #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:    x86_64
VM name:    VMWare Version: 6
Instance name: orcl11g
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 2590, image: oracle@vm010148 (TNS V1-V3)

*** 2014-11-26 14:02:14.677
*** SESSION ID:(918.11441) 2014-11-26 14:02:14.677
*** CLIENT ID:() 2014-11-26 14:02:14.677
*** SERVICE NAME:(SYS$USERS) 2014-11-26 14:02:14.677
*** MODULE NAME:(SQL*Plus) 2014-11-26 14:02:14.677
*** ACTION NAME:() 2014-11-26 14:02:14.677
 
ksqgtl *** CU-9fae27d0-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
    ksqlkdid: 0001-0017-00001579

*** 2014-11-26 14:02:14.677
*** ksudidTrace: ksqgtl
    ksusesdi:   0000-0000-00000000
    ksusetxn:   0001-0017-00001579
ksqgtl: RETURNS 0

*** 2014-11-26 14:02:14.684
ksqrcl: CU,9fae27d0,0
ksqrcl: returns 0

*** 2014-11-26 14:02:14.685
ksqgtl *** TM-00005945-00000000 mode=6 flags=0x401 timeout=21474836 ***===>在直接加载时会在的对象object_id=22853即t_app表上加mode6的表锁
ksqgtl: xcb=0x9a9be940, ktcdix=2147483647, topxcb=0x9a9be940
    ktcipt(topxcb)=0x0

......
中间省略信息
.....
*** 2014-11-26 14:02:14.685
ksucti: init txn DID from session DID
ksqgtl:
    ksqlkdid: 0001-0017-00001579

*** 2014-11-26 14:02:14.685
*** ksudidTrace: ksqgtl
    ktcmydid(): 0001-0017-00001579
    ksusesdi:   0000-0000-00000000
    ksusetxn:   0001-0017-00001579
ksqgtl: RETURNS 0

*** 2014-11-26 14:02:14.724
ksqgtl *** MR-00000004-00000002 mode=4 flags=0x10000 timeout=21474836 ***
ksqgtl: xcb=0x9a9be940, ktcdix=2147483647, topxcb=0x9a9be940
    ktcipt(topxcb)=0x0

*** 2014-11-26 14:02:14.724
ksucti: init session DID from txn DID:
ksqgtl:
    ksqlkdid: 0001-0017-00001579

*** 2014-11-26 14:02:14.724
*** ksudidTrace: ksqgtl
    ktcmydid(): 0001-0017-00001579
    ksusesdi:   0000-0000-00000000
    ksusetxn:   0001-0017-00001579
ksqgtl: RETURNS 0

*** 2014-11-26 14:02:14.724
ksqrcl: MR,4,2
ksqrcl: returns 0

*** 2014-11-26 14:02:17.967
ksqrcl: CR,10017,2
ksqrcl: returns 0

*** 2014-11-26 14:02:17.967
ksqrcl: TX,7001d,a735
ksqrcl: returns 0

*** 2014-11-26 14:02:17.967
ksqrcl: TM,5945,0=====>在最后才释TM锁
ksqrcl: returns 0

=======================================================================================
SQL> alter session set events '10704 trace name context forever, level 10';
Session altered.

SQL> insert into /*+ append */ t_noapp select * from t_noapp;
26662 rows created.

SQL> commit;
Commit complete.


Trace file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_2633.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home
System name:    Linux
Node name:    vm010148
Release:    2.6.18-194.el5
Version:    #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:    x86_64
VM name:    VMWare Version: 6
Instance name: orcl11g
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 2633, image: oracle@vm010148 (TNS V1-V3)


*** 2014-11-26 14:04:17.957
*** SESSION ID:(918.11443) 2014-11-26 14:04:17.957
*** CLIENT ID:() 2014-11-26 14:04:17.957
*** SERVICE NAME:(SYS$USERS) 2014-11-26 14:04:17.957
*** MODULE NAME:(SQL*Plus) 2014-11-26 14:04:17.957
*** ACTION NAME:() 2014-11-26 14:04:17.957
 
ksqgtl *** CU-8aeb7b18-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
    ksqlkdid: 0001-0017-0000157A

*** 2014-11-26 14:04:17.957
*** ksudidTrace: ksqgtl
    ksusesdi:   0000-0000-00000000
    ksusetxn:   0001-0017-0000157A
ksqgtl: RETURNS 0

*** 2014-11-26 14:04:17.965
ksqrcl: CU,8aeb7b18,0
ksqrcl: returns 0

*** 2014-11-26 14:04:17.965
ksqgtl *** TM-00005946-00000000 mode=3 flags=0x401 timeout=21474836 ***===>普通加载会在的对象object_id=22854即t_noapp表上加mode3的表锁
ksqgtl: xcb=0x9aa42000, ktcdix=2147483647, topxcb=0x9aa42000
    ktcipt(topxcb)=0x0
.......
......
.....
.......

*** 2014-11-26 14:04:18.139
ksqrcl: HW,4,10005a8
ksqrcl: returns 0

*** 2014-11-26 14:04:20.825
ksqrcl: TX,70021,a732
ksqrcl: returns 0

*** 2014-11-26 14:04:20.826
ksqrcl: TM,5946,0
ksqrcl: returns 0

从上面堆栈的调用可知道，
直接加载数据，在操作过程中，以独占的方式x锁表，other session 无法获得TM lock，
而普通的加载锁表是RX(SX)方式，允许other session获得TM锁，从而再到row级锁。