ORACLE 8.0.X
SGA = (db_block_buffers * block size) + (shared_pool_size + large_pool_size + log_buffers) + 1 MB
ORACLE 8.1.X
SGA = (db_block_buffers * block size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1 MB
In theory, SGA accounts for 1/2 of the OS physical memory-1/3. We can adjust it as needed.
I recommend SGA = 0.45 * (OS RAM)
Assume that the server runs ORACLE 8.1.X, the OS memory is 2 GB MEM, and db_block_size is 8192 bytes,
Except for running ORACLE databases, there are no other applications or server software.
In this way, the total SGA is about 921 M (0.45*2048 M ),
Set shared_pool_size to 250 M (250*1024*1024 bytes)
Set database buffer cache to 620 MB (79360*8192 bytes)
The specific parameters in the initorasid. ora file are as follows:
Shared_pool_size = 262144000
#250 M
Db_block_buffers = 79360
#620 M
Log_buffer = 524288
#512 k (128 K * Number of CPUs)
Large_pool_size = 31457280
#30 M
Java _ pool_size = 20971520
#20 M
Sort_area_size = 524288
#512 k (65 k -- 2 M)
Sort_area_retained_size = 524288
# When MTS is used, sort_area_retained_size = sort_area_size
Several parameters in the/etc/system file in SUN Solaris are also related to memory allocation.
Default settings for ORACLE installation: |
Recommended settings: |
Set shmsys: shminfo_shmmax = 4294967295 Set shmsys: shminfo_shmmin = 1 Set shmsys: Sh minfo_shmmni = 100 Set shmsys: shminfo_shmseg = 15 Set semsys: seminfo_semmns = 200 Set semsys: seminfo_semmni = 70 Set ulimit = 3000000 |
Set semsys: seminfo_semmni = 315 Set semsys: seminfo_semms = 300 Set semsys: seminfo_semmns = 630 Set semsys: seminfo_semopm = 315 Set semsys: seminfo_semvmx= 32767 Set shmsys: shminfo_shmmax = 4294967295 Set shmsys: Sh minfo_shmmni = 315 Set shmsys: shminfo_shmseg = 10 Set shmsys: shminfo_shmmin = 1 |
Meanings of these parameters
Shmmax-Maximum number of bytes in the shared memory segment. We recommend that you set the value to a greater value than the physical memory size.
Shmmin-minimum size of the shared memory segment.
Shmmni-Maximum number of shared memory segments.
Shmseg-Maximum number of shared memory segments that each process can allocate.
Shmall-the maximum number of concurrent shared memory segments, which is larger than SGA.
Semmns-the maximum number of traffic signals, which depends on the number of processes in ORACLE.
Semmsl-the maximum number of signals in each traffic signal set.