Re: Trigger Performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 27 Jan 1999 02:43:53 GMT
Message-ID: <36ae7d0c.1043630@192.86.155.100>

A copy of this was sent to Maxim A Sokolnikov <max_at_mpsb.moris.rosmail.com> (if that email address didn't require changing) On Mon, 25 Jan 1999 00:50:45 +0000, you wrote:

>TimKArnold wrote:
>
>> Hi,
>>
>
>Hi> I'd like to improve the performance of a
>
>> group of triggers. I tried dbms_shared_pool.keep ('my_trigger')
>> to pin the trigger to memory, but apparently it only
>> works for packages. If 'alter table my_table cache' that
>> helps.
>> Any suggestions?
>>
>
> Because of trigger compilation each time when it fiered, try to
>rewritetrigger body using stored procedures calls where it possible.
>

Just FYI:

• in 7.3 and up triggers are stored compiled
• in no version was it true that 'each time it fired' it was compiled. It is accurate to say "the first time it fires after the database is started up it will be compiled and if it gets aged out of the shared sql pool it will be compiled again". It very infrequently would have to recompile a frequently used trigger due to the LRU aging algorithms for the shared pool...

>Maxim.
>
>
>> TIA,
>> Tim
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jan 26 1999 - 20:43:53 CST