Oracle-L: RE: Triggers - How expensive when set on heavily loaded tables ?

From: Gorbounov,Vadim <vadim.gorbounov_at_liberate.com>
Date: Wed, 30 Jul 2003 06:49:24 -0800
Message-ID: <F001.005C7D4E.20030730064924@fatcity.com>

Hi Vivek,

I'll try to share my experience.
Impact of FOR EACH ROW trigger may be quite essential on heavily loaded tables, even if a trigger is just an empty body. This is the case when application uses array (batch in java) DML. Larger arrays (like 100) are impacted more. Number of LIO may be few (in one of the recent cases - 3) times larger. This is due to Oracle cannot optimize array DML IO once trigger is there. Such applications do not scale well. Impact is much less noticeable when no batch is employed.

HTH
Vadim

-----Original Message-----
Sent: Wednesday, July 30, 2003 4:24 AM
To: Multiple recipients of list ORACLE-L ?

CASE - If Only 1 record is found in TABLE_1 (on the average) for 8 records INSERTED into TRAN_TBL

Qs Is the Overhead of Checking the condition by the trigger also a significant portion OTHER than the actual execution by the trigger of INSERT into TABLE_2 on finding a match in TABLE_1? Any percentage proportion ratios of Checking Overhead TO execution ( INSERT ) overhead ?

Any Docs , Links on such ?

Thanks

-----Original Message-----
Sent: Thursday, July 24, 2003 8:42 AM
To: 'oracledba_at_lazydba.com'; 'ORACLE-L_at_fatcity.com'

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).
--

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

Author: Gorbounov,Vadim
INET: vadim.gorbounov_at_liberate.com

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