SQL> desc x$ksmsp;
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2(16)
KSMCHPTR RAW(4)
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2(8)
KSMCHTYP NUMBER
KSMCHPAR RAW(4)
每一行代表shared pool 中的一个chunk
KSMCHSIZ ksmsp chunk size 表示每个chunk的大小 16---3981312
KSMCHCLS ksmsp chunk class 表示类型,主要有4类:
free : free chunks 表示可以自由分配
recr : recreatable chunks 可以被重新创建的chunks
freeable : 可以被释放的chunks
perm : 永久包含的对象
查看一下子池使用及内存情况
SELECT 'shared pool (' || NVL (DECODE (TO_CHAR (ksmdsidx), '0', '0 - Unused', ksmdsidx),'Total') || '):' subpool, SUM (ksmsslen) BYTES, ROUND (SUM (ksmsslen)/ 1048576, 2) mb FROM x$ksmss WHERE ksmsslen > 0
GROUP BY ROLLUP (ksmdsidx) ORDER BY subpool ASC
/
查询一下各个子池的剩余内存
ksmchcom chunkcomment,
DECODE ( ROUND (ksmchsiz / 1000),
0, '0-1K', 1, '1-2K',2, '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',
6, '6-7k', 7, '7-8k',8, '8-9k',9, '9-10k', '> 10K'
) "size",
COUNT (*), ksmchcls status, SUM (ksmchsiz) BYTES
FROM x$ksmsp WHERE ksmchcom = 'free memory'
GROUP BY ksmchidx, ksmchcls, 'sga heap(' || ksmchidx || ',0)',ksmchcom, ksmchcls,
DECODE ( ROUND (ksmchsiz / 1000),
0, '0-1K', 1, '1-2K',2, '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',
6, '6-7k', 7, '7-8k',8, '8-9k',9, '9-10k', '> 10K' );