Oracle触发器问题解决一例

2020年8月14日 10:31:09Linux大全评论820 views阅读模式

例行检查数据库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