昨晚做一个数据割接，将两张表的数据以 append 方式 插入到 A 表，割到一段时间之后，发现大量的TM 锁
HOLDER SID ID1 ID2 LMODE REQUEST TYPE CTIME BLOCK INST_ID ------- ---------- ---------- ---------- ---------- ---------- ---- ---------- ---------- ---------- holder: 2947 816780 0 6 0 TM 22 1 2 waiter 1120 816780 0 0 3 TM 22 0 2 waiter 890 816780 0 0 3 TM 22 0 2 waiter 1331 816780 0 0 3 TM 22 0 2 waiter 1653 816780 0 0 3 TM 22 0 2 waiter 345 816780 0 0 3 TM 22 0 2 waiter 646 816780 0 0 3 TM 22 0 2 waiter 730 816780 0 0 3 TM 22 0 2 waiter 1136 816780 0 0 3 TM 22 0 2 waiter 1731 816780 0 0 3 TM 22 0 2 waiter 2711 816780 0 0 3 TM 22 0 2 问题原因： insert /*+ append */ 时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive）， 其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），从下面截图中可以看出当时持锁者的mode 为 6， 这使其他DML在直接路径加载期间将被阻塞。 下图是当时应用连接过来的会话对表 ord_t_item_repository 的 insert 操作 JDBC Thin Client 16742 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16666 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 35068 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 35070 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 35084 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16685 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 17239 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 17253 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 35074 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 35076 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16740 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16670 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16677 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16681 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T PROGRAM SPID SQL_ID SQL_TEXT1 - ------------------------------------------------ ------------------------ ------------- ------------------------------------------- JDBC Thin Client 17094 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 17843 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16683 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16687 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16952 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T sqlplus@irora09s (TNS V1-V3) 11233 7qxp4ywv5xvtw insert /*+ append */ into ord_t_ite JDBC Thin Client 16664 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T plsqlDev.exe 45231 6ph04820fdr0z select /*+use_nl(a,b,c)*/distinct a.sid,a.s JDBC Thin Client 35078 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 35080 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 35082 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16738 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 16807 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T JDBC Thin Client 17257 gp99rgyddj2h3 insert into ORD_T_ITEM_REPOSITORY(ORDERID,T 总结： a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。 b、insert /*+ append */ 时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。 c、insert /*+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。