Re: triggers, sequence - need advice

Daud wrote:
>
> We have an application which was running fine until our vendor install
> a bunch of triggers on all tables to 'capture' data into an
> intermediate table. From that table they have another process which
> will read and xfer to another database (their own database...not
> oracle) for reporting and other stuff. Now we are seeing some
> performance problems with all these triggers around.
> I would like to find out if there is anything that can be done to
> improve (lets just say I simply can't remove all those triggers...but
> I can rewrite to make them more efficient).
> One each table, they have 3 triggers (after insert/update/delete) to
> basically to the same thing....capture data and save in intermediate
> table. Would I see any improvement by combining the three triggers
> into just 1? When inserting data into the intermediate table they are
> using an oracle sequence for sort of ordering (same sequence for
> insert/update/delete from all tables). Should I change and use time
> instead?
> Anything else I can look into...perhaps what is the better way of
> doing something like this?
> Thanks!

You could explore turning a row-at-a-time insert into an array insert with something like:

Original code:
 before insert for each row
   insert into tab values (:new.x);

New code:
  before insert for each row
    nested table type (idx) := :new.x;

  after insert statement level
    forall i in 1 .. nested table type.count

       insert into tab values (...)

if the incoming SQL's are all row-at-a-time anyway then you wont see any gain.

Another option is to move the trigger code into PL/SQL program units so that the underlying cursors are not soft parsed each time

hth
connor     

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"