Shared Pool tuning is the most important aspect of Oracle shared global area optimization. And by far it is the most neglected area.

Shared Pool is a memory area in Oracle which is in turn a part of Oracle SGA(System Global Area). Shared Pool comprises of  different meory Strctures. If shared pool size is not configured Properly , the performance of the database suffers drastically.

Shared Pool consists of

1. Library Cache

2. Dictionary Cache

3. Control Structures.

Library cache inturn comprises of Shared Sql Area, Pl/sql Procedures, Functions, Packages (loaded) and control Structures (Latches and locks).

Shared Sql Area : contains Parsed SQL and execution Plans for statements already run against the database.

Pl/sql Area : contains the recently executed Procedures, Functions and Packages.

Control Structures : A memory are for maintaing Latches and Locks.

Dictionary cache  stores all the info of  tables and views in the database, Names and datatypes of the columns in the database, Object and system privileges of all the Users.

All the Shared Pool Structures are maintained by a LRU (least recently Used) algorithm, by which Oracle removes the objects from the shared Pool , Until there is enough free space in the shared Pool to accomodate new Object.

Oracle maintains the stats of all the objects in the shared pool, if any of the memory objects are not used from the last 3 seconds, these memory objects will be aged out and will be removed from the cache.

Library cache is described as the variable component of Shared Pool where as the dictionary cache is termed as fixed component of shared Pool.

The memory structures inside the Library cache are variable in size whereas the dictionary cache and control structures are relatively fixed in size.

The memory allocated for shared Pool is determined by an initialization Parameter called SHARED_POOL_SIZE.

Let us go ahead with shared pool tuning.

As i said shared_pool is a part of Oracle SGA (SYSTEM GLOBAL AREA),  the statistics of shared Pool usage are stored in a dynamic performance view called v$sgastat.

The following query determines the available memory for SHARED_POOL_SIZE in Oracle sga

select sum (bytes)/1024/1024 from v$sgastat where pool=’shared pool’

The above query Provides the shared pool size in mega bytes.

The following query determines the total used memory by shared_Pool in Oracle SGA.

select sum (bytes)/1024/1024 from v$sgastat where pool=’shared pool’ and name not in (’free memory’)

Total Allocated memory for shared_pool in the init.Ora or spfile can be detemined by

select  value from v$parameter where name=’shared_pool_size’

combining the above three queries to consolidated result of allocated shared_pool, Shared_pool_used, Available free space with the shared Pool, Total available percentage, you can write the query as

 select
sum(a.bytes)/(1024*1024))shared_pool_used,
max(b.value)/(1024*1024) shared_pool_size,
sum(a.bytes)/(1024*1024))-
(sum(a.bytes)/(1024*1024)) shared_pool_avail,
((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100
pct_shared_pool_avl
from v$sgastat a, v$parameter b
where (a.pool=’shared pool’
and a.name not in (’free memory’))
and
b.name=’shared_pool_size’

You need to continously monitor the shared Pool with the above query at differnet times. During Peak times and Non peak times to have glance of shared pool usage in the Oracle database.

if the available pct_shared_pool_avl crosses 95% then i think you should re-consider the Process of increasing the shared_pool_size.