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(kept||' ',1,4) "Kept?"
WHERE TYPE IN ('TRIGGER','PROCEDURE','PACKAGE BODY',
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
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