Identifying High-Use Packages for Pinning

To identify high-use packages for pinning and pin them into Oracle's shared pool (library cache), follow these steps:
1. Identifying High-Use Packages

1. Query the V$SQL View
   • Identify frequently executed SQL and PL/SQL objects (procedures, functions, and packages) using the following query:

   • SELECT 
         executions,
         sql_text,
         sharable_mem
     FROM 
         v$sql
     WHERE 
         loaded_versions > 0
     ORDER BY 
         executions DESC
     FETCH FIRST 10 ROWS ONLY;
             

   • Look for objects with high execution counts (executions) or those occupying large amounts of memory (sharable_mem).
2. Query the V$DB_OBJECT_CACHE View
   • Analyze the library cache for specific PL/SQL objects such as packages:

   • SELECT 
         name,
         type,
         sharable_mem,
         loads,
         executions
     FROM 
         v$db_object_cache
     WHERE 
         type IN ('PACKAGE', 'PACKAGE BODY')
     ORDER BY 
         executions DESC;
             

   • Focus on objects with high loads (indicating frequent reloads due to insufficient shared pool space) and executions.
3. Monitor Active Sessions
   • Use the V$SESSION and V$SQLAREA views to see currently active packages or queries:

   • SELECT 
         s.sid,
         p.sql_text,
         s.program
     FROM 
         v$session s
     JOIN 
         v$sqlarea p ON s.sql_id = p.sql_id
     WHERE 
         s.type = 'USER';
             

---

2. Pinning High-Use Packages

1. Enable DBMS_SHARED_POOL
   • Ensure the DBMS_SHARED_POOL package is installed and accessible:

   • GRANT EXECUTE ON dbms_shared_pool TO <user>;
             

2. Pin the Object in the Shared Pool
   • Use DBMS_SHARED_POOL.KEEP to pin packages into the shared pool:

   • BEGIN
         DBMS_SHARED_POOL.KEEP('<schema_name>.<object_name>', 'P');
     END;
     /
             

   • Replace <schema_name> and <object_name> with the schema and name of the package.
   • The second parameter, 'P', is optional and indicates pinning a package.
3. Verify the Pinning
   • Check if the object is pinned:

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

3. Automate the Pinning Process

• Use a startup trigger to pin high-use packages automatically when the database starts:
  

  CREATE OR REPLACE TRIGGER keep_objects_after_startup
  AFTER STARTUP ON DATABASE
  BEGIN
     DBMS_SHARED_POOL.KEEP('<schema_name>.<object_name>', 'P');
  END;
  /
  

4. Regular Maintenance

• Monitor and Adjust: Regularly review shared pool usage using `V$SGASTAT` and `V$SHARED_POOL_ADVICE` to ensure optimal performance.
• Purge Unused Objects: Remove objects that are no longer needed in the shared pool:
  

  EXEC DBMS_SHARED_POOL.UNKEEP('<schema_name>.<object_name>');
  

By pinning frequently accessed packages, you can reduce parsing and loading overhead, improving performance for critical objects.

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;

View the diagram for the Shared Pool Objects output script below to see a description of the process.
Pinned packages Output

1. In this listing, we see those packages that have the highest number of executions and their memory size. In general, you will want to pin those packages that are frequently referenced. Be aware, however, that pinned packages will leave less re-usable memory in the shared pool, and you may want to increase the shared_pool size to accommodate the pinned packages.

Although Oracle Database breaks down very large requests for memory into smaller chunks, on some systems there might still be a requirement to find a contiguous chunk (for example, over 5 KB) of memory. (The default minimum reserved pool allocation is 4,400 bytes.) If there is not enough free space in the shared pool, then Oracle Database must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.
Thus, Oracle Database internally reserves a small memory area in the shared pool that the database can use if the shared pool does not have enough space. This reserved pool makes allocation of large chunks more efficient. By default, Oracle Database configures a small reserved pool. The database can use this memory for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects. After the memory allocated from the reserved pool is freed, it returns to the reserved pool. You probably will not need to change the default amount of space Oracle Database reserves. However, if necessary, the reserved pool size can be changed by setting the SHARED_POOL_RESERVED_SIZE initialization parameter. This parameter sets aside space in the shared pool for unusually large allocations. For large allocations, Oracle Database attempts to allocate space in the shared pool in the following order:

1. From the unreserved part of the shared pool.
2. From the reserved pool. If there is not enough space in the unreserved part of the shared pool, then Oracle Database checks whether the reserved pool has enough space
3. From memory. If there is not enough space in the unreserved and reserved parts of the shared pool, then Oracle Database attempts to free enough memory for the allocation. It then retries the unreserved and reserved parts of 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;

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