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

场景：一些触发器的滥用，造成SQL的DML性能大降，通过10046亦可捕获其中耗能SQL
测试：
--创建等待插入表T1
SQL> create table t1 as select  object_id,object_name from dba_objects where 1=2;

Table created.
--创建触发器遍历的表T2
SQL> create table t2 (
  2  n1 number,
  3  n2 number);

Table created.



--创建SEQ01,SEQ02序列

create sequence seq02 start with 100000
Sequence 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=2791
QL> select * from v$mystat where rownum=1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        33          0          0

SQL> 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 operation
ORA-06512: at "AIKI.TRIG_T1", line 5
ORA-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,fchela

TKPROF: Release 11.2.0.3.0 - Development on Tue May 6 21:49:43 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle@host11gr3 trace]$ more 02.txt

TKPROF: Release 11.2.0.3.0 - Development on Tue May 6 21:49:43 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: learning_ora_2791.trc
Sort options: prsela  exeela  fchela  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = 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: 4191549303

SELECT COUNT(1) 
FROM
 T2 WHERE :B1 BETWEEN N1 AND N2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute  39209      4.40       4.63          0          0          0           0
Fetch    39209    352.13     355.49        223    2292086          0       39208
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    78418    356.54     360.12        223    2292086          0       39208

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (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)
        
--使用触发器需谨慎；