rkorclappsdbastuff’s

Pinning Objects to Improve Apps Performance

By avanish.srivatsav on May 29, 2007

Keeping database packages in the Oracle database's System Global Area (SGA) shared pool is a common practice. This is also called pinning. One common misconception is that pinning is not useful or needed in later Oracle database versions such as 10g and above, since those releases offer automatic shared memory management. This isn't completely accurate for all cases, for reasons which I'll discuss below.

An Introduction to the System Global Area (SGA)
The Oracle database's System Global Area contains various pools of memory used to satisfy particular classes of memory allocation requests:

Shared pool: used for caching shared cursors, stored procedures, dictionary caches, etc.
Buffer cache: cache of disk data
Large pool: large allocations
Java pool: Java allocations and for caching java objects
Log buffer: in-memory buffer for redo generation
Streams Pool: new in 10g, for buffering inbound and outbound logical change records
Fixed SGA: bootstrap section of the SGA

SGA memory is allocated in units of contiguous memory chunks called granules. The size of a granule depends on your operating system platform and the SGA's total size. On most platforms, if the estimated SGA size is:

Less than or equal to 1GB, the granule size is 4 MB
Greater than 1GB, the granule size is 16 MB

A Primer on Space Allocations in the Shared Pool

One of the important components of the SGA is the shared pool. The
shared pool was introduced as a feature of the Oracle Database in
Version 7, primarily as a repository for shared SQL and PL/SQL. The
shared pool has come a long way since its original release.

The Oracle database requires contiguous space. For example, if a request for 4 K of memory is made, the database cannot allocate separate 3 K and 1 K chunks. It must allocate a 4 K block of contiguous free memory to satisfy the request. If there is no free memory, it will scan the Least Recently Used list to free some memory. The heap manager will try to free as many objects as possible before giving up. If the shared pool has no space to satisfy the request for memory, an ORA-4031 error is thrown.
With the advent of automatic shared memory management, we need not configure the size of the shared pool via the shared_pool_size parameter. Automatic shared memory management requires one parameter to be set: sga_target. The Oracle database's automatic memory allocation is superior to manual memory management. This prevents ORA-4031 errors in most cases.

When Large Objects Jump in the Shared Pool

Imagine a large package (or any object) has to be loaded into the shared pool. Large PL/SQL objects present particular challenges. 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. If several large objects need to be loaded, the database has to throw out many small objects in the shared pool.
Finding candidate objects and freeing memory is very costly. These tasks will impact CPU resources.

One approach to avoiding performance overhead and memory allocation errors is to keep large PL/SQL objects in the shared pool at startup time. This process is known as pinning. This loads the objects into the shared pool and ensures that the objects are never aged out of the shared pool. If the objects are never aged out, then that avoids problems with insufficient memory when trying to reload them.
What's in Your Shared Pool Now?
Objects are 'kept' in the shared pool using the dbms_shared_pool package that is defined in the dbmspool.sql file.

For example:

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

The SQL query above will list all of the objects that are 'kept' in the shared pool using dbms_shared_pool.keep.
Identifying Candidate Objects for Keeping in the Shared Pool

To identify candidates that should be kept in the shared pool, first run the following query:

select 
substr(owner,1,10)||'.'||substr(name,1,35) "ObjectName", type, 
sharable_mem,loads, executions, kept from v$db_object_cache where type 
in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') and executions >0
 order by executions desc,loads desc,sharable_mem desc

The query above will return something like this:

Next, query the x$ksmlru table, using:

select * from x$ksmlru;

The x$ksmlru table keeps track of the current shared pool objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. Here's what the table looks like:

describe x$ksmlru        

Table or View x$ksmlru

     Name           Null?    Type
     -------------- -------- --------------
     ADDR                    RAW(4)
     INDX                    NUMBER
     KSMLRCOM                VARCHAR2(20)
     KSMLRSIZ                NUMBER
     KSMLRNUM                NUMBER

KSMLRNUM shows the number of objects that were flushed to load the large object
KSMLRISZ shows the size of the object that was loaded (contiguous memory allocated)

Note: This is a fixed table: once you query the table, the database will
automatically reset the table. Make sure that you spool the output to
a file so you can capture it for analysis.
Analyze the x$ksmlru output to determine if there are any large allocations that are flushing other objects. If this is the case, analyze the v$db_object_cache to identify the objects with high loads or executions. These should be kept in the shared pool.

Keeping Objects in Oracle Applications Databases

All E-Business Suite DBAs should do some analysis to assess whether pinning can improve the performance of your Apps environment. Arriving at the objects to be pinned varies from setup to setup. Objects have to be pinned after each instance startup, and ideally immediately after the startup.
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. You should modify this script based on your analyses of v$db_object_cache and x$ksmlru to identify the right objects to be pinned for your environment.
For more information about this script (and the related ADXCKPIN.sql script, used for listing objects in the shared pool), see the appropriate manual for your Apps release: