昨晚做一个数据割接,将两张表的数据以 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的位置开始插入,也许会造成空间浪费。
b、insert /*+ append */ 时会对表加锁(排它锁),会阻塞表上的除了select以外所有DML语句;传统的DML在TM enqueue上使用模式3(row exclusive),其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6(exclusive),这使其他DML在直接路径加载期间将被阻塞。
c、insert /*+ append */ 直接路径加载,速度比常规加载方式快。因为是从HWM的位置开始插入,也许会造成空间浪费。