触发器引起DML性能大降亦可用10046捕获元凶SQL

场景：一些触发器的滥用，造成SQL的DML性能大降，通过10046亦可捕获其中耗能SQL测试：--创建等待插入表T1SQL> create table t1 as select  object_id,object_name from dba_objects where 1=2;Table created.--创建触发器遍历的表T2SQL> create table t2 (  2  n1 number,  3  n2 number);Table created.--创建SEQ01,SEQ02序列create sequence seq02 start with 100000Sequence created.SQL> drop sequence seq01;Sequence dropped.SQL> create sequence seq01 start with 1;create sequence seq02 start with 100000;Sequence created.--插入到T2，构造10万行量SQL> SQL> SQL> SQL> begin   2  for i in 1..100000 loop  3  insert into t2 select seq01.nextval,seq02.nextval from dual;  4  end loop;  5  commit;  6  end;  7  /PL/SQL procedure successfully completed.--创建索引，避免遍历全表扫描SQL> create index idx_t2 on t2(n1,n2);Index created.--在待插入表T1上创建插入触发器SQL> create or replace trigger trig_t1   2  before insert  on t1  3  for each row  4  declare  5  v_count number;  6  begin  7  if inserting then  8  select count(1) into v_count from t2 where :new.object_id between n1 and n2;  9  if v_count=0 then 10  :new.object_id:=1111; 11  end if; 12  end if; 13  end; 14  /Trigger created.--查看当前的SID=33,SPID=2791QL> select * from v$mystat where rownum=1;       SID STATISTIC#      VALUE---------- ---------- ----------        33          0          0SQL> select spid from v$process where addr in (select paddr from v$session where sid=33);SPID------------------------2791--10046开始跟踪SQL> alter session set events '10046 trace name context forever,level 12';Session altered.--长时间运行好几分钟后人工被迫中断SQL> insert into t1 select object_id,object_name from dba_objects;insert into t1 select object_id,object_name from dba_objects            *ERROR at line 1:ORA-01013: user requested cancel of current operationORA-06512: at "AIKI.TRIG_T1", line 5ORA-04088: error during execution of trigger 'AIKI.TRIG_T1'SQL>  alter session set events '10046 trace name context off';Session altered.--tkprof格式化命令：按照最耗能SQL从大到小的顺序，过序系统信息[oracle@host11gr3 trace]$ tkprof   learning_ora_2791.trc  02.txt sys=no sort=prsela,exeela,fchelaTKPROF: Release 11.2.0.3.0 - Development on Tue May 6 21:49:43 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.[oracle@host11gr3 trace]$ more 02.txtTKPROF: Release 11.2.0.3.0 - Development on Tue May 6 21:49:43 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Trace file: learning_ora_2791.trcSort options: prsela  exeela  fchela  ********************************************************************************count    = number of times OCI procedure was executedcpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executingdisk     = number of physical reads of buffers from diskquery    = number of buffers gotten for consistent readcurrent  = number of buffers gotten in current mode (usually for update)rows     = number of rows processed by the fetch or execute call********************************************************************************--可以看到此条SQL，即为我们要找的触发器中耗能SQL元凶！SQL ID: 69jfghdwc8c1n Plan Hash: 4191549303SELECT COUNT(1) FROM T2 WHERE :B1 BETWEEN N1 AND N2call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute  39209      4.40       4.63          0          0          0           0Fetch    39209    352.13     355.49        223    2292086          0       39208------- ------  -------- ---------- ---------- ---------- ----------  ----------total    78418    356.54     360.12        223    2292086          0       39208Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 90     (recursive depth: 1)Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------         1          1          1  SORT AGGREGATE (cr=2 pr=0 pw=0 time=92 us)        20         20         20   INDEX RANGE SCAN IDX_T2 (cr=2 pr=0 pw=0 time=38 us cost=2 size=2002 card=77)(object id 77026)        --使用触发器需谨慎；