看了Tanel Poder一篇关于shared pool文章,将其归纳如下:
1、oracle9i 的shared pool引入了sub-pool概念,10.2进一步将sub pool分成4个
sub-sub-pool。具体查看:
SQL>select count(kghluidx) num_pools
from x$kghlu
where kghlushrpool=1
说明:
kghlushrpool:
=1: shared pool subpools
=0: java pool
sub-sub-pool查询:
SQL>select kghluidx, kghludur from x$kghlu
where kghlushrpool=1
KGHLUIDX KGHLUDUR
--------------------- -----------------
2 3
2 2
2 1
2 0
1 3
1 2
1 1
1 0
oracle根据shared pool大小和CPU_COUNT来计算需要分成几个sub pool,例如 oracle9i,shared pool size 大于256MB, cpu_count=4,则分成2个sub pool, 10g中shared pool size 大于512MB,11g应该是1G。
2、可以通过_kghdsidx_count控制sub pool数量
SQL>select n.ksppinm, c.ksppstvl, n.ksppdesc
from x$ksppi n, x$ksppcv c
where n.indx = c.indx
and lower(n.ksppinm) like lower ('%kghdsidx%');
1、oracle9i 的shared pool引入了sub-pool概念,10.2进一步将sub pool分成4个
sub-sub-pool。具体查看:
SQL>select count(kghluidx) num_pools
from x$kghlu
where kghlushrpool=1
说明:
kghlushrpool:
=1: shared pool subpools
=0: java pool
sub-sub-pool查询:
SQL>select kghluidx, kghludur from x$kghlu
where kghlushrpool=1
KGHLUIDX KGHLUDUR
--------------------- -----------------
2 3
2 2
2 1
2 0
1 3
1 2
1 1
1 0
oracle根据shared pool大小和CPU_COUNT来计算需要分成几个sub pool,例如 oracle9i,shared pool size 大于256MB, cpu_count=4,则分成2个sub pool, 10g中shared pool size 大于512MB,11g应该是1G。
2、可以通过_kghdsidx_count控制sub pool数量
SQL>select n.ksppinm, c.ksppstvl, n.ksppdesc
from x$ksppi n, x$ksppcv c
where n.indx = c.indx
and lower(n.ksppinm) like lower ('%kghdsidx%');
NAME VALUE DESCRIPTION5、如何获得各个sub pool中空间信息,ksmdsidx中,0表示内部使用内存,1表示sub pool #1
-------------- ----------- ------------------------
_kghdsidx_count 2 max kghdsidx count
3、每个sub pool由一个share pool latch控制,3~7表示未使用。
SQL>select child#, gets
from v$latch_children
where name = 'shared pool'
order by child#;
CHILD# GETS
---------- ----------
1 29906417
2 24955057
3 2905
4 2905
5 2905
6 2905
7 2905
4、ORA-04031错误
ORA-04031: "unable to allocate n bytes of shared memory
("shared pool", "object_name", "alloc type(2,0)" ...)
type(2,0)表示sub pool# =2, sub sub pool=0
该错误通常造成原因:
a)、shared pool空间太小
b)、空闲空间碎片太多
c)、在不同的sub pool中内存使用/空闲空间使用不平衡
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-606428/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-606428/