This aricle takes a closer look at the shared pool using a plethora of queries to help you conduct detailed investigations when needed. The shared pool may be fragmented if you observe a large number of entries in X$KSMLRU, particularly a large number of them with small KSMLRSIZ values, or if you observe many chunks of type “free” in X$KSMSP. Contrast this with a large number of entries in X$KSMLRU with medium to high values of KSMLRSIZ, which is not likely to be a symptom of a fragmented shared pool; rather, it indicates that large PL/SQL packages and/or Java classes need to be kept in the shared pool and possibly also that the shared pool itself is too small, and perhaps that application code is not being effectively shared (or some combination thereof).
In identifying the problem, take the time to monitor the application code use over time to find out which code the user sessions are attempting to load. Network with application users, developers, designers, and application vendors. The queries in the following listings will help you find contention and fragmentation issues.
Finding contention and fragmentation issues
--Names of and sessions for shared pool allocations causing contention.
select ksmlrhon, ksmlrsiz, ksmlrses
where ksmlrsiz > 1000
order by ksmlrsiz;
Shared Pool Memory Allocated
-- Shared pool memory allocated.
select sum(ksmchsiz)||' bytes' "TotSharPoolMem"
Fragmentation of shared pool
-- Fragmentation of Shared Pool.
set verify off
column PctTotSPMem for all
select ksmchcls "ChnkClass",
(select sum(ksmchsiz) TotSPMem from x$ksmsp) tot_sp_mem
group by ksmchcls, tot_sp_mem.TotSPMem
order by sum(ksmchsize);
Information about SHARED_POOL_RESERVED_SIZE
-- Information regarding shared_pool_reserved_size.
select free_space, free_count, max_free_size, max_used_size, request_misses, max_miss_size