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.

=========================================================