Re: Pinning Packages Not Yet Loaded

I am able to pin all my large custom PL/SQL packages with an on startup trigger, and I don't have to load them into the SGA in order to do it. 8.1.5 AIX 4.3.2

• Dc.

On Fri, 30 Jun 2000 19:06:46 GMT, ddf_dba_at_my-deja.com wrote:

>In article <8jilvh$idu$1_at_nnrp1.deja.com>,
> Ethan Post <epost1_at_my-deja.com> wrote:
>> My assumption is that the dbms_shared_pool.keep procedure should load
>> it for you.
>>
>> -Ethan
>> http://www.freetechnicaltraining.com
>> http://www.gnumetrics.com
>>
>> In article <Gp475.4935$j7.261697_at_news.bc.tac.net>,
>> "Jake de Haan" <jake_dehaan_at_telus.net> wrote:
>> > Hi,
>> >
>> > I'm trying to figure out how best to pin a package in the shared
 pool
>> > that is not yet been executed. I don't really want to execute any
>> > function within the package (this is a application package) so the
>> > real question is "how do I load a package into the shared pool so
 that
>> > I can pin shortly after startup?"
>> >
>> > Jake.
>> >
>> >
>>
>> --
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>>
> |Ethan Post <epost1_at_my-deja.com> wrote:
> |My assumption is that the dbms_shared_pool.keep procedure should load
> |it for you.
>
>The assumption is in error ("When you assume ..."). The
>DBMS_SHARED_POOL.KEEP function will pin objects already present in the
>shared SQL area but will NOT load them; if the object is not in the
>shared SQL area of the SGA DBMS_SHARED_POOL.KEEP will error out.
>
>The easiest way to load a package/function/procedure without executing
>it is to recompile it:
>
>alter function|package|procedure <name> compile;
>
>You may now use DBMS_SHARED_POOL.KEEP to pin the procedure, function,
>package into the SGA:
>
>execute DBMS_SHARED_POOL.KEEP('<name>'. 'P');
>
>The 'P' indicates to the procedure that a package, function or procedure
>is to be kept. The acceptable values are:
>
> -- Value Kind of Object to keep
> -- ----- ----------------------
> -- P package/procedure/function
> -- Q sequence
> -- R trigger
> -- T type
> -- JS java source
> -- JC java class
> -- JR java resource
> -- C cursor
>
>Quite a variety of objects can be pinned into the SGA; it is best to pin
>objects at database startup so that contiguous blocks of memory are
>available for the object or objects.
>
>The largest packages/procedures should be pinned first; the following
>script will list the order in which packages/procedures should be
>pinned:
>
>select owner, name, type,
> source_size+parsed_size+code_size+error_size total_bytes
>from dba_object_size
>where type in ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
>order by 4 desc;
>
>Most functions won't need to be pinned as they are usually small. You
>will, however, find a number of packages and procedures that would
>benefit from being pinned in the SGA.
Received on Fri Jun 30 2000 - 00:00:00 CDT