Shared Pool   «Prev 

Pinning Oracle-supplied packages

Because of their frequent usage, Oracle recommends that the
  1. standard,
  2. dbms_standard,
  3. dbms_utility,
  4. dbms_describe, and
  5. 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


CONNECT INTERNAL;
@/usr/oracle/rdbms/admin/dbmspool.sql
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');