| 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
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
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
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 CorpReceived on Fri Apr 12 2002 - 20:09:27 CDT
 |
 |