SGA set up on the point summary
This summary is not directed at special cases, only the server OS + ORACLE exists only as an example, if there are other applications to consider, as appropriate,
This is because recently wrote this repetitive problem caused by too much
First of all do not superstitious STS, SG, OCP, EXPERT, and so are any proposals, saying the percentage of memory
Grasp the basic principle is, data buffer normally be the largest extent possible, shared_pool_size to moderate, log_buffer typically to a few hundred K to the almost 1 M
Set before, we must first clear two issues
1: remove OS and some other expenses, can use the memory ORACLE how
2: oracle is 64 bit or 32 bit, 32bit SGA usually have 1.7 G restrictions (some of the treatment or WINDOWS OS on a specific set can support more than 2 G to even reach 3.7 G, I no experience in this area)
Below is my windows2000 under the oracle:
SQL> select * from v $ version;
BANNER
-------------------------------------------------- --------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL / SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL>
32 bit windows on the existence of the restrictions, such as AIX, HP UNIX, and so have a clear 64 BIT OS and ORACLE version, 32 bit oracle can be installed in the 64 bit os on, 64 bit oracle can not be installed in the 32 bit OS on
No matter oracle is 32 bit ORACLE or 64 bit, assuming that there has not been applied bind var the use of the situation, we can not set shared_pool_size too large, would normally be controlled at 200 M - 300M, if ORACLE ERP is a class of the use of a lot of Storage function, package, or great system, may consider increasing shared_pool_size, but if more than 500 M could be dangerous to 1 G may be a serious burden on the CPU, system or even paralysis. Therefore, if more than 300 M shared_pool_size also hit rate is not high, then the application should not blindly to find the reasons for the increase in memory, shared_pool_size too large an increase of the major management burden and latch costs.
log_buffer: 128K ---- 1M little problems between the normally should not be too much
large_pool_size: If we do not set MTS, usually in RMAN, OPQ will be used to, but in 10 M --- 50M should almost. If the set MTS, because UGA put large_pool_size the reason, this time based on the largest number and sort_ares_size session, and other set of parameters, we must increase large_pool_size settings, can be considered for the session * (sort_area_size + 2M). Would like to remind here that we must not use MTS, we do not advocate the use of MTS, especially at the same time online users is less than 500 cases.
java_pool_size: Without the use of java, to 30 M is usually enough
data buffer, done in front of the set, who can be provided to the oracle of memory, should be addressed to the data buffer = (db_block_size * db_block_buffers)
In 9 i can be in the db_cache_size
There are two important parameters we need to pay attention to
sort_area_size and hash_area_size
These two parameters are of a non-MTS under the PGA, does not belong to SGA, is a separate allocation for each session, on our servers in addition to OS + SGA, must consider these two parts
(****): OS use memory + SGA + session * (sort_area_size + hash_area_size + 2M) <for the good of physical RAM
This can be summed up, assuming that the oracle is 32 bit, server RAM than 2 G, you pay attention to the PGA Tour, are proposed
shared_pool_size + data buffer + large_pool_size + java_pool_size <1.6G
Re-specific, attention to meet the above (****) based on the principles of reference are set
If the 512 M RAM
Proposed shared_pool_size = 50M, data buffer = 200M
If 1 G RAM
shared_pool_size = 100M, data buffer = 500M
If the 2 G
shared_pool_size = 150M, data buffer = 1.2G
To the physical memory has no relationship with the parameters of the
The assumption that 64 bit ORACLE
Memory 4 G
shared_pool_size = 200M, data buffer = 2.5G
Memory 8 G
shared_pool_size = 300M, data buffer = 5G
Memory 12G
shared_pool_size = 300M ----- 800M, data buffer = 8G
Just above the reference value, different systems may be relatively large differences, the need to adjust in accordance with specific circumstances. Set the parameters proposed in the same time, init use lock_sga, in different platforms may have a different approach, making SGA locked in physical memory instead be placed in the SWAP, so good for efficiency
On the memory settings, have to conduct careful adjustment, the role of small, but may statspack information and v $ system_event, v $ sysstat, v $ sesstat, v $ latch view, and other information to consider fine-tuning