Re: Qualitative method for performance effect of Oracle triggers

Robert Roh wrote:
>
> We are studying the use of triggers on a system that expects 100,000
> records over a 10 hours period.
>
> Is there any way to measure the performance effect in a qualitative
> formulaic way as opposed to knowing that there will be some
> performance effect which you won't know until you test it
>

There are several (actually a ridiculously large number) of factors that might impact the performance of a specific trigger. To name a few:

• what kind of trigger is it?
• - (insert/update/delete ..., before or after, etc)
• is it invoked per row
• are there any loops
• are there any stored procedures
• are there any secondary table interactions (lookups, inserts, audits, etc)
• what kind of data is it,
• are the fragmentation/coalesce/chaingin issues possible
• what the h(*% are the triggers doing
• disk/operating system interactions
• are there other operations happening in the database at the same time

I wouldn't even bother trying to estimate, 'cause I couldn't get enough granularity on the possible factors. As Daniel says, use the DBMS_PROFILER At 100,000 rows/10 hours (10,000 / hour = 170 / minute or 2-3 per second ) I wouldn't be too concerned unless the underlying system is severly undersized in the first place. (Actually I wouldnt be concerned until about 250 rows/second and beyond that, just be watchful and prepared to monitor and possibly tune like bejasis.)

There are other, far worse, concerns. Like archiving, B&R, maintenance windows, disk layouts (eg: striping) etc.

Of course, idiocy like putting application code (including the code for this one, or multiple instances, on the same machine should be avoided - simply can't be tuned properly and conflicts are outside of direct control.

/Hans Received on Sat Nov 15 2003 - 20:08:05 CST