How To Determine The Default Number Of Subpools Allocated During Startup

Applies to:

Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.3 – Release: 10.1 to 10.2
Information in this document applies to any platform.

Goal

This document explains the algorithm for determining the default number of subpools allocated during database startup.

Solution

Subpools are allocated during while instance starts up using the algorithm explained below.

A) Based on number of CPUs. At least 1 subpool per 4 cpu’s
@ max_lists_by_cpu = ((cpu_count-1) >> 2) + 1;
i.e,
1-4 CPU’s = 1 subpool
5-8 CPU’s = 2 subpools
9-12 CPU’s = 3 subpools

B) Based on shared pool size .

1. When Manual Shared Memory Management  is  used:

When shared memory is managed manually,at least 1 subpool is configured for every 256MB (in 10g databases whereas it is 128MB for 9i databases) allocated for shared_pool_size parameter. From 11g, this is changed to 512MB.

2. When Automatic Shared Memory Management (ASMM) is used.

When SGA_TARGET parameter is set, for calculating the number of subpools, we assume that the maximum value to be allocated for shared_pool_size will be 50% of the target size. Therefore if sga_target>= 1024 we assume shared_pool_size can grow up to 512 which allows 2 subpools, if sga_target <1024 then assumption is shared_pool_size will not reach 512Mb so 1 subpool.

The actual number of subpool allocated will be the minimum of the above results. ie, min(A,B).

However, this hold good only if:

1. The hidden parameter _kghdsidx_count is not set explicitly in the parameter file.

2. The value for shared_pool_size and cpu_count should not be altered dynamically. Since subpools
are allocated during instance startup, changing the values dynamically wont alter the number of
subpools accordingly.

You might be required to change the number of subpools in case the subpools are not adequately sized and your application is hitting ORA-4031. Patch 4994956  ensures that the 1 subpool is allocated for every 512MB instead of 256MB with respect to the number of CPUs. If the patch is not available for your platform, please contact Oracle Support for the same.

© 2009, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. How to determine granule size
2. Gather DBMS_STATS Default parameter
3. ORA-1092 During Startup Using AUM (Automatic Undo Management)