You've got quite large shared pool, it is true that quering x$ksmsp
is not recommended due to excesive latch activity, but Oracle uses this
query to narrow down the shared pool issues:
SELECT * FROM ( SELECT ksmchidx subpool, 'DURATION:'||ksmchdur duration,
'CMNT: '||ksmchcom cmnt, ksmchcls type, SUM(ksmchsiz) total_size,
COUNT(*) allocations, 'AVG:' avg,AVG(ksmchsiz) average_size,
MIN(ksmchsiz) min_size, 'MAX:' max,MAX(ksmchsiz) max_size FROM x$ksmsp
GROUP BY ksmchidx, ksmchdur, ksmchcom, ksmchcls ORDER BY SUM(ksmchsiz))
WHERE (ROWNUM < 21 AND total_size > 10000 AND cmnt NOT LIKE
'CMNT:free%') OR cmnt LIKE 'CMNT: free%'order by 3,4,1,2
/
Your problem could be related to subpool defragmentation which seems to
be often the case with exchange partition (generally partition
maintenance?).
And indeed the shared pool can grow like crazy even when auto memory
management is inactive (_target parameters = 0), it is documented
SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled
(MEMORY_TARGET/SGA_TARGET=0) (Doc ID 1269139.1)
Check the resize ops like Tim said via query on :
V$MEMORY_RESIZE_OPS / V$SGA_RESIZE_OPS
Regards.
