Trigger presence KILLS performance

From: Thierry Van der Auwera <Thierry.Van.der.Auwera_at_StrandAssociates.com>
Date: Thu, 11 Apr 2002 11:24:16 GMT
Message-ID: <Anet8.11543$Ze.2312@afrodite.telenet-ops.be>

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) Received on Thu Apr 11 2002 - 06:24:16 CDT