I will try to describe, how the subpools are defined during the startup.
The calculation of the number of subpools depends on:
1. Parameter _kghdsidx_count --> It overrides all other settings
2. The number of CPUs
3. The size of the shared pool
Every 4 CPUs should have one subpool:
1-4 CPUs - 1 pool
5-8 CPUs - 2 pools
9-12 CPUs - 3 pools
13-16 CPUs - 4 pools
Manual memory management:
The number of subpools depends on the size of shared_pool. In the 11g, minimum size of the subpool is 512MB. So if we have a shared pool sized to 1.8 GB we will have 3 subpools each size 600MB:
1800MB/512MB=3,5 ---> 3 subpools ---> 1800MB/3subpools=600MB each pool.
Automatic memory management:
If the value of the shared_pool_size is explicitly set, the same formula as for manula managment will be used.
Otherwise following rule will be used:
If c set
then subpool_count=C
else
subpool_count=min(A,B)
C - the _kghdsidx_count parameter
A - the numbers of CPUs
B - the shared_pool_size
Example:
Oracle11gR2 is installed
the system has 16 CPUs
MEMORY_TARGET is set to 4.2GB
no SGA_TARGET nor SHARED_POOL_SIZE is explicitly set
number of CPUs=16 ---> 4 Subpools
Memory_target=4.2GB and no sga_target is set ---> 60% of memory_target will be used for SGA (2.52GB). no shared_pool_size is specified, so shared pool will have max of 50% of SGA (1.26GB). 1.26GB/512MB=2,4 --> 2 subpools each with 1260MB/2=630MB. So now the min value of: min(4,2) --> 2. So we will have 2 subpools.
Oracle11gR2 is installed
the system has 16 CPUs
MEMORY_TARGET is set to 96GB
SGA_TARGET is set to 50GB
number of CPUs=16 --> 4 subpools
SGA_TARGET=50GB, no shared_pool_size so shared pool will have max 50% from SGA=25GB. 25GB/512MB=48 subpools. Maximum number of subpools=7. So now the minimum values of 4 and 7 --> min(4,7)--> we will have 4 subpools.
Oracle11gR2 is installed
the system has 16 CPUs
MEMORY_TARGET is set to 60GB
SGA_TARGET is set to 30GB
_kghdsidx_count is set to 4
Parameter _kghdsidx_count is set to 4 and it will override all other settings.
To check the number of subpools you can use the x$kghlu table. The letters in the name of the table means:
[k]ernel [g]eneric [h]eap [lu]-least recently used. There is one row per each subpool. In this table you can also see the subpools of the java_pool. Column KGHLUSHRPOOL will define the pool. 0 is the java pool, 1 is the shared pool.
select
kghluidx
, kghludur
, decode(KGHLUSHRPOOL,'0','java pool','shared pool') POOL
, kghlufsh
, kghluops
, kghlurcr
, kghlutrn
, kghlunfu
, kghlunfs
, kghlumxa
, kghlumes
, kghlumer
, kghlurcn
, kghlurmi
, kghlurmz
, kghlurmx
from
x$kghlu
order by
3
, kghluidx
, kghludur;
1. Parameter _kghdsidx_count --> It overrides all other settings
2. The number of CPUs
3. The size of the shared pool
Every 4 CPUs should have one subpool:
1-4 CPUs - 1 pool
5-8 CPUs - 2 pools
9-12 CPUs - 3 pools
13-16 CPUs - 4 pools
Manual memory management:
The number of subpools depends on the size of shared_pool. In the 11g, minimum size of the subpool is 512MB. So if we have a shared pool sized to 1.8 GB we will have 3 subpools each size 600MB:
1800MB/512MB=3,5 ---> 3 subpools ---> 1800MB/3subpools=600MB each pool.
Automatic memory management:
If the value of the shared_pool_size is explicitly set, the same formula as for manula managment will be used.
Otherwise following rule will be used:
If c set
then subpool_count=C
else
subpool_count=min(A,B)
C - the _kghdsidx_count parameter
A - the numbers of CPUs
B - the shared_pool_size
Example:
Oracle11gR2 is installed
the system has 16 CPUs
MEMORY_TARGET is set to 4.2GB
no SGA_TARGET nor SHARED_POOL_SIZE is explicitly set
number of CPUs=16 ---> 4 Subpools
Memory_target=4.2GB and no sga_target is set ---> 60% of memory_target will be used for SGA (2.52GB). no shared_pool_size is specified, so shared pool will have max of 50% of SGA (1.26GB). 1.26GB/512MB=2,4 --> 2 subpools each with 1260MB/2=630MB. So now the min value of: min(4,2) --> 2. So we will have 2 subpools.
Oracle11gR2 is installed
the system has 16 CPUs
MEMORY_TARGET is set to 96GB
SGA_TARGET is set to 50GB
number of CPUs=16 --> 4 subpools
SGA_TARGET=50GB, no shared_pool_size so shared pool will have max 50% from SGA=25GB. 25GB/512MB=48 subpools. Maximum number of subpools=7. So now the minimum values of 4 and 7 --> min(4,7)--> we will have 4 subpools.
Oracle11gR2 is installed
the system has 16 CPUs
MEMORY_TARGET is set to 60GB
SGA_TARGET is set to 30GB
_kghdsidx_count is set to 4
Parameter _kghdsidx_count is set to 4 and it will override all other settings.
To check the number of subpools you can use the x$kghlu table. The letters in the name of the table means:
[k]ernel [g]eneric [h]eap [lu]-least recently used. There is one row per each subpool. In this table you can also see the subpools of the java_pool. Column KGHLUSHRPOOL will define the pool. 0 is the java pool, 1 is the shared pool.
select
kghluidx
, kghludur
, decode(KGHLUSHRPOOL,'0','java pool','shared pool') POOL
, kghlufsh
, kghluops
, kghlurcr
, kghlutrn
, kghlunfu
, kghlunfs
, kghlumxa
, kghlumes
, kghlumer
, kghlurcn
, kghlurmi
, kghlurmz
, kghlurmx
from
x$kghlu
order by
3
, kghluidx
, kghludur;