Re: Opinions on trigger performance?

In article <mburkeCpDsyG.Kqz_at_netcom.com> Matthew J. Burke, mburke_at_netcom.com writes:
>I'm interested in hearing from anybody familiar with the performance

We have just recently implemented a Course Registration System that contains several database triggers.

Our experience with them is that they stink for certain applications. If all
one is doing is straight auditing, you should have no problem. Trigger impact
will be minimal.

HOWEVER.... If you are doing some PL/SQL within a trigger to read another table and take
some action depending on the results THEN you may have a significant impact. For example, on one of our tables, AC063, we had a trigger with the BEFORE UPDATE clause where we were accessing another table consisting of 40,000 rows. We were using the PK as the key. The TKPROF printout of the session showed that over 3 seconds of CPU time was expended in parsing the trigger EACH TIME it fired as well as over 3 seconds of CPU time to execute. When we removed the trigger, performance soared. Parse time went down to less than 1/100 of a second and execution fell to .01 seconds.

On three of our tables we are doing auditing with database triggers and have
not seen much of an impact. The bottom line is 1) use triggers, 2) run TKPROF and see what!s going on, and 3) have a backup plan in case the performance grinds to a halt.

I have a call into the Crew in Redwood Shores about this, but I am not holding my breath. Received on Fri May 06 1994 - 20:48:11 CEST