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

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 30 Jul 2003 07:59:28 -0800
Message-ID: <F001.005C7D81.20030730075928@fatcity.com>

10046 level-8 trace will show you exactly how much response time is consumed by the recursive SQL executing the trigger(s) (as well as details about why the response time was what it was).

We have a 30-minute exercise in our Hotsos Clinic 101 in which you practice the act of interpreting exactly this kind of trace data.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic 101 in Denver, Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
VIVEK_SHARMA
Sent: Wednesday, July 30, 2003 3: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: Cary Millsap
INET: cary.millsap_at_hotsos.com

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