Saturday, June 27, 2009

Initialization Parameter Sizing

Sizing recommendations based on the active Applications user counts.

Parameter Name Development/Test Instance 11 – 100 Users 101 – 500 Users 501 – 1,000 Users 1,001 – 2,000 Users [1]
processes 200 200 800 1200 2500
sessions 400 400 1600 2400 5000
db_block_buffers 20000 50000 150000 250000 400000
sga_target [2] 1G 1G 2G 3G 14G
shared_pool_size (csp) N/A N/A N/A 1800M 3000M
shared_pool_reserved_size (csp) N/A N/A N/A 180M 300M
shared_pool_size (no csp) 400M 600M 800M 1000M 2000M
shared_pool_reserved_size (no csp) 40M 60M 80M 100M 100M
pga_aggregate_target 1G 2G 4G 10G 20G
Total Memory Required [3] ~ 2 GB ~ 3 GB ~ 6 GB ~ 13 GB ~ 25 GB

***For instances that need to support more than a thousand concurrent Applications users, better to use the Oracle 64-bit Database in order to support a larger SGA.

***The parameter sga_target should be used for Oracle 10g or 11g based environments such as Release 12. This replaces the parameter db_cache_size, which was used in Oracle9i based environments. Also, it is not necessary to set the parameter undo_retention for 10g or 11g based systems, since undo retention is set automatically as part of automatic undo tuning.

***The total memory required refers to the amount of memory required for the database instance and associated memory including the SGA and the PGA. You should ensure that your system has sufficient available memory in order to support the values provided above. The values provided above should be adjusted based on available memory so as to prevent paging and swapping.

1 comment:

  1. how to set the below parameters

    shared_pool_size (csp)
    shared_pool_reserved_size (csp)
    shared_pool_size (no csp)
    shared_pool_reserved_size (no csp)