Oracle x$ksmlru and x$ksmsp for shared pool monitoring By Burleson Consulting

MOSC Note:146599.1, Diagnosing and Resolving Error ORA-04031, shows tools for identifying shared pool allocations for specific tasks:

There is a fixed table called x$ksmlru that tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation.

If many objects are being periodically flushed from the shared pool then this will cause response time problems and will likely cause library cache latch contention problems when the objects are reloaded into the shared pool.

Oracle notes that monitoring x$ksmlru can be helpful for tracking library cache contention.

   SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

Oracle also published a script using x$ksmsp to give a listing of the RAM heap to see how free space is allocated within the shared pool, and the sizes of available chunks on the freelist for he shared pool, RAM.

select
   '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX,
   10*trunc(KSMCHSIZ/10) "From",
   count(*) "Count" ,
   max(KSMCHSIZ) "Biggest",
   trunc(avg(KSMCHSIZ)) "AvgSize",
   trunc(sum(KSMCHSIZ)) "Total"
from
   x$ksmsp
where
   KSMCHSIZ<140
and
   KSMCHCLS='free'
group by
   KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select
   '1 (140-267)' BUCKET,
   KSMCHCLS,
   KSMCHIDX,
   20*trunc(KSMCHSIZ/20) ,
   count(*) ,
   max(KSMCHSIZ) ,
   trunc(avg(KSMCHSIZ)) "AvgSize",
   trunc(sum(KSMCHSIZ)) "Total"
from
   x$ksmsp
where
   KSMCHSIZ between 140 and 267
and
   KSMCHCLS='free'
group by
   KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select
   '2 (268-523)' BUCKET,
   KSMCHCLS,
   KSMCHIDX,
   50*trunc(KSMCHSIZ/50) ,
   count(*) ,
   max(KSMCHSIZ) ,
   trunc(avg(KSMCHSIZ)) "AvgSize",
   trunc(sum(KSMCHSIZ)) "Total"
from
   x$ksmsp
where
   KSMCHSIZ between 268 and 523
and
   KSMCHCLS='free'
group by
   KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
UNION ALL
select
   '3-5 (524-4107)' BUCKET,
   KSMCHCLS,
   KSMCHIDX,
   500*trunc(KSMCHSIZ/500) ,
   count(*) ,
   max(KSMCHSIZ) ,
   trunc(avg(KSMCHSIZ)) "AvgSize",
   trunc(sum(KSMCHSIZ)) "Total"
from
   x$ksmsp
where
   KSMCHSIZ between 524 and 4107
and
   KSMCHCLS='free'
group by
   KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select
   '6+ (4108+)' BUCKET,
   KSMCHCLS,
   KSMCHIDX,
   1000*trunc(KSMCHSIZ/1000) ,
   count(*) ,
   max(KSMCHSIZ) ,
   trunc(avg(KSMCHSIZ)) "AvgSize",
   trunc(sum(KSMCHSIZ)) "Total"
from
   x$ksmsp
where
   KSMCHSIZ >= 4108
and
   KSMCHCLS='free'
group by
   KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);