Sizing the Shared Pool

Resizing the shared pool
To size the shared pool we have only one parameter available to influence the library
cache size.The parameter name is "SHARED_POOL_SIZE".this parameter sizes the shared pool and then The dictionary cache takes some portion of the shared pool as determined by an internal algorithm.The calculations that have been used by the internal algorithm to determine the size of the shared pool
are based on many estimates about which objects were cached, the number of concurrent users,
and concurrent open cursors. These estimates can lead to wildly different values.

Oracle Support recommends using Automatic Shared Memory Management (ASMM). This is
enabled by setting the SGA_TARGET parameter to the value that the entire SGA will use. The
Memory Manager (MMAN) process adjusts the memory allocated to dynamic pools to obtain
the best performance within the memory allowed.

When the database is already in operation, the Shared Pool Advisor gives an indication of an
estimated optimal size. As with any estimate, check other indicators such as latch waits, hard
parses, and reloads for confirmation.
When there is no baseline, set the SHARED_POOL_SIZE to approximately 40% of the
available SGA.We can change this value after monitoring the performance and determine if we need more memory or decreasing the memory

The shared pool requires a certain amount of memory to even allow the instance to start. This
value varies with the packages that are invoked at startup, and that depends on the features and
options that are enabled. Enterprise Manager will invoke certain packages as soon as it starts.

Shared Pool Advisory
The STATISTICS_LEVEL initialization parameter controls the shared pool advisory. The
shared pool advisory statistics track the library cache’s use of shared pool memory and predict
the change in total instance-wide parse times for different sizes of the shared pool.

The below 2 views help you determine how much memory the library cache is using and how much does the library cache needs?.

SQL> select * from  V$LIBRARY_CACHE_MEMORY;

LC_NAMESPACE    LC_INUSE_MEMORY_OBJECTS LC_INUSE_MEMORY_SIZE LC_FREEABLE_MEMORY_OBJECTS LC_FREEABLE_MEMORY_SIZE
--------------- ----------------------- -------------------- -------------------------- -----------------------
BODY                                  0                    0                         34                       2
CLUSTER                               0                    0                          0                       0
INDEX                                 0                    0                          0                       0
JAVA DATA                             0                    0                          0                       0
JAVA RESOURCE                         0                    0                          0                       0
JAVA SOURCE                           0                    0                          0                       0
OBJECT                                0                    0                          0                       0
OTHER/SYSTEM                          0                    0                          0                       0
PIPE                                  0                    0                          0                       0
SQL AREA                            208                    3                        231                       4
TABLE/PROCEDURE                       0                    0                        391                       3

TRIGGER                               0                    0                          0                       0

12 rows selected.
The V$LIBRARY_CACHE_MEMORY view displays information about memory allocated to
library cache memory objects in different namespaces. A memory object is an internal grouping
of memory for efficient management. A library cache object may consist of one or more
memory objects.
V$LIBRARY_CACHE_MEMORY, provide information to help
you determine how much memory the library cache is using, how much is currently pinned, how
much is on the shared pool’s LRU list, and how much time might be lost or gained by changing
the size of the shared pool. These statistics are reset if the STATISTICS_LEVEL parameter is
set to BASIC, or when the instance is restarted.

In order to estimate  how much memory does the library cache needs?.
SQL> SELECT shared_pool_size_for_estimate AS
pool_size, estd_lc_size,
estd_lc_time_saved
FROM V$SHARED_POOL_ADVICE;

 POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED
---------- ------------ ------------------
       132            8              86003
       148           24              87333
       164           40              88893
       180           56              89500
       196           72              90087
       212           88              90518
       228          104              90742
       244          120              90911
       260          136              91032
       276          152              91118

10 rows selected.

The V$SHARED_POOL_ADVICE view should be the first tool to use when sizing the shared
pool. If the advisory indicates that a larger pool is not useful for the library cache memory
objects, you can drill down to see whether changing the SQL (to improve use of shared cursors)
or other activity (such as deferring DDL to off hours) improves performance.

The V$SHARED_POOL_ADVICE view displays information about the estimated time saved
during parsing using different shared pool sizes. The sizes range from 50% to 200%, in equal
intervals of the current shared pool size, and are not configurable. If rows in the
V$SHARED_POOL_ADVICE view have the same values of parse time savings (given in the
ESTD_LC_TIME_SAVED column), this indicates that there would be no additional hits on
those size ranges for library cache objects. However, if time saving values increase for larger
pool sizes, this indicates that it may help to increase the shared pool size.

The Statspack/AWR reports also present the shared pool advisory.