Tuning Instance   «Prev  Next»
Lesson 3 Pinning Packages in the Shared Pool Library Cache
Objective Pin packages into the shared pool.
Pinning Memory Objects
Pinning (or memory fencing) is a procedure that tells a database that after the package is initially loaded into the shared pool, the package must always remain in memory. In order to pin a package, it must be marked as "unswappable." Oracle provides a procedure called dbms_shared_pool.keep to pin a package, and packages can be unpinned with dbms_shared_pool.unkeep.
Only packages can be pinned. Stored procedures cannot be pinned unless they are placed into a package.
The choice of whether to pin a package in memory is a function of the size of the object and the frequency of its use. Very large packages that are called frequently might benefit from pinning, but any difference might go unnoticed because the frequent calls to the procedure have kept it loaded into memory anyway. Therefore, because the object never pages out in the first place, pinning has no effect.
Also, the way procedures are grouped into packages can have some influence. Some Oracle DBAs identify high-impact procedures and group them into a single package, and then pin this package in the shared pool library cache.
I highly recommend that you store all SQL in packages if you have enough shared_pool memory. Storing SQL in packages has several benefits. It ensures that all SQL is uniform and reusable, and it makes it possible to pin the SQL packages.

Re-pinning packages
UNIX users might want to add code to their database startup script to ensure that the packages are re-pinned after each database startup, guaranteeing that all packages are re-pinned with each bounce of the box. A script might look like this:
su oracle -c "/usr/oracle/bin/svrmgrl /<<!
connect internal;
EXECUTE dbms_shared_pool.keep('DBMS_ALERT'); 
EXECUTE dbms_shared_pool.keep('DBMS_DDL'); 
EXECUTE dbms_shared_pool.keep('DBMS_DESCRIBE'); 
EXECUTE dbms_shared_pool.keep('DBMS_LOCK'); 
Running pin.sql when restarting
As a database administrator, you also need to remember to run pin.sql whenever restarting a database. This is done by reissuing the PIN command from inside SQL*DBA immediately after the database has been restarted.
Locating and pinning large stored objects
If you have large procedures or large anonymous PL/SQL blocks in your application, you may also want to put these into packages and pin them in the shared pool. You can determine what large stored objects are in the shared pool by selecting from the V$DB_OBJECT_CACHE fixed view. This will also tell you which objects have been marked kept. This can be done with the following query:
select * from V$db_object_cache where sharable_mem > 10000;
If you have plenty of free memory in the shared pool and you wish to mark all packages in the system "kept," you can execute the following PL/SQL snippet:
   own varchar2(100); 
   nam varchar2(100); 
cursor pkgs is 
   object_type = 'PACKAGE';
begin open pkgs; 
   loop fetch pkgs into own, nam; 
   exit when pkgs%notfound; 
   dbms_shared_pool.keep(own || '.' || nam, 'P'); 
end loop;
In the next lesson, we will look at tuning the shared pool reserved size.