Re: Trigger presence KILLS performance

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

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
Received on Fri Apr 12 2002 - 09:43:08 CDT