Pinning Packages in the Shared Pool Library Cache in Oracle 19c

To pin packages in the Shared Pool Library Cache in Oracle 19c, you use the DBMS_SHARED_POOL.KEEP procedure. Pinning objects in the shared pool prevents them from being aged out due to memory pressure, which can improve performance for frequently used objects like packages, functions, and cursors.
Steps to Pin Packages in the Shared Pool:

1. Connect as SYSDBA or a user with sufficient privileges:
   • You need the EXECUTE privilege on the DBMS_SHARED_POOL package.

   • CONNECT / AS SYSDBA;
                     

2. Load the package into the shared pool:
   • Before pinning, ensure the package is already loaded into the shared pool by referencing it.

   • BEGIN
         PACKAGE_NAME.PROCEDURE_NAME;
     END;
     /
                     

3. Pin the package using DBMS_SHARED_POOL.KEEP:
   • Use the DBMS_SHARED_POOL.KEEP procedure to pin the object. Specify the name of the package in the appropriate schema.

   • BEGIN
         DBMS_SHARED_POOL.KEEP('SCHEMA_NAME.PACKAGE_NAME');
     END;
     /
                     

4. Verify that the object is pinned:
   • You can check the V$DB_OBJECT_CACHE view to confirm that the object is in the shared pool.

   • SELECT NAME, TYPE, KEPT
     FROM V$DB_OBJECT_CACHE
     WHERE NAME = 'PACKAGE_NAME'
       AND TYPE = 'PACKAGE';
                     

   • KEPT = YES indicates the package is pinned.

Example: Suppose you want to pin the `HR.PAYROLL_UTILS` package in the shared pool:

BEGIN
    DBMS_SHARED_POOL.KEEP('HR.PAYROLL_UTILS');
END;
/

To verify:

SELECT NAME, TYPE, KEPT
FROM V$DB_OBJECT_CACHE
WHERE NAME = 'PAYROLL_UTILS'
  AND TYPE = 'PACKAGE';

Notes:

1. Sufficient Shared Pool Size:
   • Ensure the shared pool has enough memory to accommodate the pinned objects. You may need to increase the SHARED_POOL_SIZE parameter in your SPFILE or PFILE.

   • ALTER SYSTEM SET SHARED_POOL_SIZE = 500M;
                     

2. Unpinning Objects:
   • If you want to unpin an object, use the DBMS_SHARED_POOL.UNKEEP procedure:

   • BEGIN
         DBMS_SHARED_POOL.UNKEEP('SCHEMA_NAME.PACKAGE_NAME');
     END;
     /
                     

3. Pinning Best Practices:
   • Only pin frequently used objects that are critical for performance.
   • Monitor shared pool usage with V$SGASTAT and V$DB_OBJECT_CACHE.

By pinning packages strategically, you can reduce parse times and ensure consistent performance in Oracle 19c.

The concept of memory fencing and pinning objects into memory has been around since the 1960's. Any time a piece of data or program is used frequently, the database should have the ability to make that object ineligible for a page-out. IBM first introduced this concept with the introduction of the MVS operating system, but now has extended it to work with databases such as Oracle. The idea is that any PL/SQL, SQL, or data blocks that are frequently referenced by the application should be given special status, so that it will always remain in memory when needed. The trick, of course, is to identify what SQL or data should be kept in memory at the expense of other items in the shared pool or data buffer. That is the real challenge of Oracle tuning. In all of the guidelines stated so far it has been mentioned that the memory is usually allocated above and beyond that needed for

1. fixed size areas and
2. pinned objects.

How do you determine what to pin? Generally speaking any package, procedure, function or cursor that is frequently used by your application should be pinned into the shared pool when the database is started. I suggest adding a null startup function to every in house generated package. It looks like the following piece of code.

FUNCTION start_up RETURN number IS
Ret NUMBER:=1;
BEGIN
  Ret:=0
  RETURN ret;
END start_up;

The purpose of the null startup function is to provide a touch point to pull the entire package into the shared pool. This allows you to create a startup SQL procedure that pulls all of the application packages into the pool and pins them using the DBMS_SHARED_POOL package. The DBMS_SHARED_POOL package may have to be built in earlier releases of Oracle.
The DBMS_SHARED_POOL package is built using the DBMSPOOL.SQL and PRVTPOOL.PLB scripts located in (UNIX) $ORACLE_HOME/rdbms/admin or (NT)

x:\orant\rdbms\admin  

(where x: is the home drive for your install).

• Pinning or Memory Fencing
  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.

• Pinning Oracle-supplied packages
  Because of their frequent usage, Oracle recommends that the
  1. standard,
  2. dbms_standard,
  3. dbms_utility,
  4. dbms_describe, and
  5. dbms_output packages
  always be pinned in the shared pool. The following snippet demonstrates how a stored procedure called sys.standard can be pinned:

The USER_OBJECT_SIZE view gives you the following information about the size of the programs stored in the database:

1. SOURCE_SIZE: Size of the source in bytes. This code must be in memory during compilation (including dynamic/automatic recompilation).
2. PARSED_SIZE: Size of the parsed form of the object in bytes. This representation must be in memory when any object that references this object is compiled.
3. CODE_SIZE: Code size in bytes. This code must be in memory when the object is executed.

Here is a query that allows you to show code objects that are larger than a given size. You might want to run this query to identify the programs that you will want to pin into the database using DBMS_SHARED_POOL in order to minimize the swapping of code in the SGA:

/* File on web: pssize.sql */
SELECT name, type, source_size, parsed_size, code_size
FROM user_object_size
WHERE code_size > &&1 * 1024
ORDER BY code_size DESC

CONNECT INTERNAL;
@/usr/oracle/rdbms/admin/dbmspool.sql
EXECUTE dbms_shared_pool.keep('sys.standard');

A standard procedure can be written to pin all of the recommended Oracle packages into the shared pool. Here is the script:

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'); 
EXECUTE dbms_shared_pool.keep('DBMS_OUTPUT'); 
EXECUTE dbms_shared_pool.keep('DBMS_PIPE'); 
EXECUTE dbms_shared_pool.keep('DBMS_SESSION'); 
EXECUTE dbms_shared_pool.keep('DBMS_SHARED_POOL'); 
EXECUTE dbms_shared_pool.keep('DBMS_STANDARD'); 
EXECUTE dbms_shared_pool.keep('DBMS_UTILITY'); 
EXECUTE dbms_shared_pool.keep('STANDARD');

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:

ORACLE_SID=mydata
export ORACLE_SID
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'); 
exit;
!"

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:

declare 
   own varchar2(100); 
   nam varchar2(100); 
cursor pkgs is 
select 
   owner, 
   object_name 
from 
   dba_objects 
where 
   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;
end;

In the next lesson, we will look at tuning the shared pool reserved size.