X$KSMSP 视图 (类似堆Heapdump信息)
使用这个视图能找出当前分配的空闲空间，有助于理解共享池碎片的程度。如我们在前面的描述，查找为游标分配的足够的大块内存的第一个地方是空闲列表( free list)。 下面的语句显示了空闲列表中的大块内存:
SELECT '0 (<140)' bucket, ksmchcls, 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, 10 * TRUNC (ksmchsiz / 10)
UNION ALL
SELECT '1 (140-267)' bucket, ksmchcls, 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, 20 * TRUNC (ksmchsiz / 20)
UNION ALL
SELECT '2 (268-523)' bucket, ksmchcls, 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, 50 * TRUNC (ksmchsiz / 50)
UNION ALL
SELECT '3-5 (524-4107)' bucket, ksmchcls, 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, 500 * TRUNC (ksmchsiz / 500)
UNION ALL
SELECT '6 (4108 )' bucket, ksmchcls, 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, 1000 * TRUNC (ksmchsiz / 1000);
4. ORA-04031 错误与 Large Pool
大池是个可选的内存区，为以下的操作提供大内存分配：
评论加载中…
 |