ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 外键列索引缺失的潜在性能问题一例

外键列索引缺失的潜在性能问题一例

原创 Linux操作系统 作者:realkid4 时间:2012-05-20 23:13:25 0 删除 编辑

 

在实际开发过程中,外键约束帮助我们维护数据库层面完整性。虽然很多新开发的系统将这部分验证转移到应用系统层面进行,但是无论从执行效率还是代码可靠性上看,适度的外键约束设置是有益无害的。

 

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博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-730338/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7545876