Re: Triggers - How expensive when set on heavily loaded tables ?

From: <Brian_P_MacLean_at_eFunds.Com>
Date: Wed, 23 Jul 2003 22:46:20 -0700
Message-Id: <25998.339304@fatcity.com>

I did some trigger timings about a year ago on v8.1.7.4. I did the testing using a trigger with only 1 statement in it, and that statements was "null;". I found that my transaction timing increased by 100% for the first trigger added, and 25% for every trigger after that. So that math breaks down this way:

      The base transaction took 60 seconds;
      With one before insert trigger with a "null;" statement in it, it now
took 120 seconds;
      I added one more after insert trigger with a "null" statement in it,

the total time was 150 seconds;

Kind of makes ya rethink/realize what the true cost of all those before insert/update triggers we have added over the years, that do nothing more than update the add_date and change_date columns. I know I'll never do it again.

Now in your case, maybe the trigger is the right answer. But we can't make that decision for you.....YOU HAVE TO TEST IT FOR YOURSELF!

                                                                                                                                 
                      "VIVEK_SHARMA"                                                                                             
                      <VIVEK_SHARMA_at_inf        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>             
                      osys.com>                cc:                                                                               
                      Sent by:                 Subject:  Triggers - How expensive when set on heavily loaded tables ?            
                      ml-errors_at_fatcity                                                                                          
                      .com                                                                                                       
                                                                                                                                 
                                                                                                                                 
                      07/23/2003 09:14                                                                                           
                      PM                                                                                                         
                      Please respond to                                                                                          
                      ORACLE-L

Qs 1) Whenever an Account ID is transacted - INSERT occurs into a table TRAN_TBL as part of the transaction , Additionally the Account ID is checked for existence in another table say TABLE_1 . If found , a record is inserted into yet another table say TABLE_2 .

Qs. Operations involving TABLE_1 & TABLE_2 if managed using triggers , How expensive in CPU & performance will it be ?

NOTE - TRAN_TBL undergoes very large volumes of Concurrent OLTP INSERT transaction .

Qs 2) If an alert is to be raised on addition of a new record to TABLE_2 :- a) Can trigger be used ?
b) Should a cron job running every 5 min. look at TABLE_2 & based on the time criteria generates the alert ?

Thanks

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: VIVEK_SHARMA
INET: VIVEK_SHARMA_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Thu Jul 24 2003 - 00:46:20 CDT