开发反馈删除一条记录特别慢，需要8分钟，太慢了：
delete from testtab ra where ra.id=111000001000000629990661 and ra.spec_id=2210100001;

这边第一反应，是看执行计划是否正常，难道大表走全表扫描了？

查看表记录和大小正常。
SQL> select count(*) from TESTTAB;
 
  COUNT(*)
----------
    131065
 
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TESTTAB' and owner='ADMIN';
 
SUM(BYTES)/1024/1024
--------------------
                  40

直接select * from TESTTAB ra where ra.id=111000001000000629990661 and ra.spec_id=2210100001;速度很快，0.001秒就出来了，必然是走索引了。
SQL> explain plan for select * from TESTTAB ra where ra.id=111000001000000629990661 and ra.spec_id=2210100001;
 
Explained
 
SQL> select * from table(DBMS_XPLAN.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ERROR: an uncaught error in function display has happened; please contact Oracle
       Please provide also a DMP file of the used plan table PLAN_TABLE
       ORA-00997: illegal use of LONG datatype
发现竟然无法显示执行计划，并且报的信息也很奇怪，检查表结构desc testtab并没有long类型字段。先不管它。

另外检查正常查询的语句和是否有死锁问题，也排除怀疑：

查看是否死锁：(无）
SELECT a.sid,d.spid,b.status,e.event,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE and B.sid=E.sid 
AND B.PADDR=D.ADDR and A.sid in(select sid from v$Lock where block=1);

正在跑的语句：(无异常)
SELECT distinct d.spid,a.sid,e.event,b.status,c.sql_id,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE and B.sid=E.sid 
AND B.PADDR=D.ADDR 
and b.status='ACTIVE'
order by sql_text;

于是考虑10046跟踪看到底过程干啥了
[oracle@test ~]$ sqlplus jyc/xxx

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 15 15:23:36 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set time on 
15:23:45 SQL> set timing on
15:23:49 SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.00
15:23:54 SQL> delete from testtab ra where ra.id=111000001000000629990661 and ra.spec_id=2210100001;
    
1 row deleted.

Elapsed: 00:07:44.06

15:33:07 SQL> rollback;

Commit complete.

Elapsed: 00:00:00.01
15:33:08 SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.01
15:33:17 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@test ~]$ pwd
/home/oracle
[oracle@test ~]$ cd $ORACLE_BASE/rdbms/zyzh/zyzh/trace
[oracle@test trace]$ ls -lt|head
3232
-rw-r----- 1 oracle oinstall  3600901 915 15:33 zyzh_mmon_15256.trc
-rw-r----- 1 oracle oinstall   388143 915 15:33 zyzh_mmon_15256.trm
-rw-r----- 1 oracle oinstall 22753379 915 15:33 zyzh_ora_76181.trc
-rw-r----- 1 oracle oinstall  1211963 915 15:33 zyzh_ora_76181.trm

获取zyzh_ora_76181.trc后，发现删除过程中，会先select大量大表，所以导致删除记录很慢。
grep " select " zyzh_ora_76181.trc > zyzh.txt 

在zyzh.txt可以看到涉及外键的表。

下面源和目标端查询所有外键表和名称，对比目标外键信息后删除无用外键问题解决。
select table_name, constraint_name from user_constraints where constraint_type = 'R' order by table_name asc;