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.
1) OWNER, 2) TYPE, 3) NAME, 4) EXECUTIONS, 5) MEM_USED Kept
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.
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;