Re: PL/SQL procedures and packages

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/05
Message-ID: <34b14fbe.908126_at_www.sigov.si>#1/1


On Mon, 05 Jan 1998 10:42:47 -0800, Avinash Deshpande <avdeshpa_at_cisco.com> wrote:

>Hi,
>That was good advice from Stevens but I am sure that won't help u with
>the work.
>The PL/SQL compiled version stays in the memory when u compile the same.
>Then even if u delete u'r source file the compiled version will stay
>put.
>So enjoy.
>Always use CREATE OR REPLACE ...when u create procedures..it drops the
>compiled version & re-creates the procedure.
>
>Avinash
>
>Amoda Kulkarni wrote:
>>
>> Hi All,
>> How do I make PL/SQL procedures and functions stay in memory?
>>
>> Thanks in Advance
>> Amod

I belive by expression "stay in memory" Amod ment "stay in RAM", more precicely "stay in shared pool of the SGA", not how to store them in data dictionary, i.e "save on disk" (nor how to keep them in one's head ;-))).

The procedure is called "pinnig PL/SQL objects in shared pool". For this you must use supplied package DBMS_SHARED_POOLL. If it is not instaled run the scripts "dbmspool.sql" and "prvtpool.sql". The procedure goes like this:

  1. Load the wanted object into the shared pool by referencing it (execute standalone/packaged procedure or function, execute SQL that fires the desired trigger, ...).
  2. Execute DBMS_SHARED_POOL.KEEP(name_of_the_object)

That way the object is pinned into memory and will not be aged out by the LRU mechanism.

Regards,


Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================
The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Jan 05 1998 - 00:00:00 CET

Original text of this message