Re: Trigger presence KILLS performance

Hallo Uday,

It is a STATEMENT level Trigger.

Thierry

"Uday Moogala" <umoogala_at_oracle.com> wrote in message news:3CB6F27C.4C0EBB30_at_oracle.com...
> 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 Mon Apr 15 2002 - 03:58:17 CDT