今天遇到一个有意思的案例，一开发同事告诉我他删除一个表的记录非常慢，已经快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,
CASE
WHEN 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 ROWID
DECODE(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.ID2
AND A.BLOCK > 0 --BLOCK THE OTHER SQL
AND B.REQUEST > 0
AND ((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.OWNER
WHERE 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
WHERE
PALLET_ID =1039928
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 26 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 3 26 1
Misses in library cache during parse: 1
Optimizer 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" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.75 0.74 70540 78205 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.75 0.74 70540 78205 0 1
Misses in library cache during parse: 0
Optimizer 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" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.69 0.67 70528 78205 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.69 0.68 70528 78205 0 1
Misses in library cache during parse: 0
Optimizer 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" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 3.30 3.23 296991 297868 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 3.30 3.23 296991 297868 0 1
Misses in library cache during parse: 0
Optimizer 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" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.94 2.88 296991 297868 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.94 2.88 296991 297868 0 1
Misses in library cache during parse: 0
Optimizer 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)
********************************************************************************
begin
sys.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 0
Execute 4 0.00 0.00 0 0 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 0 0 4
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5
时间： 2024-04-19 11:03:13