RE: pinning objects

John

Thanks for an excellant and thorough reply. Given the quality of the reply I would be interested in seeing the rest of your document if that is possible?

Cheers

John

> -----Original Message-----
> From: Hallas John [SMTP:John.Hallas_at_btcellnet.net]
> Sent: 11 January 2002 14:11
> To: Multiple recipients of list ORACLE-L
> Subject: RE: pinning objects
>
> I wrote a database tuning document a while ago and the notes below are
> from the section on pinning. I do recall that the final comment re the
> difference betwen pinning and keeping was from a Steve Adams response to a
> question on this list.
>
> The library cache forms part of the shared pool area. An important part of
> managing the library cache efficiently is to ensure that any frequently
> used packages are kept in the shared pool and not aged out. Oracle
> provides the dbms_shared_pool package to keep (or pin) packages,
> procedures, triggers and cursors. The object is never flushed out of
> memory until either an instance shutdown or it is explicitly unpinned.
> Objects that are accessed frequently are the best candidates for pinning.
> Pin packages as user SYS.
>
> It is recommended that the following packages are pinned in memory at
> instance startup.
>
> SYS.STANDARD
> SYS.DBMS_STANDARD
> SYS.DBMS_DESCRIBE
> SYS.DBMS_UTILITY
> SYS.DBMS_LOCK
> SYS.DBMS_PIPE
> SYS.DBMS_OUTPUT
>
> The syntax to pin and then unpin a package is
>
> EXECUTE DBMS_SHARED_POOL.KEEP ('sys.dbms_output');
> EXECUTE DBMS_SHARED_POOL.UNKEEP ('sys.dbms_output');
>
> 1) Note that when the shared pool is flushed (ALTER SYSTEM FLUSH
> SHARED_POOL) kept packages are NOT flushed out.
> 2) Note that pinning and keeping are technically not the same. In the
> library cache a pin is a lock held by a particular session on one or more
> heaps of an object. With regard to the shared pool, a pin is a bitmap in
> the header of a recreatable chunk indicating whether it is eligible to be
> aged out or flushed from the shared pool. Library cache object heaps that
> are marked for keeping may not be pinned in either sense.
>
> A script to identify suitable objects is :-
>
> Col owner format a10
> Col name format a25
> Set lines 200
> Rem
> rem Look for high number of loads
> rem
> select owner,name,loads,executions,kept
> from v$db_object_cache
> where type like 'PACK%'
> and loads > 1
> order by loads
> /
>
>
> HTH
>
> John
>
>
>
> -----Original Message-----
> From: John Dunn [ <mailto:john.dunn_at_sefas.co.uk>]
> Sent: 11 January 2002 09:40
> To: Multiple recipients of list ORACLE-L
> Subject: pinning objects
>
>
> I am investigating pinning some of the developers packages into memory.
>
> Could you please clarify how this is done.
>
> Also how can I identify which packages would beefit from pinning?
>
> John
>
> --
> Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
> --
> Author: John Dunn
> INET: john.dunn_at_sefas.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> =========================================================
> This electronic message contains information from the mmO2 plc Group
> which may be privileged or confidential. The information is intended to be
>
> for the use of the individual(s) or entity named above. If you are not the
>
> intended recipient be aware that any disclosure, copying, distribution or
> use of the contents of this information is prohibited. If you have
> received
> this electronic message in error, please notify us by telephone or email
> (to the numbers or address above) immediately.
> =========================================================
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).