Re: Trigger presence KILLS performance

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