本篇文章給大家分享的是有關(guān)如何使用lock_sga和pre_page_sga參數(shù)保證SGA常駐物理內(nèi)存，小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著小編一起來(lái)看看吧。

創(chuàng)新互聯(lián)不只是一家網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司；我們對(duì)營(yíng)銷(xiāo)、技術(shù)、服務(wù)都有自己獨(dú)特見(jiàn)解，公司采取“創(chuàng)意+綜合+營(yíng)銷(xiāo)”一體化的方式為您提供更專(zhuān)業(yè)的服務(wù)！我們經(jīng)歷的每一步也許不一定是最完美的，但每一步都有值得深思的意義。我們珍視每一份信任，關(guān)注我們的成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)質(zhì)量和服務(wù)品質(zhì)，在得到用戶(hù)滿(mǎn)意的同時(shí)，也能得到同行業(yè)的專(zhuān)業(yè)認(rèn)可，能夠?yàn)樾袠I(yè)創(chuàng)新發(fā)展助力。未來(lái)將繼續(xù)專(zhuān)注于技術(shù)創(chuàng)新，服務(wù)升級(jí)，滿(mǎn)足企業(yè)一站式成都全網(wǎng)營(yíng)銷(xiāo)推廣需求，讓再小的成都品牌網(wǎng)站建設(shè)也能產(chǎn)生價(jià)值！

通過(guò)修改lock_sga和pre_page_sga參數(shù)可以保證SGA不被換出到虛擬內(nèi)存，進(jìn)而可以提高SGA的使用效率。通過(guò)這個(gè)小文兒給大家展示一下這兩個(gè)參數(shù)的修改過(guò)程，不要太樂(lè)觀，修改過(guò)程是存在“小坎坷”的。

當(dāng)lock_sga參數(shù)設(shè)置為T(mén)RUE時(shí)（默認(rèn)值是FALSE），可以保證整個(gè)SGA被鎖定在物理內(nèi)存中，這樣可以防止SGA被換出到虛擬內(nèi)存。只要設(shè)置lock_sga為“TRUE”便可保證SGA被鎖定在物理內(nèi)存中，這里之所以順便將pre_page_sga參數(shù)也設(shè)置為“TRUE”，是因?yàn)檫@樣可以保證在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)把整個(gè)SGA讀入到物理內(nèi)存中，以便提高系統(tǒng)的效率（雖然會(huì)增加系統(tǒng)的啟動(dòng)時(shí)間）。

修改過(guò)程如下：

1.查看lock_sga和pre_page_sga參數(shù)的默認(rèn)值
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.注意：兩個(gè)參數(shù)都是靜態(tài)參數(shù)。確認(rèn)之。
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”選項(xiàng)修改之，成功。
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.重新啟動(dòng)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

“小坎坷”出現(xiàn)了，想一想，這里為什么會(huì)啟動(dòng)失敗呢？
原因很簡(jiǎn)單，Linux操作系統(tǒng)對(duì)每一個(gè)任務(wù)在物理內(nèi)存中能夠鎖住的最大值做了限制！需要手工進(jìn)行調(diào)整。

5.“ORA-27102”及“Cannot allocate memory”問(wèn)題處理
1）使用“ulimit -a”命令獲得“max locked memory”的默認(rèn)大小
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

可見(jiàn)，一個(gè)任務(wù)可以鎖住的物理內(nèi)存最大值是32kbytes，這么小的值根本無(wú)法滿(mǎn)足我們SGA的5G大小需求。

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

（2）切換到root用戶(hù)
ora10g@secDB /home/oracle$ su - root
Password:

（3）在root用戶(hù)下嘗試修改，成功。
[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.調(diào)整完操作系統(tǒng)的限制后，我們?cè)俅螄L試啟動(dòng)數(shù)據(jù)庫(kù)。成功！
[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參數(shù)在Oracle 10gR2官方文檔中的描述。

LOCK_SGA

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	No
Range of values	true | false
Basic	No

LOCK_SGA locks 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

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	No
Range of values	true | false

PRE_PAGE_SGA determines 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.

Note:

This setting does not prevent your operating system from paging or swapping the SGA after it is initially read into memory.

PRE_PAGE_SGA can 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 that PRE_PAGE_SGA can 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.

通過(guò)修改lock_sga和pre_page_sga參數(shù)值為“TRUE”可以有效的將整個(gè)SGA鎖定在物理內(nèi)存中，這樣可以有效的提高系統(tǒng)的性能，推薦酌情進(jìn)行調(diào)整。
注意：不同的操作系統(tǒng)對(duì)這lock_sga參數(shù)的支持情況是不同的，如果操作系統(tǒng)不支持這種鎖定，lock_sga參數(shù)將被忽略。

以上就是如何使用lock_sga和pre_page_sga參數(shù)保證SGA常駐物理內(nèi)存，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

當(dāng)前文章：如何使用lock_sga和pre_page_sga參數(shù)保證SGA常駐物理內(nèi)存
文章URL：http://vcdvsql.cn/article46/gjggeg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、、企業(yè)建站、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、域名注冊(cè)、移動(dòng)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)