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:
- 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:
Related
Diagnosing and Resolving Error ORA-04031 (Metalink Note 146599.1)
