如何使用lock_sga和pre_page_sga参数保证SGA常驻物理内存_好文资源

本篇文章给大家分享的是有关如何使用lock_sga和pre_page_sga参数保证SGA常驻物理内存，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。通过修改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/活动：慈云数据爆款香港服务器，CTG+CN2高速带宽、快速稳定、平均延迟10+ms 速度快，免备案，每月仅需19元！！点击查看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

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官方文档中的描述。

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 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 implem开发云主机域名entation for shared

memory whereby you can change the page size.

通过修改lock_sga和pre_page_sga参数值为“TRUE”可以有效的将整个SGA锁定在物理内存中，这样可以有效的提高系统的性能，推荐酌情进行调整。

注意：不同的操作系统对这lock_sga参数的支持情况是不同的，如果操作系统不支持这种锁定，lock_sga参数将被忽略。