| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger presence KILLS performance
Thomas Kyte wrote:
>
> In article <3CB6F27C.4C0EBB30_at_oracle.com>, Uday says...
> >
> >Hi Thierry,
> >
> >How is your trigger defined, I mean, at statement level or at row level. If at
> >statement level, trigger fires only once per delete stmt. If at row level,
> >trigger fires for each and every row deleted. This is the overhead of firing the
> >trigger.
> >
> >It seems your trigger is at row level. Do you need to fire trigger code for each
> >row? If not, then create a statement level trigger.
> >
> >good luck,
> >Uday
> >
>
> been digging into this. seems to be an issue.
>
> if you do the following:
>
> drop table t;
> create table t as select * from all_objects;
> create index t_idx on t(owner);
> alter session set sql_trace=true;
> delete from t t_with_no_trigger_1 where owner = 'SYS';
> rollback;
> create or replace trigger t_trigger
> before delete on t
> begin
> null;
> end;
> /
> delete from t t_with_trigger_1 where owner = 'SYS';
> rollback;
>
> You might find something like this:
>
> delete from t t_with_no_trigger_1
> where
> owner = 'SYS'
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 1 0.00 0.00 0 0 0 0
> Execute 1 0.79 5.29 153 21 9725 8957
> Fetch 0 0.00 0.00 0 0 0 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 2 0.79 5.29 153 21 9725 8957
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 216
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 DELETE T
> 8958 INDEX RANGE SCAN (object id 30086)
>
> delete from t t_with_trigger_1
> where
> owner = 'SYS'
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 1 0.00 0.00 0 0 0 0
> Execute 1 1.38 7.73 37 21 27713 8957
> Fetch 0 0.00 0.00 0 0 0 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 2 1.38 7.73 37 21 27713 8957
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 216
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 DELETE T
> 8958 INDEX RANGE SCAN (object id 30086)
>
> Notice the current mode blocks -- 27,713 vs 9,725. If we do the math:
>
> (27713-9725)/8957
>
> that's just about 2. Every index will contribute 2 consistent gets during the
> delete for each row deleted.
>
> (if we index some other column, NOT owner, you will not observe this behavior,
> both deletes will have the same number of consistent gets, you must be doing an
> index range scan on the delete itself).
>
> I believe we've identified the issue and it doesn't happen in 816 and before and
> *shouldn't* happen in 8173 and above -- i have to patch my testbed to see that
> it's fixed in 8173 as I think..... Unless someone out there with 8173 wants to
> test for us?
>
> >Thomas Kyte wrote:
> >
> >> In article <Anet8.11543$Ze.2312_at_afrodite.telenet-ops.be>, "Thierry says...
> >> >
> >> >Hi all,
> >> >
> >> >I just ran into a performance problem when using triggers.
> >> >The situation is this:
> >> >I have a table containing a few million records.
> >> >deleting from this table takes x sec. depending on the number of rows.
> >> >After adding a simple "before delete STATEMENT level trigger" my deletes now
> >> >roughly take 2x sec. to complete.
> >> >I've run a few tests and it's definitely the presence of an enabled trigger
> >> >that causes the performance drop.
> >> >even a trigger that doesn't do anything (BEGIN NULL; END;) causes the
> >> >performance drop.
> >> >
> >> >Does anyone have an explanation why this is so and more importantly, is
> >> >there a way around this?
> >> >
> >> >Ex: Delete 10000 recs takes about 8 sec. When set the trigger it takes 16
> >> >sac.
> >> >
> >> >
> >> >Thanks.
> >> >
> >> >Thierry
> >> >
> >> >(Oracle Version 8.1.7.0.0)
> >> >
> >> >
> >> >
> >>
> >>calling the plsql from sql -- doing the context switch back and forth and back
> >> and forth -- adds overhead.
> >>
> >>In an oltp system where you typically update a row, insert a row, delete a row--
> >> the overhead to the end user (instead of taking 0.001 seconds, it takes 0.002
> >> seconds) is not noticable.
> >>
> >> In a large bulk operation -- everything is magnified.
> >>
> >> --
> >> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> >> Expert one on one Oracle, programming techniques and solutions for Oracle.
> >> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> >> Opinions are mine and do not necessarily reflect those of Oracle Corp
> >
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Results from 8.1.7.1, 8.1.7.3, 9.0.1.2 and 9.0.1.3
BANNER
delete from t t_with_no_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.01 0.01 0 0 0 0 Execute 2 1.50 2.31 202 29 13893 12803 Fetch 0 0.00 0.00 0 0 0 0
total 3 1.51 2.32 202 29 13893 12803
delete from t t_with_before_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.02 0.02 0 0 0 0 Execute 1 2.17 3.06 114 29 39604 12803 Fetch 0 0.00 0.00 0 0 0 0
total 2 2.19 3.08 114 29 39604 12803
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
PL/SQL Release 8.1.7.3.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.3.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
delete from t t_with_no_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.02 0.02 0 0 0 0 Execute 1 1.40 2.59 245 29 14006 12907 Fetch 0 0.00 0.00 0 0 0 0
total 2 1.42 2.61 245 29 14006 12907
delete from t t_with_before_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.01 0.01 0 0 0 0 Execute 1 2.35 3.00 136 29 39923 12907 Fetch 0 0.00 0.00 0 0 0 0
total 2 2.36 3.01 136 29 39923 12907
BANNER
delete from t t_with_no_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0 Execute 1 2.27 3.13 86 34 16704 15358 Fetch 0 0.00 0.00 0 0 0 0
total 2 2.27 3.13 86 34 16704 15358
delete from t t_with_before_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.01 0.01 0 0 0 0 Execute 1 3.93 4.40 27 34 47534 15358 Fetch 0 0.00 0.00 0 0 0 0
total 2 3.94 4.41 27 34 47534 15358
Oracle9i Enterprise Edition Release 9.0.1.3.0 - Production
PL/SQL Release 9.0.1.3.0 - Production
CORE 9.0.1.2.0 Production
TNS for Solaris: Version 9.0.1.3.0 - Production
NLSRTL Version 9.0.1.3.0 - Production
delete from t t_with_no_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.00 0.02 0 0 0 0 Execute 1 1.77 2.43 440 37 16433 15162 Fetch 0 0.00 0.00 0 0 0 0
total 2 1.77 2.45 440 37 16433 15162
delete from t t_with_before_trigger
where
owner = 'SYS'
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0 Execute 1 2.84 3.42 0 38 46903 15162 Fetch 0 0.00 0.00 0 0 0 0
total 2 2.84 3.42 0 38 46903 15162
Cheers
Connor
-- ============================== Connor McDonald http://www.oracledba.co.uk "Some days you're the pigeon, some days you're the statue..."Received on Sat Apr 13 2002 - 11:48:19 CDT
 |
 |