被Oracle觸發器給坑了，Oracle觸發器

被Oracle觸發器給坑了，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上。觸發器是每行觸發，如果要高效，處理得有批量的思想。本次問題的解決，如果不是開發人員告訴我有觸發器，這個問題真的很難找出來。