RelationalDBDesign RelationalDBDesign

Shared Pool   «Prev 

Pinning Oracle-supplied packages

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

Use Program Size to Determine Pinning Requirements

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

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');