Introduction

The Oracle shared pool provides critical services for sharing of complex objects among large numbers of users. DBAs often spent much time learning about the shared pool memory management and configure and tune shared pool usage.
The Automatic Shared Memory Management (ASMM) features introduced in 10GR1 solved this problem by providing the DBA a simple, automatic self-tuning mechanism for configuring shared memory components of SGA, including the buffer cache and shared pool.

Purpose of the Shared Pool

The shared pool was introduced as a feature of the Oracle database in version 7, primary as repository for Shared SQL and PL/SQL. Since that time, although it has evolved to serves in its primary role for caching of cursors shared between sessions connected to the database.
At the most fundamental level, the shared pool is a metadata cache. Whereas the buffer cache is almost completely utilized for the caching of data, the shared pool is used for caching complex objects describing where the data is stored, how it relates to other data and how it can be retrieved.
Much of the shared pool usage is to support the execution od shared SQL and PL/SQL packages; but in order to build a cursor or compile a PL/SQL procedure, we need to know all about the database objects referenced by the SQL or PL/SQL being compiled. For example, in order to build a cursor for a simple select statement from a table, we need to know metadata about the table including column names, data types, indexes and optimizer statistics. All of this additional metadata is also cached in the shared pool, independently of the cursors or program unit.

Components of the Shared Pool

Generally what is of far greater interest to the DBA, are the allocation of memory that can be aged in and out of the cache since building new objects in the shared pool is expensive and impacts scalability and performance. Memory allocations for objects that can be rebuilt are sometimes referred to as 'recreatable'.

V$SGASTAT

The easiest way for examining the contents of the shared pool at a given time is to query the fixed view V$SGASTAT. Since Oracle keeps running totals of component memory allocations in the shared pool, selecting from this view is inexpensive and will not impact the production system. This vire has been enhanced in 10GR2 to provide a finer-granularity of memory allocation data.
Example:

SELECT * FROM(SELECT NAME, BYTES/(1024*1024) MB FROM V$SGASTAT WHERE POOL = 'shared pool'ORDER BY BYTES DESC)WHERE ROWNUM <= 10;

NAME MB
-------------------------- ----------
ktcmvcb 6856.96056
KGH: NO ACCESS 1610.02487
sql area 571.81263
free memory 502.999672
CCursor 301.799118
PCursor 176.69886
library cache 112.56636
kglsim object batch 77.3775787
gcs resources 75.5597076
sql area:PLSQL 51.2854691


Multiple selects from V$SGASTAT will show that some components remain constant in size.

Cache Dynamics

The fluctuations in the size of all of these components, is driven by the process of building new SQL san PL/SQL objects within the cache, a process which itself is driven by the dynamics of the application. Each of these components participates at some level in this process, with memory, constantly in a state flux, being freed from one component for allocation elsewhere.

Object-level View

At this, it should be fairly obvious that each component has many hundreds of objects in the shared pool. where an object can be for example, a cursor, a table, a view or a PL/SQL package.
Usually these individual objects are not large, but they can grow to be several MB based on the object type and its defining attributes. Not surprisingly, a package body containing many procedures is likely to consume more space in the shared pool than a simple select statement.

The size and other details of individual objects in the shared pool cab be obtained by querying the fixed view V$DB_OBJECT_CACHE (or related views such as V$SQL and V$SQLAREA) The query below returns the largest 2 objects from each Namespace in
V$DB_OBJECT_CACHE

SELECT * FROM (SELECT ROW_NUMBER () over (PARTITION BY NAMESPACE ORDER BY SHARABLE_MEM DESC) ROW_within , NAMESPACE, SHARABLE_MEM, SUBSTR(NAME,1,40) NAME FROM V$DB_OBJECT_CACHE ORDER BY SHARABLE_MEM DESC) WHERE ROW_WITHIN <= 2 ORDER BY NAMESPACE, ROW_WITHIN;

ROW_WITHIN NAMESPACE SHARABLE_MEM NAME
---------- ---------------------------- ------------ ----------------------------------------
1 CLUSTER 2794 C_OBJ#_INTCOL#
2 CLUSTER 1684 SMON_SCN_TO_TIME
1 RSRC PLAN 5117 SYS_GROUP
2 RSRC PLAN 0 OTHER_GROUPS
1 RULESET 34367 ALERT_QUE_R

Memory Heaps

One of the popular misconceptions is that we can often run into problems when we try and allocate memory for one of these large objects. In extreme cases that can be true, but not this is not normally the case. In order to understand why, we have to understand that each individual object is not comprised of single large allocation, but is further partitioned into independent memory allocation called ‘heaps’. The number of heaps for an object depends on the object type. For example, a SQL cursor has 2 heaps: a smaller heap for the library cache metadata (usually called heap 0) and a larger heap containing the executable representation of the cursor (usually called sqlarea). Understanding this is important for comprehending the inner workings of some init.ora parameters like session_cached_cursors.
Although heaps themselves may be pretty large, each heap id itself comprised of one more chunks of memory. The following diagram shows the heap layout for some typical shared pool objects:

Space Allocation in the Shared Pool

The first thing to understand here is that Oracle required contiguous space to satisfy each memory request. For example, if a request is made for a 4K chunk of memory, then the heap manager (the bit of Oracle responsible for keeping track of shared pool space) cannot simply return a 3K chunk and a 1K chunk to the requester. If no such chunk exists, then a process begins of freeing batches of heaps from the shared pool, rechecking the free space after each batch to see if sufficient contiguous memory has been freed.
When the shared pool has no space to satisfy a request for memory then an ORA-4031 error is signaled. However, before signaling the error, the heap manager will always try to free as many objects as it can before giving up; iterates through the shared pool LRU list five times attempting to free space and so create a contiguous chunk of the requested size, before raising a 4031. For each chunk of memory freed, the memory is automatically coalesced wherever possible.

SQL & PL/SQL in the Shared Pool

All SQL cursors and PL/SQL packages a re allocated almost totally in 4K chunks. The bigger the cursor, the more 4K chunks we allocate to it; but rarely more than 4K unless there is some unusual requirement that needs to be met by the SQL optimizer or PLSQL runtime engine. Since the majority of allocation request are made for 4K chunks, then all freed cursors will also return 4K chunks back to the pool. It follows that there should never really be a problem building a new cursor or PL/SQL package providing there are old cursors or packages that we can age out.

Parsing

Since we are on the subject of memory allocation for SQL and PL/SQL, it is also necessary to discuss the subject of parsing, since without hard parsing, there would rarely be any requirement to allocate space from the shared pool anyway.
Hard parsing affects the system at several levels. Even before the cursor can be built, the SQL has to be parsed and optimized. This process requires access to library cache and row cache resources and may incur recursive loading of library cache and row cache objects. This puts pressure on library cache, row cache and shared pool components and explains why high hard parse rates are often accompanied by latch contention for these systems.
The relative cost of soft parse to hard parse is several orders of magnitude less. However, even soft-parsing is not without cost. In particular, the lookup of the SQL statement in the shared pool and the lock and pin operations required to execute the cursor all incur latching overhead. Also, although the cost of soft parse is several orders of magnitude less, the frequency is often orders of magnitude more. This is why various SQL caching options have been introduced on both client and server sides to aid scalability on high throughput systems.

Literals

Another application consideration is usage of literals. If there are many identical single-use SQL statements in the shared pool which differ only by the literal values, then this is a SQL sharing issue caused by the application generating SQL statements with literals rather than bind variables. High-throughput OLTP systems should ideally use bind variables to allow for the re-use of existing SQL statements; if modifying the application is not possible, then consider using the init.ora parameter cursor_sharing set to FORCE to reduce the number of single-use statements which differ only in literals.
There are a number of different ways to identify literal SQL:
• One method is to check V$SQLAREA for SQL statements which have the same execution plan (i.e. PLAN_HASH_VALUE), but differing SQL Id’s (SQL_ID) • A second method is to compare the leading N characters of SQL text in the V$SQLAREA view; those SQL statements with the same leading N characters may be the identical SQL with literals

Configuring the Shared Pool

However, arriving at the initial sizing of the shared pool has always been problematic because the dynamics of the application workload and the size of the global memory footprint are incredibly difficult to model. Even once an acceptable level of shared pool performance is attained for a given configuration, changes in the application, changes in user load and changes in initialization parameters can all negatively impact the system. Given that a system has been established and that a representative workload can be executed, the following section gives some useful tips on what to look for when assessing the SGA requirements.
Note that proactive sizing of the shared pool, and reactive tuning when problems arise should be handled differently; reactive tuning should always be driven by the biggest bottleneck in the system. The biggest bottleneck should be determined by examining the wait events along with the time model (V$SYS_TIME_MODEL) data. This data can be used to identify which is the largest problem area, and hence what to concentrate on.

Undersizing the Shared Pool

Of the two possible extremes, ‘too small’ is definitely the most problematic. Having a shared pool that is too small could result in a myriad of performance issues:• ORA-04031 out of shared pool memory • Library cache lock and pin contention (high library cache reloads) • Row cache enqueue contention (high row cache reloads) • Latch contention (shared pool, library cache, row cache) These should be examined in the order suggested here.

ORA-04031

This error should not appear in any of the application logs, the alert log or any trace files. Do not depend on ORA-04031 errors being written to the alert log, as 4031 errors only appear in the alert log if they affect background process operations (such as PMON activities). 4031’s are not internal errors and so could be trapped and handled by the application (this is not recommended).
From 10gR1 onwards, a 4031 trace file is written to the user_dump_dest (or background_dump_dest) directory; this trace file is useful in diagnosing the nature of problem.

Library Cache Reloads

The shared pool should be large enough to avoid the excessive overhead from constantly reloading the same recreatable objects.
Before sizing the shared pool to avoid reloads, you should check that the reloads are not a consequence of invalidations. If the number of invalidations is a significant proportion of the number of reloads (where significant is any number greater than 20%), then investigate the cause and fix the source of the invalidations first.
The following query from V$LIBRARYCACHE will show haw many reloads and Invalidations have occurred since instance startup

SELECT NAMESPACE, PINS, PINS-PINHITS LOADS, RELOADS,INVALIDATIONS, 100 * (RELOADS - INVALIDATIONS) / (PINS-PINHITS) "%RELOADS" FROM V$LIBRARYCACHE WHERE PINS >0 ORDER BY NAMESPACE;

NAMESPACE PINS LOADS RELOADS INVALIDATIONS %RELOADS
--------------- ---------- ---------- ---------- ------------- ----------
BODY 104188273 1052 603 0 57.3193916
CLUSTER 5613 140 79 0 56.4285714
INDEX 20675002 6151 4369 0 71.029101
SQL AREA 627459674 9140453 238610 175744 .687777728
TABLE/PROCEDURE 265612275 86391 37095 0 42.9385005
TRIGGER 483898 316 232 0 73.4177215


Although there are no absolute recommendations, library cache reloads should be only a small percentage (say 10%) of the total loads on the system.

Row Cache MIsses

If library cache reloads are high, then it is likely that dictionary cache misses will be high also. Query V$ROWCACHE to view this data.

SELECT PARAMETER , SUM(GETS) GETS , SUM(GETMISSES) GETMISSES, SUM("COUNT") NUM, SUM(USAGE) USAGE FROM V$ROWCACHE WHERE GETMISSES > 0 GROUP BY PARAMETER ORDER BY PARAMETER;

PARAMETER GETS GETMISSES NUM USAGE
-------------------------------- ---------- ---------- ---------- ----------
dc_awr_control 16460 415 1 1
dc_constraints 2001 677 0 0
dc_database_links 11383620 89 5 5
dc_files 4248 1313 0 0
dc_global_oids 513894 1084 34 34
dc_hintsets 1 1 0 0
dc_histogram_data 213983893 87858 5959 5959
dc_histogram_defs 61303102 97242 3983 3983
dc_object_grants 13188017 1827 250 250
dc_object_ids 301224033 22093 930 930
dc_objects 73246110 30001 1089 1089
dc_profiles 16354351 20 3 3
dc_rollback_segments 45923272 644 584 584
dc_segments 30937535 34097 1230 1230
dc_sequences 13754 3036 11 11
dc_table_scns 46760420 1505551 1 1
dc_tablespace_quotas 12121 1225 22 22
dc_tablespaces 70787039 103 102 102
dc_usernames 66902952 306 24 24
dc_users 413738271 1048 103 103
kqlsubheap_object 1 1 1 1
outstanding_alerts 314186 306164 5 5



...