Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Triggers - How expensive when set on heavily loaded tables ?

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

From: <jkstill_at_cybcon.com>
Date: Thu, 24 Jul 2003 20:14:16 -0700 (PDT)
Message-Id: <26007.339500@fatcity.com>


Brian,

Were those row level or statement level triggers?

Jared

On Wed, 23 Jul 2003 Brian_P_MacLean_at_eFunds.Com wrote:

>
> 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
> also send the HELP command for other information (like subscribing).
Received on Thu Jul 24 2003 - 22:14:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US