Re: triggers, sequence - need advice

Daud wrote:
>
> Thank you for the input guys.
>
> Question for Norman.
> I have no problem pinning the triggers and I am just curious about the
> triggers being reloaded everytime they are fired. I thought if I dont
> pin them, they get reloaded only if they have been aged out.
> ------
>
> Question for Connor.
> I dont fully your statement below. I understand about soft and hard
> parses but not entirely sure abt your statement below. Could you pls
> explain a little bit more or point me to a good documentation.
>
> >> Another option is to move the trigger code into PL/SQL program
> units so
> >> that the underlying cursors are not soft parsed each time
> ------
>
> You are right abt the problem with the index. I am not sure what I can
> do since I dont think I can use reverse-key index with RBO. We are
> still stuck with RBO because the app has not been rewritten to support
> CBO yet....slow :(( Let me know if this is not a true statement. And I
> cannot prepend it a with a randam char because I need (well not me,
> the job that process the rows) to process the rows in order.
>
> thanks
> Daud
>
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3F2A3AE1.2E22_at_yahoo.com>...
> > 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

create a trigger on table T which does:
  select 1 from dual

and then run 'insert into T values' 10 times in SQL Plus

then replace your trigger with a call to proc P which does the select 1 from dual, and then run 'insert into T values' 10 times in SQL Plus

run each with sql_trace = true and check the tkprof file

hth
connor

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

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