通過修改lock_sga和pre_page_sga引數可以保證SGA不被換出到虛擬記憶體，進而可以提高SGA的使用效率。通過這個小文兒給大家展示一下這兩個引數的修改過程，不要太樂觀，修改過程是存在“小坎坷”的。

當lock_sga引數設定為TRUE時（預設值是FALSE），可以保證整個SGA被鎖定在實體記憶體中，這樣可以防止SGA被換出到虛擬記憶體。只要設定lock_sga為“TRUE”便可保證SGA被鎖定在實體記憶體中，這裡之所以順便將pre_page_sga引數也設定為“TRUE”，是因為這樣可以保證在啟動資料庫時把整個SGA讀入到實體記憶體中，以便提高系統的效率（雖然會增加系統的啟動時間）。

修改過程如下：

1.檢視lock_sga和pre_page_sga引數的預設值
sys@ora10g> show parameter sga

NAME            TYPE                 VALUE
--------------- -------------------- -----------------
lock_sga        boolean              FALSE
pre_page_sga    boolean              FALSE
sga_max_size    big integer          5G
sga_target      big integer          5G

2.注意：兩個引數都是靜態引數。確認之。
sys@ora10g> alter system set lock_sga=true;
alter system set lock_sga=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


sys@ora10g> alter system set pre_page_sga=true;
alter system set pre_page_sga=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

3.使用“scope=spfile”選項修改之，成功。
sys@ora10g> alter system set lock_sga=true scope=spfile;

System altered.

sys@ora10g> alter system set pre_page_sga=true scope=spfile;

System altered.

4.重新啟動Oracle使spfile的修改生效
sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora10g> startup;
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory

“小坎坷”出現了，想一想，這裡為什麼會啟動失敗呢？
原因很簡單，Linux作業系統對每一個任務在實體記憶體中能夠鎖住的最大值做了限制！需要手工進行調整。

5.“ORA-27102”及“Cannot allocate memory”問題處理
1）使用“ulimit -a”命令獲得“max locked memory”的預設大小
ora10g@secDB /home/oracle$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 266239
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

可見，一個任務可以鎖住的實體記憶體最大值是32kbytes，這麼小的值根本無法滿足我們SGA的5G大小需求。

2）將其修改為無限大
（1）oracle使用者是無法完成這個修改任務的
ora10g@secDB /home/oracle$ ulimit -l unlimited
-bash: ulimit: max locked memory: cannot modify limit: Operation not permitted

（2）切換到root使用者
ora10g@secDB /home/oracle$ su - root
Password:

（3）在root使用者下嘗試修改，成功。
[root@secDB ~]# ulimit -l unlimited
[root@secDB ~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 266239
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
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 2047
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

6.調整完作業系統的限制後，我們再次嘗試啟動資料庫。成功！
[root@secDB ~]# su - oracle
ora10g@secDB /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 20 22:21:40 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

NotConnected@> startup;
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  2080320 bytes
Variable Size             905970112 bytes
Database Buffers         4445962240 bytes
Redo Buffers               14696448 bytes
Database mounted.
Database opened.

7.lock_sga和pre_page_sga引數在Oracle 10gR2官方文件中的描述，供參考。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams097.htm#REFRN10084

LOCK_SGA

LOCK_SGAlocks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is 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

PRE_PAGE_SGAdetermines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.

PRE_PAGE_SGAcan increase the process startup duration, because every process that starts must access every page in the SGA. The cost of this strategy is fixed; however, you might simply determine that 20,000 pages must be touched every time a process starts. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your 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, if the SGA is 80 MB in size and the page size is 4 KB, then 20,000 pages must be touched 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 be touched to refresh the SGA (80,000/4,000 = 20). The page size is operating system-specific and generally cannot be changed. Some operating systems, however, have a special implementation for shared memory whereby you can change the page size.

8.小結
通過修改lock_sga和pre_page_sga引數值為“TRUE”可以有效的將整個SGA鎖定在實體記憶體中，這樣可以有效的提高系統的效能，推薦酌情進行調整。
注意：不同的作業系統對這lock_sga引數的支援情況是不同的，如果作業系統不支援這種鎖定，lock_sga引數將被忽略。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25736250/viewspace-703615/，如需轉載，請註明出處，否則將追究法律責任。