Oracle触发器问题解决一例

例行检查数据库AWR报告,有一条update语句执行多次,每次执行时间30多秒,这条SQL语句很简单,就是根据主键条件修改数据,主键个数是1到100之间。这个问题由来已久,只是偶尔出现。主键是varchar2,类似序列,由于之前有迁移过数据,特别在主键上为迁移的这部分数据加过标记,用肉眼看主键的分布是不均匀的。

第一次诊断:这个表有150万的数据,执行慢是因为update的时候没走到主键索引,于是去看了下直方图的分布,只有两个桶,于是重新收集了主键的直方图信息,有250个桶了。准备观察一天,第二天再看AWR,发现反而越来越慢了。

第二次诊断:听开发人员说此表上有触发器,测试发现果然是触发器的问题,触发器消耗的资源统统记在update语句上,让人感到莫名其妙。修改方法是将触发器的业务通过SQL实现,整个功能快了不少。下面对问题进行抽象、实验:

1.初始化数据及建立触发器

drop table test1 purge;

drop table test2 purge;
create table test1 as select * from dba_objects;
insert into test1 select  * from dba_objects;
commit;
create table test2 as select * from dba_objects;
create index ind_t1_object_id on test1(object_id) nologging;
create index ind_t2_object_id on test2(object_id) nologging;
exec dbms_stats.gather_table_stats(user,'test1',cascade => true);

exec dbms_stats.gather_table_stats(user,'test2',cascade => true);

CREATE OR REPLACE TRIGGER t_trigger
  BEFORE update ON test1
  FOR EACH ROW
BEGIN
  update test2 t
    set t.object_name = :old.object_name
  where t.object_id = :old.object_id;
END;


SQL> set autotrace traceonly
SQL> set timing on

2.执行update语句会触发触发器
SQL> update test1 set object_name=''||object_name;
已更新140300行。
已用时间:  00: 00: 15.21
执行计划
----------------------------------------------------------
Plan hash value: 160929213
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
|  0 | UPDATE STATEMENT  |      |  140K|  4110K|  384  (1)| 00:00:06 |
|  1 |  UPDATE            | TEST1 |      |      |            |          |
|  2 |  TABLE ACCESS FULL| TEST1 |  140K|  4110K|  384  (1)| 00:00:06 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
    140739  recursive calls
    427013  db block gets
    282079  consistent gets
          0  physical reads
  120365752  redo size
        718  bytes sent via SQL*Net to client
        498  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    140300  rows processed
SQL> commit;
提交完成。


3.disable触发器
SQL> alter trigger t_trigger disable;

4.执行update语句不会触发触发器
SQL> update test1 set object_name=''||object_name;
已更新140300行。
已用时间:  00: 00: 01.67
执行计划
----------------------------------------------------------
Plan hash value: 160929213
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
|  0 | UPDATE STATEMENT  |      |  140K|  3425K|  384  (1)| 00:00:06 |
|  1 |  UPDATE            | TEST1 |      |      |            |          |
|  2 |  TABLE ACCESS FULL| TEST1 |  140K|  3425K|  384  (1)| 00:00:06 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
        389  recursive calls
    144840  db block gets
      2216  consistent gets
          0  physical reads
  50003740  redo size
        721  bytes sent via SQL*Net to client
        498  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
    140300  rows processed

总结:通过两次实验可以看到资源消耗差别非常大,触发器消耗的资源都算在update上。触发器是每行触发,如果要高效,处理得有批量的思想。本次问题的解决,如果不是开发人员告诉我有触发器,这个问题真的很难找出来。

相关推荐