Re: Pinning Packages Not Yet Loaded

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.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.