Oracle RDBMS: Pinning of Database Objects

This is one way to improve performance for a program that uses large packages. To understand the concept, Lets ask ourselves why pin is used in our daily life? To have a small note of important points like phone numbers or syntaxes to be recollected often or to get noticed soon. The same applies here in database. The process of placing the database packages in the Shared Global Area (SGA) is called Pinning.

SGA consists of shared pool,buffer cache, large pool, java pool, log buffer, streams pool etc. We are concerned only about shared pool now. In 10g, we can use sga_target init parameter instead of specifying size of each and every SGA components in the init.ora. This parameter will set automatic shared pool management. shared pool works on continuous memory chunks called granules.Shared pool works on LRU algorithm.

Using multiple large PL/SQL objects in the database can be hazardous job. The database has to search for free space for the object. If it cannot get enough contiguous space, it will free many small objects to satisfy the request. To search for small objects causes high CPU resources. Because of this we pin the large objects to shared pool during start up.Objects are 'kept' in the shared pool using the dbms_shared_pool package that is defined in the dbmspool.sql file.

Syntax:

execute dbms_shared_pool.keep('owner.object');

To view a list of all objects that are kept in the shared pool, one can query the v$db_object_cache :

select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';

In Oracle Applications :

The $AD_TOP/sql/ADXGNPIN.sql script is provided to pin the packages in Oracle Applications. This script pins all Oracle Applications objects, which is generally not completely necessary or advisable.

This method is very useful in data warehousing environment, where we can pin the most used objects with the memory.