Lock_sga 和 pre_page_sga 参数详解
来源:互联网 发布:ubuntu 修改用户组 编辑:程序博客网 时间:2024/05/28 15:06
Lock_sga 和 pre_page_sga 参数详解
Lock_sga 和pre_page_sga,是两个平时用的不算太多的参数,但是这两个参数平时在优化的时候可能给你带来比较乐观的性能提升,通过修改lock_sga和pre_pga_sga参数可以保证SGA不被换出到swap,进而而已提高SGA的使用效率。
当lock_sga参数的值修改设置为true的时候,可以保证整个sga被锁定在物理内存中,这样可以防止sga被换出到swap中;当然理当需要把pre_page_sga参数也设置为true,只有这样才能保证在数据库启动之初将整个sga读取到物理内存,而不走交换内存,从而有效的提高数据库效率,当然会增加数据库的启动时间。
调整过程如下:
1、 查看lock_sga和pre_page_sga参数的默认值:
SQL> show parameter lock_sga NAME TYPE VALUE------------------------------------ -----------------------------------------lock_sga boolean FALSESQL> show parameter pre_page_sga NAME TYPE VALUE------------------------------------ -----------------------------------------pre_page_sga boolean FALSE
2、 修改这两个参数,因为其是静态参数,故在添加scop=spfile,记录到参数文件,下次启动生效:
SQL> alter system set lock_sga = truescope=spfile; System altered. Elapsed: 00:00:00.03SQL> alter system set pre_page_sga = truescope=spfile; System altered. Elapsed: 00:00:00.02SQL>
3、 重启数据库
SQL> startup ORA-27102: out of memory Linux-x86_64 Error: 12: Cannot allocate memorySQL>
发现数据库现在起不来了,想想看,这是什么原因导致启动失败呢,其实很简单,Linux操作系统对每一个任务在内存中能锁住的值做了限制,只需手工修改即可。
4、 处理解决ora-27102及Linux-x86_64 Error:12问题:
[oracle@woo ~]$ ulimit -acore file size (blocks, -c) 0data seg size (kbytes, -d) unlimitedscheduling priority (-e) 0file size (blocks, -f) unlimitedpending signals (-i) 32768max locked memory (kbytes, -l) 32max memory size (kbytes, -m) unlimitedopen files (-n) 65536pipe size (512 bytes, -p) 8POSIX message queues (bytes, -q) 819200real-time priority (-r) 0stack size (kbytes, -s) 10240cpu time (seconds, -t) unlimitedmax user processes (-u) 16384virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited
从上面信息我们可以看到,一个任务可以锁住的物理内存最大值为32KB,这个值根本没法满足我们sga的大小。那么我们需要对该值进行修改,使其适应及满足相关要求。
5、 修改有两种方法:
5.1、临时生效可以切换到root用户通过如下命令,进行修改:
[root@woo ~]# ulimit -l32[root@woo ~]# ulimit -l unlimited[root@woo ~]# ulimit -acore file size (blocks, -c) 0data seg size (kbytes, -d) unlimitedscheduling priority (-e) 0file size (blocks, -f) unlimitedpending signals (-i) 32768max locked memory (kbytes, -l) unlimitedmax memory size (kbytes, -m) unlimitedopen files (-n) 1024pipe size (512 bytes, -p) 8POSIX message queues (bytes, -q) 819200real-time priority (-r) 0stack size (kbytes, -s) 10240cpu time (seconds, -t) unlimitedmax user processes (-u) 32768virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited
5.2 让其永久生效的方法:
[root@woo ~]# vi /etc/security/limits.conf 最底部添加如下两行oracle soft memlock unlimitedoracle hard memlock unlimited
6、 修改系统限制成功后,启动数据库,可以正常Open,但是时间更长了。
SQL> startupORACLE instance started. Total System Global Area 6081740870 bytesFixed Size 1220844 bytesVariable Size 197136148 bytesDatabase Buffers 406847488 bytesRedo Buffers 2969600 bytesDatabase mounted.Database opened.SQL>
7、 参考:
关于locak_sga 及pre_page_sga 参数在ORACLE10gR2官方文档中的介绍:
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 to lockthe SGA into real (physical) memory, especially if the use of virtual memorywould include storing some of the SGA using disk space. This parameter isignored on platforms that do not support it.
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 at instancestartup. Operating system page table entries are then prebuilt for each page ofthe SGA. This setting can increase the amount of time necessary for instancestartup, but it is likely to decrease the amount of time necessary for Oracleto reach its full performance capacity after startup.
Note:
This setting does not prevent your operating system from paging orswapping the SGA after it is initially read into memory.
PRE_PAGE_SGAcan increase the process startup duration, because every process thatstarts 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 everytime a process starts. This approach can be useful with some applications, butnot with all applications. Overhead can be significant if your systemfrequently creates and destroys processes by, for example, continually loggingon and logging off.
The advantage thatPRE_PAGE_SGAcan afford depends on page size. For example, if the SGA is 80 MB in sizeand 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 mustbe touched 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.
关于Metalink对于该介绍,参考如下地址:
http://space.itpub.net/?uid-20674423-action-viewspace-itemid-767830
http://blog.csdn.net/wuweilong/article/details/9774337
http://www.prudentwoo.com/archives/1207
慎重提醒:不同操作系统对着lock_sga参数的支持情况是不同的,如果操作系统不支持这种锁定,参数将忽略。
- Lock_sga 和 pre_page_sga 参数详解
- lock_sga和pre_page_sga参数
- lock_sga 和pre_page_sga 参数测试
- 使用lock_sga和pre_page_sga参数保证SGA常驻物理内存
- 使用lock_sga和pre_page_sga参数保证SGA常驻物理内存 .
- 使用lock_sga和pre_page_sga参数保证SGA常驻物理内存
- 使用lock_sga和pre_page_sga参数保证SGA常驻物理内存 .
- Oracle 使用lock_sga和pre_page_sga参数保证SGA常驻物理内存
- lock_sga and pre_page_sga设置
- 配置lock_sga/pre_page_sga/hugepages
- oracle锁定sga物理内存:pre_page_sga和lock_sga
- 内存足够但是监视主机还是使用虚拟内存LOCK_SGA和PRE_PAGE_SGA,HugePages
- pre_page_sga参数总结
- linux lock_sga
- PRE_PAGE_SGA 的玩法
- PRE_PAGE_SGA 的玩法[转]
- mencoder和ffmpeg参数详解
- c3p0实例和参数详解
- 多线程编程中条件变量和虚假唤醒(spurious wakeup)的讨论
- android 捕捉异常
- [LeetCode]Jump Game II、Jump Game
- oracle数据类型
- 子对话框获取父对话框句柄几种解决方法
- Lock_sga 和 pre_page_sga 参数详解
- 使用BeanUtils操作javaBean类
- Windows RPC编程入门(已动手实践按红体字部分修改可以正常运行)
- 最大流水题集锦
- C语言中的整数自动转换原则
- NYOJ 23题 取石子(一)
- (一)验证微信公众平台
- NSPR学习笔记(一):PR_LOG的使用
- [Android] App在三星某些机子上闪退:"不保留活动"