场景:一些触发器的滥用,造成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)
--使用触发器需谨慎;
触发器引起DML性能大降亦可用10046捕获元凶SQL
最新推荐文章于 2024-04-27 12:03:20 发布