在实际开发过程中，外键约束帮助我们维护数据库层面完整性。虽然很多新开发的系统将这部分验证转移到应用系统层面进行，但是无论从执行效率还是代码可靠性上看，适度的外键约束设置是有益无害的。
Oracle数据库中，外键列使用最常出现的错误就是外键列没有添加索引。在笔者过去的文章中，反复针对这个问题进行过不同层面探讨。其中最常见的一个问题就是由于索引缺失导致的死锁现象。
在外键列没有索引的情况下，我们对主子表的操作会带来更大范围的锁定。当系统存在并发的情况下，就会出现数据库层面的死锁现象。今天本文所要阐述的是由于没有索引列导致的删除主表数据操作低效问题。
1、环境准备
本文的现象来自真实案例，笔者进行简单的模拟。这是使用Oracle 11gR2进行试验。
SQL> select * from v$version;
BANNER
---------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
下面我们构建主子表关系，建立一个父表对应一个子表的结构。
SQL> create table t_master as select * from dba_objects where object_id is not null;
Table created
SQL> alter table t_master add constraint pk_t_master primary key (object_id);
Table altered
SQL> select count(*) from t_master;
COUNT(*)
----------
72571
SQL> create table t_detail as select * from dba_objects where object_id is not null;
Table created
--构建外键
SQL> alter table t_detail add constraint fk_detail_master foreign key (object_id) references t_master(object_id);
Table altered
--收集统计量
SQL> exec dbms_stats.gather_table_stats(user,'T_DETAIL',cascade => true);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user,'T_MASTER',cascade => true);
PL/SQL procedure successfully completed
2、问题场景
注意，此时我们没有为外键列增加索引。删除子表记录。
SQL> truncate table t_detail;
Table truncated
SQL> truncate table t_master;
truncate table t_master
ORA-02266: 表中的唯一/主键被启用的外键引用
对外键关系的主子表而言，主表的truncate操作时不被允许的。所以，只能使用delete操作进行，但是我们发现这个delete操作性能低下。
SQL> delete t_master;
72571 rows deleted
Executed in 18.781 seconds
在真实案例中，如果一个主表对应四张子表的时候，删除操作根本无法完成，验证影响性能。
3、问题分析
按照问题的思路，首先确定其他数据库操作没有问题的情况下，只有这个delete操作存在性能问题。此时，我们直观的想法是想知道当delete的时候，Oracle是不是在等待什么，是不是有锁定？
SQL> select sid from v$mystat where rownum<2;
SID
----------
15
SQL> select object_id, object_name from dba_objects where object_name in ('T_MASTER','T_DETAIL');
OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------------
75521 T_DETAIL
75519 T_MASTER
Executed in 0.141 seconds
检查删除过程中v$session视图的等待事件。
SQL> select sid, event, status from v$session where sid=15;
SID EVENT STATUS
---------- ---------------------------------------- --------
15 db file sequential read ACTIVE
SQL> delete t_master;
已删除72571行。
已用时间: 00: 00: 18.48
--锁状态
SQL> select * from v$lock where sid=15;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
37E874F0 37E8751C 15 AE 100 0 4 0 1069 0
37E87A60 37E87A8C 15 TO 65908 1 3 0 1048 0
B7C34FB0 B7C34FE0 15 TM 75519 0 3 0 2 0
3778A68C 3778A6CC 15 TX 327711 1397 6 0 2 0
我们发现此时再进行db file sequential read，该事件最常见的动作是在进行索引路径执行计划。那么，是不是执行计划出了问题。为了更进一步看到细节，我们使用autotrace的统计量功能。
SQL> delete t_master;
已删除72571行。
已用时间: 00: 00: 18.48
执行计划
----------------------------------------------------------
Plan hash value: 940845719
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 72571 | 354K| 152 (0)| 00:00:02
| 1 | DELETE | T_MASTER | | | |
| 2 | INDEX FULL SCAN| PK_T_MASTER | 72571 | 354K| 152 (0)| 00:00:02
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
217889 recursive calls
371175 db block gets
217941 consistent gets
33 physical reads
43237552 redo size
720 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
72571 rows processed
执行计划本身没有什么问题，值得关注的是大量的recursive call次数，达到217889。recursive call是Oracle内部执行的语句，一个delete操作要如此多次数的操作，比较可疑。
可以继续查看一下这些recursive call究竟是什么，Oracle在进行delete操作的时候，连带有什么动作？此处，可以使用10046事件跟踪。
--11g中跟踪文件位置；
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
---------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_5881.trc
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> delete t_master;
已删除72571行。
SQL> alter session set events '10046 trace name context off';
会话已更改。
生成的trace文件较大（40M），再使用tkprof进行分析。
D:\>tkprof wilson_ora_5881.trc res.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期日 5月 20 02:05:11 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
在res.txt结果信息中，一段代码吸引注目。
select /*+ all_rows */ count(1)
from
"SYS"."T_DETAIL" where "OBJECT_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 72570 16.24 19.12 0 0 0 0
Fetch 72570 3.62 4.31 0 217710 0 72570
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 145141 19.86 23.43 0 217710 0 72570
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=3 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T_DETAIL (cr=3 pr=0 pw=0 time=0 us cost=562 size=10 card=2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: shared pool 3 0.00 0.00
********************************************************************************
注意，上面信息显示，在delete父表的时候，Oracle连带的检查了子表，次数为主表的记录数（72570）。而每次的执行计划，还是全表扫描。这就是问题的所在！
从上面的信息看，当没有子表索引列的时候，Oracle父表要删除每一条记录，都需要访问子表，以确定没有对应子表的参照记录。而且，没有子表索引的前提下，执行计划效率低下。
进一步考虑，如果子表数目个数超过一定数目，delete操作带来的连带操作会更多，就会出现执行不下去的情况。
那么，我们添加了索引，问题就会不同了。
4、问题解决
我们添加索引对象。
--添加索引
SQL> create index idx_t_detail on t_detail(object_id);
Index created
Executed in 0.25 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T_DETAIL',cascade => true);
PL/SQL procedure successfully completed
Executed in 0.906 seconds
再使用相同的方法进行检验。
SQL> delete t_master;
已删除72571行。
已用时间: 00: 00: 11.00
执行计划
----------------------------------------------------------
Plan hash value: 940845719
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 72571 | 354K| 152 (0)| 00:00:02 |
| 1 | DELETE | T_MASTER | | | | |
| 2 | INDEX FULL SCAN| PK_T_MASTER | 72571 | 354K| 152 (0)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
269 recursive calls
298487 db block gets
214 consistent gets
4 physical reads
43232632 redo size
719 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
72571 rows processed
注意到，连带的recursive call次数减少到269，联动的逻辑物理读次数也降低了。总的执行时间也消耗少了。我们再使用10046事件进行监控。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
-----------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_5974.trc
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> delete t_master;
已删除72571行。
SQL> alter session set events '10046 trace name context off';
会话已更改。
--分析跟踪文件
D:\>tkprof wilson_ora_5974.trc res2.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期日 5月 20 02:17:12 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
生成的跟踪文件很小，而且其中大规模的子表查询动作也没有在结果文件中找到。问题的根源可以确定已经找到。
5、结论
Oracle中，我们添加主键之后，系统会自动的为我们添加主键索引对象。但是，外键就不会帮助我们建立索引。这个的确给我们开发带来一些问题。本文所描述的场景是一个不次于死锁场景的难题。如果你发现一个父表删除动作效率低下，可以考虑下时候是由于索引缺失的缘故。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17203031/viewspace-730338/ ，如需转载，请注明出处，否则将追究法律责任。
