x$kghlu、x$ksmss、x$ksmsp关于共享池

 

今天是2013-10-14，开始进行shared pool 原理学习，至此在网上看到几个重要内部视图，在此摘录一下。感觉还是挺好的。

 

1：x$kghlu可以查看各子池的chunk使用情况
主要字段：
kghlushrpool：
=1: shared pool subpools
=0: java pool
KGHLUIDX：sub-pool
KGHLUDUR：sub-sub-pool
kghlurcr：recurrent chunks
kghlutrn：transient chunks
kghlufsh：flushed chunks
kghluops：pins and releases chunks
kghlunfu：ora-4031 chunks
kghlunfs：last error chunk size

SQL> select INDX,INST_ID,KGHLUIDX,KGHLUDUR,KGHLUSHRPOOL,KGHLUNFU from x$kghlu where KGHLUSHRPOOL=1;

INDX INST_ID KGHLUIDX KGHLUDUR KGHLUSHRPOOL KGHLUNFU
---------- ---------- ---------- ---------- ------------ ----------
0 1 7 0 1 5851
1 1 6 0 1 63070
2 1 5 0 1 71666
3 1 4 0 1 9528
4 1 3 0 1 69590
5 1 2 0 1 6186
6 1 1 0 1 62860

7 rows selected.

2：x$ksmss可以获得各个sub pool中空间信息
主要字段：
ksmdsidx：
=0：表示内部使用内存
=1：表示sub pool
SQL> select ksmdsidx sub_pool,sum(ksmsslen) bytes from x$ksmss where ksmsslen>0 group by ksmdsidx order by sub_pool;

SUB_POOL BYTES
---------- ----------
1 1509950680
2 1543505304
3 4206462328
4 1577059984
5 1610618352
6 1442841952
7 1543505736

7 rows selected.

SQL>

具体的子池使用及内存情况
SELECT 'shared pool (' || NVL (DECODE(TO_CHAR(ksmdsidx), '0', '0 - Unused', ksmdsidx),'Total') || '):' sub_pool,
SUM(ksmsslen) BYTES, ROUND(SUM(ksmsslen)/1048576, 2) mb
FROM x$ksmss WHERE ksmsslen > 0 GROUP BY ROLLUP (ksmdsidx) ORDER BY sub_pool ASC;

查看各个子池的剩余内存，有可能是零散的碎片：
SELECT sub_pool, NAME, SUM(BYTES), ROUND(SUM(BYTES)/1048576, 2) mb FROM (
SELECT 'shared pool (' || DECODE(TO_CHAR(ksmdsidx), '0', '0 - Unused', ksmdsidx)|| '):' sub_pool, ksmssnam NAME, ksmsslen BYTES
FROM x$ksmss WHERE ksmsslen>0 AND LOWER(ksmssnam) LIKE LOWER('%free memory%'))
GROUP BY subpool, NAME ORDER BY sub_pool ASC, SUM(BYTES) DESC;

3：x$ksmsp可以监控共享池碎片的情况，每条记录表示共享池的每个chunk，此视图可能过度耗用cpu。
主要字段：
ksmchcom：每个chunk的注释
ksmchsiz：每个chunk的大小
ksmchcls：表示类型
=FREE：不包含任何对象的chunk，可以任意分配
=RECR：chunk中对象可以移出内存，需要时可以重建
=FREEABL：会话周期等调用的对象，其chunk随后可以全部或部分提前释放
=PERM：chunk包含永久对象，不能独立释放

select count(1) from x$ksmsp;

select ksmchsiz,ksmchcom from x$ksmsp where ksmchsiz>10000 and ksmchcom like '%PL/SQL%'; <ksmchcom: free memory / permanent memor>

select a.ksmchcom,sum(a.chunk) chunk,sum(a.recr) recr,sum(a.freeabl) freeabl,sum(a.sum) sum
from (
select ksmchcom,count(ksmchcom) chunk,decode(ksmchcls,'recr',sum(ksmchsiz),null) recr,
decode(ksmchcls,'freeabl',sum(ksmchsiz),null) freeabl,sum(ksmchsize) sum from x$ksmsp group by ksmchcom,ksmchcls) a
group by a.ksmchcom;

SELECT ksmchidx "SubPool", 'sga heap(' || ksmchidx || ',0)' sga_heap,
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' );

备注：此例7个子池，7个shared pool latch
SQL> select child#, gets from v$latch_children where name = 'shared pool' order by child#;

CHILD# GETS
---------- ----------
1 1965105585
2 2083784908
3 1304423260
4 2197281833
5 2205897428
6 2001096469
7 1999951480

7 rows selected.

SQL>