Pinning Objects to Improve Apps Performance

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:

System Global Area (SGA) Pools:

  • 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 granulesThe 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:

SQL output to identity candidates:

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:



I found the following comments interesting because I saw something opposite in 10g (compare with 8i/9i). I have to double the shared_pool, set several underscore parameters, etc. In my opioion, ASMM is not there yet.

"The Oracle database's automatic memory allocation is superior to manual memory management. This prevents ORA-4031 errors in most cases."

Posted by Tianhua Wu on May 30, 2007 at 02:14 AM PDT #

Tianhu, Any specific case to support the comment? I mean if there is a case, always start a tar with  support

Posted by Avanish Srivatsav on May 30, 2007 at 02:20 AM PDT #

Yes, we did have TAR with Oracle for several Months since last year. I would give you the TAR number, unfortunately I can not find TAR beyond March in Metalink. I had to manually double the size of shared_pool (compared with 9i), reduce subpool number to 1, etc to get rid of ora-4031.

Posted by Tianhua Wu on May 30, 2007 at 06:44 AM PDT #

Nice post...the subject needed to be addressed and you did it very well.

Posted by Floyd Teter on June 01, 2007 at 09:46 AM PDT #

It appears sga_target does not work as well as initially hoped, which is why Note 216205.1 and the R12 equivalent 396009.1 require shared_pool_size and shared_pool_reserve_size be set to resolve ora-4031 errors. For the first year or so after 10g was certified for 11i, those 2 parameters were on the removal list on Note 216205.1. I have seen the problem in many different ERP instances when shared_pool_reserved_size is not set.

Posted by Michael Taylor on June 05, 2007 at 08:20 AM PDT #

Just the observation that on both Linux and Solaris instances on 10gR1 and 10gR2, whenever shared_pool_reserved_size is not set, users see ORA-00431 unable to allocate shared memory segment errors after a few days. I assumed it was not coincidental that shared_pool_size and shared_pool_reserved_size became mandatory parameters for both 11i and R12 per the referenced Notes, and figured there was no point creating a TAR for issues with the parameters unset if they are required. You could probably work with Ahmed Alomari's team to see exactly why the parameters are now required.

Posted by Michael Taylor on June 05, 2007 at 02:01 PM PDT #

Michael, two things. First it is not mandatory to set the shared_pool parameters, in the note given 216205.1 all the mandatory parameters have #mp suffixed. The idea behind giving these parameters is, althought we have the automatic shared memory management to take care of these parameters, it is observed that the pain in allocating the sizes is high. So if we set  a certain value to these parameters, they are taken as minimum sizes ie at any time, oracle will never have shared_pool_size less than what is set, althought if at a time we need higher values, oracle will be able to allocate. these parameters are made as a context variables and when autoconfig is run, it will prompt for the size, if the end user enters a value, that will be taken otherwise the default of what we recommended is taken.

Posted by Avanish Srivatsav on June 05, 2007 at 06:36 PM PDT #

We have shared_pool_reserve_size set, even you did not set it, it is 10% of shared_pool_size by default, given our shared_pool_size, it is more than enough. There are several parameters (such as _shared_pool_reserved_min_alloc, _kghdsidx_count) need to be set to fix ora-4031.

But my point here is not how to resolve ora-4031, this is just a technical issue that can be resolved. My point is ASMM is not mature yet, it might take Oracle several RDBMS releases before it works the way it is supposed to. For 10g, it is not there yet.

Posted by Tianhua Wu on June 08, 2007 at 05:11 AM PDT #


Your given example invites us to -

"Imagine a large package (or any object) has to be loaded into the shared pool."

Surely, if this were a regular occurrence, the correct answer would be to configure a separate Large Pool allocation to which such large objects would be loaded ?

Thank you for an interesting article.


Posted by Glenn Oliver on October 01, 2008 at 09:33 PM PDT #

Hi, Glenn,

Sadly, Avanish has moved on to other pastures. I've passed on your comment to other members of the Applications Performance Group. I'll post their replies when I receive them.


Posted by Steven Chan on October 08, 2008 at 03:03 AM PDT #

Hi, Glenn,

Although he has other development responsibilities in a different group now, Avanish was kind enough to send me this reply to your question:

We pin the pl/sql packages etc in library cache in shared pool.

Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance:

1. Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved

2. I/O Server Processes

3. Parallel Query Buffers if the initialization parameter
PARALLEL_AUTOMATIC_TUNING is set to true (otherwise, these buffers are allocated to the shared pool)

4. Oracle Backup and Restore Operations using RMAN

The large pool does not have an LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.

Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the above components otherwise is done from the shared pool. Also due to the large memory requirements for I/O and Rman operations, the large pool is better able to satisfy the requirements instead of depending on the Shared Pool Area.

So if we decide to pin the packages in shared pool, we should properly size the shared pool and then pin. Large pool has no role here.


Posted by Steven Chan on October 14, 2008 at 06:42 AM PDT #

Hi Steven:
I am running with Oct2008 cpu using ebs 11.5.10 rup6 in RH Linux. I am suggested to set Sga_target to 0. I think this is turn off the asmm feature and configure db_cache_size, shared_pool_size, large_pool_size and java_pool_size manually. Basically, he suggests do not use asmm feature. Can you comment on this?

Other thought is to setupup sga_target > 0 and manual configure 2 parameters (db_cache_size, and shared_pool_size) only but total of (db_cache_size+shared_pool_size) < ½ of sga_target. Hope the asmm feature can auto turn or use rest of memory. Any comment?

Thanks in advance.

Posted by Zosen Wang on January 08, 2009 at 04:38 AM PST #

I have few queries regarding V$DB_OBJECT_CACHE view.

1. I found executions as 0 for some of the packages. How and When this execution is recorded as 0?
2. I also found that in my production database, for some of the object say package X, timestamp is showing 10 year old date, is that means that
a) this object X has not been used for last 10 years i.e. last used 10 years before?
b) This view has not been flushed/refreshed from last 10 years?

3. When and how this view get refresed?
4. Can we use this view for determining potentially unused objects?

Thanks in advance.
Best Regards

Posted by Amit on August 27, 2013 at 06:07 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed


« July 2014