Enable Oracle SGA to enjoy memory larger than 1.7

In short, generally set shmmax> = SGA (32-bit system supports SGA up to 1.7 GB). The default value is 32 MB,
For 64-bit Linux operating systems, set shmmax to be greater than sga_max_size.

The size of the Linux Shared Memory Page is 4 kb. The size of the shared memory segment is an integer multiple of the size of the shared memory page.

The maximum size of a shared memory segment is 16 GB, so the number of pages to be shared is 16 GB/4kb = 16777216kb/4kb = 4194304 (page ), that is, 16 GB physical memory in 64 bit system, set the kernel. shmall = 4194304 meets the requirements (almost twice the original 2097152 ). In this case, you can adjust the shmmax parameter to 16 GB, and modify the sga_max_size and sga_target to 12 GB (the maximum SGA size you want to set, of course, can also be 2 GB ~ 14g and so on. It also coordinates PGA parameters and other memory usage such as OS, and cannot be set to too full, such as 16g ).

The value should be no less than shmmax/page_size. the default value is 2097152. If the total SGA of all instances running on the server does not exceed 8 GB (the maximum and maximum shared memory allocated by the system is 8 GB), you do not need to change the value.

The 32-bit CPU is 2 GB for the system to use for applications in Windows. The SGA memory usage of Oracle cannot exceed 1.7 GB. The following settings are required to expand the SGA to GB or above.

1. Modify on OS
1. Modify the boot. ini file and add/3 GB/PAE:
In this line, multi (0) disk (0) RDISK (0) Partition (1) Windows = "Microsoft Windows"/3 GB/PAE
2. Modify the Windows registry:
Regedit: Find the awe_window_memory parameter in the hkey_local_machinesoftwareoraclehome0 directory and change it to the memory size required by Oracle. For example, 3*1024*1024*1024 for 3G.
If this parameter is not large, you can create a new string named awe_window_memory. The value is the size mentioned above. This value must be large enough and will be reported if it is not enough:
ORA-27102 out of memory
OSD-00034 message 34 not found; Product = RDBMS; facility = sosd
O/s error: (OS 8) Not enough storage is available to process this command
3. modify the management tools in the Windows Control Panel --> Domain Security Policy --> Local Security Policy --> User permission Allocation --> lock the Memory Page (the page is locked in memory) the OS username for Oracle Database startup.

4. Restart your computer

Select name, value from V $ parameter where name in ('sga _ max_size ', 'db _ cache_size', 'shared _ pool_size ', 'shared _ pool_reserved_size ', 'large _ pool_size ', 'java _ pool_size', 'db _ block_size ', 'db _ block_buffers', 'Log _ buffer', 'sort _ area_size ', 'sort _ area_retained_size ', 'hash _ area_size', 'session', 'open _ cursors ')
Order by name;

Alter system set sga_target = 3749707776 scope = spfile;

2. Oracle Modification

1. Backup spfile D: \ diablooracle \ product \ 10.2.0 \ db_1 \ database \ spfiledia. ora
Create pfile = 'C:/initsid. ora 'from spfile;

2. Modify parameters on pfile
Use_indirect_data_buffers = true
Pre_page_sga = true
Db_block_buffers = 262144
S ga_max_size = 2147483648
Sga_target = 2147483648

3. Start the database with pfile, write the parameters back to spfile, and restart the database.
Startup pfile =''
Create spfile from pfile
Shutdown immeidate
Startup

====================

Method 2:

Use the PAE option to enable Oracle to use SGA over 1.7g on 32-bit Windows servers with 4G memory
Large | medium | small
[Do not specify 2009/02/23 14:35 | by Zeus]
1. test environment: HP dl380 G5 server, 4 GB memory, 32-bit Windows 2003 Enterprise Edition

2. Modify the c: \ Boot. ini file and add the/PAE option as follows:

[Boot loader]
Timeout = 30
Default = multi (0) disk (0) RDISK (0) Partition (1) \ WINDOWS
[Operating systems]
Multi (0) disk (0) RDISK (0) Partition (1) \ Windows = "Windows Server 2003, enterprise"/noexecute = optout/fastdetect/PAE
[Code]
3. After windows is restarted, modify the Oracle SGA parameter and restart Oracle to take effect:
[Code]
Alter system reset sga_max_size scope = spfile SID = '*';
Alter system reset sga_target scope = spfile SID = '*';
Alter system set shared_pool_size = 256 m scope = spfile;
Alter system set db_block_buffers' = 320000 scope = spfile;
Alter system set use_indirect_data_buffers = true scope = spfile;
Shutdown immediate;
Startup;

After the PAE mode is used, you cannot use the Oracle automatic memory management function. Therefore, you need to remove the parameters sga_max_size and sga_target and manually manage each memory group, the shared_pool_size parameter and db_block_buffers parameter specify the size and size of the Shared Pool and data buffer respectively. use_indirect_data_buffers specifies that Oracle can use memory segments larger than 4 GB on 32-bit platforms.

4. Check the Oracle SGA statistics to see that the settings have taken effect:

Pre_page_sga
In the Oracle example, only the minimum size of each storage zone is added. The other SGA memory is only allocated as internal memory. Only when the progress touch reaches the corresponding memory will the memory be placed in the physical memory. However, we may also want to allocate all SGA resources to physical memory after the first iteration. In this case, you can set the pre_page_sga parameter to the destination.
The default value of this parameter is false, that is, not all SGA instances are placed in physical memory. When this parameter is set to true, the instance moves all SGA instances to physical memory. It enables the instance to dynamically tune up its maximum performance. However, the synchronization time is also longer (because all SGA instances are placed in physical memory, and the Oracle process needs to touch all SGA instances ).
Lock_sga
In order to ensure that the SGA is stored in the physical memory, you do not need to write in/out, but can control it through the number of lock_sga. When this parameter is set to false, all SGA instances can be stored in the physical memory. Of course, some systems do not support the internal storage setting, so this parameter cannot be used.
 
Sga_target/sga_max_size
The size of each part of the SGA does not need to be determined separately in 10 Gb. The false setting sets sga_target to 200 m, indicating that the maximum SGA size is m for all SGA components, such as the share pool, buffer cache, large pool, and javapool all need to be allocated from them. Oracle will automatically specify the initial values for each component, and adjust the size of each group during the operation.
When sga_target is used, if sga_max_size is not set or sga_target is set to smaller than sga_target, sga_max_size and so on can be automatically adjusted to the SGA-TARGET.SGA_TARGET, the value cannot be greater than the value of sga_max_size. Currently, the number of metric data pushed by sga_max_size is as follows:
Lock_sga = true
Sga_target> = 4G (the larger the physical memory of the system, the better)
Sga_max_size> = 4G (the larger the number of physical storage conditions in the system, the better), but the efficiency has not improved much. It can be seen through the following two queries, the hit rate is 99.2% and 98.8%, indicating that the optimization of Oracle program has reached a high efficiency: Check the high-speed data storage hit rate:
Select sum (PINs) "Data Access", sum (PINs)-sum (reloads) "cachehit ",
(1-sum (reloads)/sum (PINs) * 100 "hit rate" fromv $ librarycache:
Select sum (gets) "dictionary access", sum (gets)-sum (getmisses) "dictionary cache hit ",
(1-sum (getmisses)/sum (gets) * 100 "hit rate" from V $ rowcache