Identifying High-Use Packages for Pinning

Question: How do I identify high-use packages for pinning and pin them into an Oracle shared pool library cache?
Oracle's shared pool is a crucial part of the System Global Area (SGA), comprising several components, one of which is the library cache. The library cache stores executable versions of SQL statements, PL/SQL blocks, and other important information. High-use packages, often accessed SQL and PL/SQL objects, can be "pinned" into the shared pool's library cache to prevent them from aging out, thereby boosting performance. The pinning process retains the parsed or compiled form of frequently executed SQL statements or PL/SQL blocks in memory, reducing the parsing overhead.
The following steps describe how to identify high-use packages and pin them in the Oracle shared pool's library cache:

One of the ways to identify high-use packages is by querying the V$SQLAREA or V$SQL views, which provides data about SQL statements that are currently in memory. For example, you can use the following query to list SQL statements ordered by the number of times they were executed:

SELECT sql_text, executions
FROM v$sqlarea
ORDER BY executions DESC;

This will return a list of SQL statements along with the number of times they were executed. The statements with the highest number of executions are potential candidates for pinning.
For PL/SQL objects, you can use the DBA_OBJECTS or DBA_SOURCE views to find frequently used packages, procedures, functions, etc.

Once you have identified the high-use packages, you can use the DBMS_SHARED_POOL package to pin them into the shared pool. The DBMS_SHARED_POOL.KEEP procedure is used for this purpose.
Here is an example of how to pin a package named MY_PACKAGE:

EXEC DBMS_SHARED_POOL.KEEP('MY_PACKAGE', 'P');

In the above SQL command, 'P' indicates a PL/SQL package. For an individual SQL statement, replace 'P' with 'Q' and provide the SQL's address and hash_value, which can be obtained from the V$SQLAREA view.

After pinning the packages, you can verify whether they have been pinned correctly by querying the V$DB_OBJECT_CACHE view.

SELECT owner, name, type, kept
FROM v$db_object_cache
WHERE kept = 'YES';

The SQL command above will return a list of all objects that are pinned in the shared pool. The 'kept' column indicates whether an object is pinned or not.
By following the above steps, you can successfully identify high-use packages and pin them into the Oracle shared pool library cache, which can significantly improve your database's performance by reducing the overhead of reparsing frequently executed SQL statements or PL/SQL blocks.
Please note that care must be taken when pinning objects in the shared pool, as excessive or unnecessary pinning can lead to inefficient use of shared memory and even memory contention issues. Regular monitoring and proper identification of high-use packages are essential to keep the Oracle shared pool performing optimally.

To review one of our primary shared pool tuning goals, we want to find small, high-use packages and pin them into our shared pool library cache. We can use the V$DB_OBJECT_CACHE fixed view to display this information. To look at pinned packages in the SGA run the following script. The output from this listing should show those packages that are frequently used by your application.

memory.sql - Display used SGA memory for triggers,

packages, & procedures
SET PAGESIZE 60;
COLUMN EXECUTIONS FORMAT 999,999,999;
COLUMN Mem_used   FORMAT 999,999,999;
SELECT SUBSTR(owner,1,10) Owner,
       SUBSTR(type,1,12)  Type,
       SUBSTR(name,1,20)  Name,
       executions,
       sharable_mem       Mem_used,
       SUBSTR(kept||' ',1,4)   "Kept?"
 FROM V$db_object_cache
 WHERE TYPE IN ('TRIGGER','PROCEDURE','PACKAGE BODY',

'PACKAGE')
 ORDER BY EXECUTIONS DESC;

Pinned Packages Output
In the next lesson, you will learn how to identify swapped-out items from the shared pool.

The following trigger pins packages on each database startup.
Pinning packages is an effective way of keeping large PL/SQL objects in the shared pool of memory, improving performance and enhancing database stability. This trigger, PIN_ON_STARTUP, will run each time the database is started. You should create this trigger while connected as a user with ADMINISTER DATABASE TRIGGER privilege.

rem while connected as a user with the
rem ADMINISTER DATABASE TRIGGER system privilege:
create or replace trigger PIN_ON_STARTUP
after startup on database
begin
DBMS_SHARED_POOL.KEEP (
'SYS.STANDARD', 'P');
end;