通过修改lock_sga和pre_page_sga参数可以保证SGA不被换出到虚拟内存，进而可以提高SGA的使用效率。通过这个小文儿给大家展示一下这两个参数的修改过程，不要太乐观，修改过程是存在“小坎坷”的。
当lock_sga参数设置为TRUE时（默认值是FALSE），可以保证整个SGA被锁定在物理内存中，这样可以防止SGA被换出到虚拟内存。只要设置lock_sga为“TRUE”便可保证SGA被锁定在物理内存中，这里之所以顺便将pre_page_sga参数也设置为“TRUE”，是因为这样可以保证在启动数据库时把整个SGA读入到物理内存中，以便提高系统的效率（虽然会增加系统的启动时间）。
env：linux oracle 10.2.0.4
Node2
Linux操作系统对每一个任务在物理内存中能够锁住的最大值做了限制！需要手工进行调整。
[root@ldbrac2 mysql]# su - oracle
[oracle@ldbrac2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on WedFeb 29 10:27:24 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters,OLAP and Data Mining options
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ -----------------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 880M
sga_target big integer 880M
SQL> show parameter lock_a^Hsg^H^[[D^[[D
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ -----------------------------------------
lock_sga boolean FALSE
SQL> alter system set lock_sga=truescope=spfile;
System altered.
SQL> alter system set pre_page_sga=truescope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 1222624 bytes
Variable Size 260048928 bytes
Database Buffers 654311424 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ -----------------------------------------
lock_sga boolean TRUE
SQL>
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ -----------------------------------------
lock_sga boolean TRUE
SQL> showparameter lock_sga
NAME TYPE VALUE
------------------------------------ -----------------------------------------
lock_sga boolean TRUE
Node1
[root@ldbrac1 ~]# ulimit -l unlimited
[root@ldbrac1 ~]# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 32764
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
[root@ldbrac1 ~]# su - oracle
[oracle@ldbrac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on WedFeb 29 10:29:58 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters,OLAP and Data Mining options
SQL> alter system set lock_sga=truescope=spfile;
alter system set lock_sga=true scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILEspecified at startup
SQL> shutdown immediate;
[oracle@ldbrac1 ~]$ cat/u01/app/oracle/product/10.2.0/db_1/dbs/initldbrac1.ora
ldbrac2.__db_cache_size=666894336
ldbrac1.__db_cache_size=734003200
ldbrac1.__java_pool_size=4194304
ldbrac2.__java_pool_size=8388608
ldbrac1.__large_pool_size=4194304
ldbrac2.__large_pool_size=4194304
ldbrac2.__shared_pool_size=226492416
ldbrac1.__shared_pool_size=163577856
ldbrac2.__streams_pool_size=8388608
ldbrac1.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/ldbrac/adump'
*.background_dump_dest='/u01/app/oracle/admin/ldbrac/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=14
*.control_files='+DATA/ldbrac/controlfile/current.260.732154615'
*.core_dump_dest='/u01/app/oracle/admin/ldbrac/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='domain'
*.db_file_multiblock_read_count=16
*.db_name='ldbrac'
*.dispatchers='(PROTOCOL=TCP)(service=test.domain)'
ldbrac1.instance_number=1
ldbrac2.instance_number=2
*.job_queue_processes=10
*.log_archive_dest_1='location=+DATA/ldbrac/archives2mandatory'
*.log_archive_dest_2=''
*.max_shared_servers=5
*.open_cursors=300
*.pga_aggregate_target=307232768
*.processes=200
*.remote_listener='LISTENERS_LDBRAC'
*.remote_login_passwordfile='exclusive'
*.service_names='ldbrac.domain,test.domain'
*.sga_target=922746880
*.shared_servers=3
ldbrac2.thread=2
ldbrac1.thread=1
*.undo_management='AUTO'
ldbrac2.undo_tablespace='UNDOTBS2'
ldbrac1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ldbrac/udump'
修改pfile加入spfile的指定
[oracle@ldbrac1 dbs]$ vi initldbrac1.ora
ldbrac2.__db_cache_size=666894336
ldbrac1.__db_cache_size=734003200
ldbrac1.__java_pool_size=4194304
ldbrac2.__java_pool_size=8388608
ldbrac1.__large_pool_size=4194304
ldbrac2.__large_pool_size=4194304
ldbrac2.__shared_pool_size=226492416
ldbrac1.__shared_pool_size=163577856
ldbrac2.__streams_pool_size=8388608
ldbrac1.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/ldbrac/adump'
*.background_dump_dest='/u01/app/oracle/admin/ldbrac/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=14
*.control_files='+DATA/ldbrac/controlfile/current.260.732154615'
*.core_dump_dest='/u01/app/oracle/admin/ldbrac/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='domain'
*.db_file_multiblock_read_count=16
*.db_name='ldbrac'
*.dispatchers='(PROTOCOL=TCP)(service=test.domain)'
ldbrac1.instance_number=1
ldbrac2.instance_number=2
*.job_queue_processes=10
*.log_archive_dest_1='location=+DATA/ldbrac/archives2mandatory'
*.log_archive_dest_2=''
*.max_shared_servers=5
*.open_cursors=300
*.pga_aggregate_target=307232768
*.processes=200
*.remote_listener='LISTENERS_LDBRAC'
*.remote_login_passwordfile='exclusive'
*.service_names='ldbrac.domain,test.domain'
*.sga_target=922746880
*.shared_servers=3
ldbrac2.thread=2
ldbrac1.thread=1
*.undo_management='AUTO'
ldbrac2.undo_tablespace='UNDOTBS2'
ldbrac1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ldbrac/udump'
SPFILE='+DATA/ldbrac/spfileldbrac.ora'
[oracle@ldbrac1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on WedFeb 29 10:54:19 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initldbrac1.ora';
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 1222624 bytes
Variable Size 180357152 bytes
Database Buffers 734003200 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> show parameter lock
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
db_file_multiblock_read_count integer 16
ddl_wait_for_locks boolean FALSE
distributed_lock_timeout integer 60
dml_locks integer 988
gc_files_to_locks string
lock_name_space string
lock_sga boolean TRUE
SQL>
SQL> alter database open;
Database altered.
SQL>
[oracle@ldbrac1 ~]$ ipcs -m|grep ora
0x00fa5a34 131073 oracle 640 94371840 16
0xb1260140 262146 oracle 640 924844032 41 locked
.lock_sga和pre_page_sga参数在Oracle10gR2官方文档中的描述，供参考。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams097.htm#REFRN10084
LOCK_SGA
Property Description
Parameter type Boolean
Default value false
Modifiable No
Range of values true | false
Basic No
LOCK_SGAlocks the entire SGA into physical memory. It is usually advisable tolock the SGA into real (physical) memory, especially if the use of virtualmemory would include storing some of the SGA using disk space. This parameteris ignored on platforms that do not support it.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams168.htm#REFRN10174
PRE_PAGE_SGA
Property Description
Parameter type Boolean
Default value false
Modifiable No
Range of values true | false
PRE_PAGE_SGAdetermines whether Oracle reads the entire SGA into memory atinstance startup. Operating system page table entries are then prebuilt foreach page of the SGA. This setting can increase the amount of time necessaryfor instance startup, but it is likely to decrease the amount of time necessaryfor Oracle to reach its full performance capacity after startup.
Note:
This setting does not prevent your operating system from paging or swapping theSGA after it is initially read into memory.
PRE_PAGE_SGAcan increase the process startup duration, because every processthat starts must access every page in the SGA. The cost of this strategy isfixed; however, you might simply determine that 20,000 pages must be touchedevery time a process starts. This approach can be useful with someapplications, but not with all applications. Overhead can be significant ifyour system frequently creates and destroys processes by, for example,continually logging on and logging off.
The advantage thatPRE_PAGE_SGAcan afford depends on page size. For example, ifthe SGA is 80 MB in size and the page size is 4 KB, then 20,000 pages must betouched to refresh the SGA (80,000/4 = 20,000).
If the system permits you to set a 4 MB page size, then only 20 pages must betouched to refresh the SGA (80,000/4,000 = 20). The page size is operatingsystem-specific and generally cannot be changed. Some operating systems,however, have a special implementation for shared memory whereby you can changethe page size.
.小结
通过修改lock_sga和pre_page_sga参数值为“TRUE”可以有效的将整个SGA锁定在物理内存中，这样可以有效的提高系统的性能，推荐酌情进行调整。
注意：不同的操作系统对这lock_sga参数的支持情况是不同的，如果操作系统不支持这种锁定，lock_sga参数将被忽略。Windows不支持lock sga。
AIX
Metalink上给出修改参考三步骤：
1．$ /usr/sbin/vmo -r -ov_pinshm=1
2．$ /usr/sbin/vmo -r -omaxpin%=percent_of_real_memory
3．Set LOCK_SGA parameter to TRUE in the init.ora
136
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
