SHARED POOL

The Shared Pool is a memory structure that is shared by all system users.  It consists of both fixed and variable structures.  The variable component grows and shrinks depending on the demands placed on memory size by system users and application programs.

Memory can be allocated to the Shared Pool by the parameter SHARED_POOL_SIZE in PFILE.  The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.You can alter the size of the shared pool dynamically with the ALTER SYSTEM SET command.  

Shared Pool size cannot be > SGA_MAX_SIZE or SGA_TARGET. It is recommended to let Oracle optimize/manage the Shared Pool size by implementing AMM or ASMM to manage memory.

Shared Pool stores the most recently executed SQL statements and used data definitions.  This is because some system users and application programs will tend to execute the same SQL statements often having this information in memory improves system performance. Shared Pool includes Library Cache and Data Dictionary Cache.

SQL> show parameter shared_pool

NAME                                                  TYPE               VALUE

------------------------------------                -----------            ------------------------------

shared_pool_reserved_size             big integer      95630131

shared_pool_size                              big integer       0

                                                                                                                                 

SQL> Select sum(bytes/1024/1024) "Size (MB)"  

                FROM V$SGASTAT

                where POOL = 'shared pool';

 Size (MB)

---------------

      1888 

SQL> SELECT POOL, NAME, BYTES/1024/1024 "Size (MB)"

                  FROM V$SGASTAT 

               WHERE NAME = 'free memory' 

                      AND POOL = 'shared pool';

POOL              Option                                                               Size (MB)

------------         ----------------------------------------------                          ----------

shared pool     free memory                                                     376.965912

The Oracle shared pool contains Oracle's library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. Hence, the shared pool is a key component, so it's necessary for the Oracle database administrator to check for shared pool contention.

The amount of memory available for the library cache can drastically affect the parse rate of an Oracle database instance. The shared pool advisory statistics provides information about library cache memory to database administrators (DBA’s), allowing them to predict how changes in the size of the shared pool can affect aging out of objects in the shared pool.


The shared pool advisory statistics track the library cache's use of shared pool memory and predict how the library cache will behave in different sizes of shared pools. Determines 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.

set lines 200

--

COL shared_pool_size_for_estimate     FOR 9999999                    HEA sp_size

COL shared_pool_size_factor                FOR 9999999.9999           HEA sp_size_factor

COL estd_lc_size                                   FOR 999999                      HEA lc_size

COL estd_lc_memory_objects               FOR 99999999                  HEA lc_mem_objs

COL estd_lc_time_saved                       FOR 9999999999              HEA lc_time_saved

COL estd_lc_time_saved_factor            FOR 999999.99999           HEA lc_time_saved_factor

COL estd_lc_load_time                          FOR 999999999                HEA lc_load_time

COL estd_lc_load_time_factor               FOR 9999999.99999         HEA lc_load_time_factor

COL estd_lc_memory_object_hits         FOR 99999999999999      HEA lc_memory_object_hits

--

select * from v$shared_pool_advice;

This above query displays information about estimated parse time in the shared pool for different sizes ranges from 10% of the current shared pool size or the amount of pinned library cache memory, whichever is higher, to 200% of the current shared pool size in equal intervals. The value of the interval depends on the current size of the shared pool.

Shared Pool Components/Contents:

There are many components in shared pool that occupy memory and are shared globally. A number of components in shared pool are fixed in size are allocated at the instance start-up.

The below query shows the contents of shared pool at any given time via a fixed view, The data displayed is sum of all allocation of each component.

SQL>select * from

(select name, round(bytes/1024/1024,4) MB from v$sgastat

 where pool = 'shared pool'

 order by 2 desc)            

where rownum <=10;

NAME                                         MB

--------------------------                ----------

sql area                                   1522.3323

free memory                           1257.9943

KGH: NO ACCESS                 758.4828

CCursor                                   297.9297

PCursor                                   165.5545

library cache                           118.9883

kglsim object batch                  66.1936

Cursor Stats                            64.0249

kglsim heap                             45.2159

db_block_hash_buckets         45

Some of the Components that are found in shared pool are:

•         SQL (cursors) à SQL statements that gets called repeatedly by many sessions.
•          PL/SQL à Packages and Procedures that gets called repeatedly by many sessions.
•          Library Cache à Metadata for tables, indexes, views, synonyms, dependencies, constraints and others.
•         Row Cache à Cached rows from data dictionary used to build complex SQL and Library Cache objetcs.

The background process MMAN (memory manager) manages the shared pool automatically by allocating memory dynamically on demand. When the server process send a memory request MMAN allocates memory in chunks of (4k or less) depends on the size required. When the shared pools has no space to allocates the memory, MMAN will try to free the space by flushing the aged out objects from the shared pool and if all the objects in the shared pool are pinned (the objects are being currently used in SQL or PL/SQL query execution) then the MMAN throw the error ORA-04301.

There are no. of fixed view to look at the shared memory used by each instance of the SQL or PL/SQL via the views V$SQL, V$SQLAREA, V$SQLSTATS. Its recommended to use v$sqlstats view as it faster and has greater data retention.

SQL>select * from

(select sql_id,plan_hash_value,

       to_char(last_Active_time, 'DD-MON-YYYY HH24:MI') last_active, 

  parse_calls,rows_processed,executions,fetches,loads,

       round(cpu_time/1000/1000, 2) "CPU Time Sec's",

       round(elapsed_time/1000/1000,2) "Elapsed Sec's", 

       round(sharable_mem/1024) "MemKB",

       sorts

from v$sqlstats

order by 10 desc)

where rownum < 30;

To look up the no. of objects by namespace in the library cache of shared pool run the following query:

SQL>select namespace, pins, pinhits, reloads, invalidations

  from v$librarycache

 order by namespace;

To look up the no. of times different type of objects in the shared pool been referred can be seen by the following views:

SQL>COL hit_ratio FOR 999.99

SQL>COL "DML's"   FOR 999999999

--

SQL>SELECT parameter, sum(gets) hits, sum(getmisses) missed,

       100*sum(gets - getmisses) / sum(gets) hit_ratio,

       sum(modifications)  "DML's", sum(dlm_requests) requests,

       sum(dlm_conflicts) conflists, sum(dlm_releases) releases,

       sum(flushes) "FlushToDisk"

 FROM v$rowcache

 WHERE gets > 0

 GROUP BY parameter;