今天遇到一个有意思的案例，一开发同事告诉我他删除一个表的记录非常慢，已经快1个多小时了还没有完成。而且删除的记录只有1百多条。真是大跌眼镜的一件事情。最后发现该表与多个表有外键关联关系（这个表即是主表、又是从表），最后我禁用引用该表的外键约束后。一秒内删除了记录。然后启用外键约束关系。下面记录、分析一下解决过程的思路（下面是在测试环境的记录，数据量不一样）。
我去处理这个问题时，首先怀疑可能是SQL的阻塞、触发器、外键约束、高水位线等因素中的某一个导致DELETE操作慢，于是我打算一个一个排除，我先试着删除一条记录，然后去检查SQL的阻塞情况，结果使用下面SQL语句并没有发现SQL被阻塞。于是SQL的阻塞导致DELETE慢的原因被我排除了。
SELECT '节点 ' || A.INST_ID || ' SESSION ' || A.SID || ',' || A_S.SERIAL# ||
' 阻塞了 节点 ' || B.INST_ID || ' SESSION ' || B.SID || ',' || B_S.SERIAL# BLOCKINFO,
A.INST_ID,
A_S.SID,
A_S.SCHEMANAME,
A_S.MODULE,A_S.STATUS,
A.TYPE LOCK_TYPE,
A.ID1,
A.ID2,
DECODE(A.LMODE,
0,
'NONE',1,
NULL,2,
'ROW-S (SS)',3,
'ROW-X (SX)',4,
'SHARE (S)',5,
'S/ROW-X (SSX)',6,
'EXCLUSIVE (X)') LOCK_MODE, '后为被阻塞信息' ,B.INST_ID BLOCKED_INST_ID,
B_S.SID BLOCKED_SID,
B.TYPE BLOCKED_LOCK_TYPE,
DECODE(B.REQUEST,
0,
'NONE',1,
NULL,2,
'ROW-S (SS)',3,
'ROW-X (SX)',4,
'SHARE (S)',5,
'S/ROW-X (SSX)',6,
'EXCLUSIVE (X)') BLOCKED_LOCK_REQUEST,B_S.SCHEMANAME BLOCKED_SCHEMANAME,
B_S.MODULE BLOCKED_MODULE,B_S.STATUS BLOCKED_STATUS,
B_S.SQL_ID BLOCKED_SQL_ID,
OBJ.OWNER BLOCKED_OWNER,
OBJ.OBJECT_NAME BLOCKED_OBJECT_NAME,
OBJ.OBJECT_TYPE BLOCKED_OBJECT_TYPE,
CASEWHEN B_S.ROW_WAIT_OBJ# <> -1 THEN
DBMS_ROWID.ROWID_CREATE(1,
OBJ.DATA_OBJECT_ID,
B_S.ROW_WAIT_FILE#,
B_S.ROW_WAIT_BLOCK#,
B_S.ROW_WAIT_ROW#)
ELSE '-1' END BLOCKED_ROWID, --THE BLOCKED ROWIDDECODE(OBJ.OBJECT_TYPE,
'TABLE','SELECT * FROM ' || OBJ.OWNER || '.' || OBJ.OBJECT_NAME ||
' WHERE ROWID=''' ||
DBMS_ROWID.ROWID_CREATE(1,
OBJ.DATA_OBJECT_ID,
B_S.ROW_WAIT_FILE#,
B_S.ROW_WAIT_BLOCK#,
B_S.ROW_WAIT_ROW#) || '''',
NULL) BLOCKED_DATA_QUERYSQL FROM GV$LOCK A,GV$LOCK B,
GV$SESSION A_S, GV$SESSION B_S,DBA_OBJECTS OBJ
WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2AND A.BLOCK > 0 --BLOCK THE OTHER SQL
AND B.REQUEST > 0AND ((A.INST_ID = B.INST_ID AND A.SID <> B.SID) OR
(A.INST_ID <> B.INST_ID))
AND A.SID = A_S.SID AND A.INST_ID = A_S.INST_ID AND B.SID = B_S.SID AND B.INST_ID = B_S.INST_ID AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+)ORDER BY A.INST_ID,A.SID;
接下来，我检查了该表的的触发器，结果并没有发现DELETE触发器。也就是说DELETE操作并不会触发任何触发器。触发器导致DELETE慢的怀疑也可以排除掉了。
SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='INV_LOCATION_PALLETS'
我用show_space检查了一下这个表的高水位线，发现并没有问题，不需要收缩高水位线。高水位线这个因素也可以排除了。只剩下外键约束的影响了。于是检查了一下有哪些表是该表的从表，如下所示
SELECT /*+RULE*/ D.CONSTRAINT_NAME PK_NAME,
D.TABLE_NAME
|| '.' || D.COLUMN_NAME PK_COLUMN,
A.CONSTRAINT_TYPE,
B.CONSTRAINT_NAME FK_NAME,
B.TABLE_NAME
|| '.' || B.COLUMN_NAME FK_COLUMN
FROM DBA_CONSTRAINTS A JOIN DBA_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER = B.OWNER JOIN DBA_CONSTRAINTS C ON A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME AND A.R_OWNER = C.OWNER JOIN DBA_CONS_COLUMNS D ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME AND C.OWNER = D.OWNERWHERE D.TABLE_NAME = 'INV_LOCATION_PALLETS'
有时候也可以用下面语句查看引用这个表的外键引用关系
SELECT * FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='PK_INV_LOCATION_PALLETS'
如上所示，INV_LOCATION_PALLETS这个表有三个从表，而这里面有个表的记录很大，大概2千多万。所以导致DELETE操作很慢。
我们可以用跟踪当前会话，查看一下DELETE操作，就会发现它会去处理从表，检查从表有没有对应的记录，而这个外键刚好也没有索引（下面是使用tkprof命令格式化的内容）。在这篇Delete the data on the table very slow(删除数据慢)博客里面跟深入的介绍、分析了删除表删除数据慢的原因。在此不做过多赘述了。
DELETE INVENTORY.INV_LOCATION_PALLETS WHEREPALLET_ID =1039928
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 3 26 1Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.00 0.01 0 3 26 1Misses in library cache during parse: 1Optimizer mode: ALL_ROWS
Parsing user id: 5 Rows Row Source Operation
------- --------------------------------------------------- 1 DELETE INV_LOCATION_PALLETS (cr=752149 pr=735050 pw=0 time=7550289 us)1 INDEX UNIQUE SCAN PK_INV_LOCATION_PALLETS (cr=3 pr=0 pw=0 time=38 us)(object id 59532)
********************************************************************************
select /*+ all_rows */ count(1)
from "INVENTORY"."INV_REQ_HD" where "TO_PALLET_ID" = :1call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.75 0.74 70540 78205 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 0.75 0.74 70540 78205 0 1Misses in library cache during parse: 0Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 1 SORT AGGREGATE (cr=78205 pr=70540 pw=0 time=743169 us)0 TABLE ACCESS FULL INV_REQ_HD (cr=78205 pr=70540 pw=0 time=743155 us)
********************************************************************************
select /*+ all_rows */ count(1)
from "INVENTORY"."INV_REQ_HD" where "FROM_PALLET_ID" = :1call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.69 0.67 70528 78205 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 0.69 0.68 70528 78205 0 1Misses in library cache during parse: 0Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 1 SORT AGGREGATE (cr=78205 pr=70528 pw=0 time=680000 us)0 TABLE ACCESS FULL INV_REQ_HD (cr=78205 pr=70528 pw=0 time=679987 us)
********************************************************************************
select /*+ all_rows */ count(1)
from "INVENTORY"."INV_REQ_LINES" where "TO_PALLET_ID" = :1call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 3.30 3.23 296991 297868 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 3.30 3.23 296991 297868 0 1Misses in library cache during parse: 0Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 1 SORT AGGREGATE (cr=297868 pr=296991 pw=0 time=3232134 us)0 TABLE ACCESS FULL INV_REQ_LINES (cr=297868 pr=296991 pw=0 time=3232122 us)
********************************************************************************
select /*+ all_rows */ count(1)
from "INVENTORY"."INV_REQ_LINES" where "FROM_PALLET_ID" = :1call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 2.94 2.88 296991 297868 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 2.94 2.88 296991 297868 0 1Misses in library cache during parse: 0Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 1 SORT AGGREGATE (cr=297868 pr=296991 pw=0 time=2885783 us)0 TABLE ACCESS FULL INV_REQ_LINES (cr=297868 pr=296991 pw=0 time=2885772 us)
********************************************************************************
beginsys.dbms_output.get_line(line => :line, status => :status);
end;call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 4 0.00 0.00 0 0 0 0Execute 4 0.00 0.00 0 0 0 4Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 8 0.00 0.00 0 0 0 4Misses in library cache during parse: 0Optimizer mode: ALL_ROWS
Parsing user id: 5 
参考资料：
http://www.anbob.com/archives/1962.html/comment-page-1