AskDba.org Weblog

Today I will discuss about Shared pool subpools which have been introduced in release 9iR2 and higher (To be precise >9.2.0.5) and can be controlled by a hidden parameter “_kghdsidx_count “

Most of you would not have come across this term/parameter unless you have faced ORA-4031 error.

As you all are aware that Shared Pool (part of SGA) contains the library cache, the dictionary cache , buffers for parallel execution messages, and control structures and also UGA in case of Shared Server Configuration(If Large pool is configured then UGA would be stored in Large Pool). The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Shared Pool Latch is used to protect and achieve the concurrency in Shared Pool. Subpools were introduced so as to have multiple Latches and relieve load on the single Latch.

Oracle determines the pools to be configured based on few factors

1) One subpool for every 4 CPU’s
2)Minimum amount of memory in each subpool.

Actually this varies from release to release. Please find below minimum size requirement for various versions

9.2.0.5 – 9.2.0.8 – 128Mb per subpool
10.1.0.1 – 10.2.0.3 – 256 Mb per subpool
10.2.0.4 > – 512 Mb
11.1.0.6 > 512 Mb

This means if on 10.2.0.2 , you have allocated shared_pool_size to 600M on a 12 CPU machine, you will have 2 Subpools to satisfy both the conditions.

In case you are using SGA_TARGET, the condition changes for calculating the number of subpools. In this case we assume that the maximum value to be allocated for shared_pool_size will be 50% of the target size. So considering SGA_TARGET is set to 600 M in above case, shared_pool_size will be assumed to be 300M so we will have only 1 Subpool.

3)Maximum of 7 subpools can be present

Note that even Shared Pool reserved area and Large Pool will be having subpools equal to the subpools in Shared Pool. We cannot set different value of subpools for these pools.

To determine number of subpools, you can use below query

SQL> select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and nam.ksppinm like ‘%kghdsidx%’ order by 1 ;

NAME VALUE
—————————— ——————–
_kghdsidx_count 4

Above query indicates that there are 4 subpools

In case you get ORA-4031 and trace file gets generated, then the trace file can also be used to know the number of subpools configured. To do this search on “Memory Utilization of Subpool”
e.g
Memory Utilization of Subpool 1
========================
free memory 10485760
Memory Utilization of Subpool 2
========================
free memory 20971520

This means that there are two subpools configured for your database.

Now there is no recommendation from Oracle on Number of subpools, though they suggest having 500M as minimum subpool size. I will say that in case you are not facing serious Shared pool Latch contention, 2 subpools should be sufficient (though I believe most of contention issues can be solved by tuning the application). But if anyone has seen performance improvement by changing the number of subpools then I would like him/her to share their findings.

Anyways to change the number of subpools, we need to set parameter _kghdsidx_count in pfile or spfile and restart the database

Spfile
——–
alter system set “_kghdsidx_count”=1 scope=spfile;

Restart of database is required as it is a Static parameter.