Oracle parameter Configuration

　　

One, the Oracle large_pool_size size setting value how much

Java_pool_size:
Specifies, in bytes, the size of the Java storage pool, which is used to store Java methods and classes that define representations in shared memory, and Java objects that are ported to Java session space at the end of a call.
Large_pool_size:
Specifies the size of the allocation heap for a large pool, which can be used as session memory by a shared server, as a message buffer for parallel execution, and as a disk I/O buffer for Rman backup and recovery.
Large_pool are typically used for shared server (MTS), parallel computing, or Rman backup recovery. The parameter that sets its size is: large_pool_size
If you do not set Mts,large_pool usually in Rman, OPQ will be used, large_pool_size settings in 10M---50M should be almost.
If you have multiple channels assigned to the Rman backup recovery, consider the formulas given in the reference.

Large_pool_size = Number_of_allocated_channels *

(16MB (4 * size_of_tape_buffer)).
If MTS is set, then because of UGA put to large_pool_size, this time according to the maximum number of sessions and Sort_ares_size parameters set, you must increase the large_pool_size settings,
can be considered as session * (Sort_area_size 2M). It is important to note that MTS is not required, and we do not advocate the use of MTS, especially if the number of online users is less than 500.

Ii. a summary of the SGA setting

This summary is not specific to the exception, only the server exists in the OS ORACLE as an example, if there are other applications please consider as appropriate
And that's because this recurring problem has happened so much lately.
First do not superstition Sts,sg,ocp,expert and so on any advice, memory percentage of the argument
The basic principle is that data buffer can usually be as large as possible, shared_pool_size to moderate, Log_buffer usually up to hundreds of K to 1M is almost.
Before setting up, the first thing to be clear is 2 questions
1: How much memory can be used for Oracle, except for OS and some other overhead
2:oracle is 64bit or bit,32bit usually the SGA has a 1.7G limit (some OS processing or specific settings on Windows can support 2G or even 3.7G, I do not have this experience)
Below is the Oracle under my windows2000:

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>

There are 32bit limitations on windows, such as AIX, HP UNIX, and so on, with a clear version of 64BIT OS and Oracle, 32bit Oracle can be installed on 64bit OS, and a bit Oracle cannot be installed on the
Regardless of whether Oracle is a three-bit oracle or a bit, assuming that the application does not have a good use of bind Var, it cannot set shared_pool_size too large and should normally be controlled in 200m--300m,
If the ORACLE ERP class uses many stored procedure functions, packages, or large systems, you can consider increasing the shared_pool_size, but if exceeding 500M can be dangerous, reaching 1G can be a serious burden on the CPU,
The system is even paralyzed. So shared_pool_size if more than 300M also hit rate is not high, then should be from the application to find the reason rather than blindly increase the memory, shared_pool_size too large mainly increase the management burden and latch overhead.

log_buffer:128k----1M is usually not a big problem.
Large_pool_size: If MTS is not set, it is usually used in RMAN, OPQ, but 10M---50M should be the same. If MTS is set, then because UGA is put into large_pool_size,
This time according to the maximum number of sessions and Sort_ares_size parameters set, you must increase the large_pool_size settings, you can consider the

Session * (Sort_area_size 2M).
It is important to note that MTS is not required, and we do not advocate the use of MTS, especially if the number of online users is less than 500.

Java_pool_size: If you don't use Java, it's usually enough for 30M.

Data buffer, after making the previous settings, any memory that can be supplied to Oracle should be given to data buffer = (Db_block_size * db_block_buffers)

Can be db_cache_size in 9i

There are 2 important parameters we need to be aware of:

Sort_area_size and Hash_area_size

These two parameters in the non-MTS are belong to the PGA, not the SGA, is allocated for each session separately, on our server in addition to the OS SGA, we must consider these two parts
(* * *): OS uses memory SGA session* (sort_area_size hash_area_size 2M) < Total physical RAM for good
This comes down to the assumption that Oracle is a one-bit, server ram greater than 2G, note the case of your PGA, is recommended
Shared_pool_size Data buffer large_pool_size Java_pool_size < 1.6G
Re-embodiment, attention to meet the above (* * *) on the basis of the principle can refer to the following settings

If 512M RAM
Recommended shared_pool_size = 50M, data buffer = 200M

If 1G RAM
Shared_pool_size = 100M, data buffer = 500M

If 2G
Shared_pool_size = 150M, data buffer = 1.2G

Physical memory is no longer related to parameters.

Assuming that the "bit ORACLE"

Memory 4G
Shared_pool_size = 200M, data buffer = 2.5G

Memory 8G
Shared_pool_size = 300M, data buffer = 5G

Memory 12G
Shared_pool_size = 300M-----800M, Data buffer = 8G

The above is only the reference value, different systems may be relatively large, need to adjust according to specific circumstances. It is recommended to use LOCK_SGA in init while setting parameters, which may have different ways on different platforms.
Making the SGA locked in physical memory without being put into SWAP is good for efficiency.
About the memory settings, to be carefully adjusted, plays a small role, but can be based on statspack information and V$system_event,v$sysstat,v$sesstat,v$latch and other view information to consider fine-tuning.

Oracle parameter Configuration