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
from x$ksmlru
where ksmlrsiz > 1000
order by ksmlrsiz;
Shared Pool Memory Allocated
-- Shared pool memory allocated.
select sum(ksmchsiz)||' bytes' "TotSharPoolMem"
from x$ksmsp;
Fragmentation of shared pool
-- Fragmentation of Shared Pool.
set verify off
column PctTotSPMem for all
select ksmchcls "ChnkClass",
sum(ksmchsiz) "SumChunkTypeMem",
Max(ksmchsiz) "LargstChkofThisTyp",
count(1) "NumOfChksThisType",
round((sum(ksmchsiz)/tot_sp_mem.totspmem),2)*100||'%' "PctTotSPMem
from x$ksmsp,
(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
from v$shared_pool_reserved;
what is the reason to publish sql queries in jpeg?????
Issue migrating to a different hosting platform. Let us know if you see any more like this though.
Apologies for the inconvenience!
Edited to include copy-able SQL.
USE caution when you try these on prod environment as they are related to critical memory structures and you could bring down the instance with an ORA-600. personal experience had an internal fatal error with 504 idle bit session argument.
AFAIK, when you run this query while operating you will face many of locks on the screen.
reading and gathering current shared pool status makes locks on the database.