转载请注明出处：http://blog.csdn.net/xiaofan23z
通过修改lock_sga和pre_page_sga参数可以保证SGA不被换出到虚拟内存，进而可以提高SGA的使用效率。通过这个小文儿给大家展示一下这两个参数的修改过程，不要太乐观，修改过程是存在“小坎坷”的。
当lock_sga参数设置为TRUE时（默认值是FALSE），可以保证整个SGA被锁定在物理内存中，这样可以防止SGA被换出到虚拟内存。只要设置lock_sga为“TRUE”便可保证SGA被锁定在物理内存中，这里之所以顺便将pre_page_sga参数也设置为“TRUE”，是因为这样可以保证在启动数据库时把整个SGA读入到物理内存中，以便提高系统的效率（会增加系统的启动时间）。
一：修改配置AIX下锁定SGA
1.查看当前操作系统参数
sfc3rc2:/# vmo -L
NAME CUR DEF BOOT MIN MAX UNIT TYPE
DEPENDENCIES
--------------------------------------------------------------------------------
ams_loan_policy n/a 1 1 0 2 numeric D
--------------------------------------------------------------------------------
force_relalias_lite 0 0 0 0 1 boolean D
--------------------------------------------------------------------------------
kernel_heap_psize 64K 0 0 0 16M bytes B
--------------------------------------------------------------------------------
lgpg_regions 0 0 0 0 8E-1 D
lgpg_size
--------------------------------------------------------------------------------
lgpg_size 0 0 0 0 16M bytes D
lgpg_regions
--------------------------------------------------------------------------------
low_ps_handling 1 1 1 1 2 D
--------------------------------------------------------------------------------
maxfree 1088 1088 1088 16 1587K 4KB pages D
minfree
memory_frames
--------------------------------------------------------------------------------
maxperm 1721K 1721K S
--------------------------------------------------------------------------------
maxpin 1598K 1598K S
--------------------------------------------------------------------------------
maxpin% 80 80 80 1 100 % memory D
pinnable_frames
memory_frames
##default number 80%
--------------------------------------------------------------------------------
memory_frames 1984K 1984K 4KB pages S
--------------------------------------------------------------------------------
memplace_data 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_mapped_file 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_shm_anonymous 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_shm_named 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_stack 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_text 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_unmapped_file 2 2 2 1 2 D
--------------------------------------------------------------------------------
minfree 960 960 960 8 1587K 4KB pages D
maxfree
memory_frames
--------------------------------------------------------------------------------
minperm 58766 58766 S
--------------------------------------------------------------------------------
minperm% 3 3 3 1 100 % memory D
--------------------------------------------------------------------------------
nokilluid 0 0 0 0 4G-1 uid D
--------------------------------------------------------------------------------
npskill 20K 20K 20K 1 2M-1 4KB pages D
--------------------------------------------------------------------------------
npswarn 80K 80K 80K 1 2M-1 4KB pages D
--------------------------------------------------------------------------------
numpsblks 2560K 2560K 4KB blocks S
--------------------------------------------------------------------------------
pinnable_frames 1609K 1609K 4KB pages S
--------------------------------------------------------------------------------
relalias_percentage 0 0 0 0 32K-1 D
--------------------------------------------------------------------------------
scrub 0 0 0 0 1 boolean D
--------------------------------------------------------------------------------
v_pinshm 0 0 1 0 1 boolean D
##是否允许pin住内存 默认为0 ，
##current = 当前值 default = 缺省值 reboot = 重新引导值 min = 最小值 max = 最大值 unit = 可调计量单位 type =类型参数：D（指 Dynamic）、S（指 Static）、R（指 ##Reboot）、 B（指 Bosboot）、M（指 Mount）、I（指 Incremental）和 C（指 Connect） dtunable = 从属可调参数的列表
##
Dynamic如果该参数可在任何时间更改
Static如果该参数决不可更改
Reboot如果该参数仅可在重新引导期间更改
Bosboot如果该参数仅可通过运行
bosboot 并重新引导机器来##更改
Mount如果参数的更改仅对将来文件系统或目录安装有效
Incremental如果该参数仅可增量（除了引导时间之外）
Connect如果参数的更改仅对将来的套接字连接有效
##
--------------------------------------------------------------------------------
vmm_default_pspa -1 -1 -1 -1 100 numeric D
--------------------------------------------------------------------------------
wlm_memlimit_nonpg 1 1 1 0 1 boolean D
--------------------------------------------------------------------------------
n/a means parameter not supported by thecurrent platform or kernel
Parameter types:
S= Static: cannot be changed
D= Dynamic: can be freely changed
B= Bosboot: can only be changed using bosboot and reboot
R= Reboot: can only be changed during reboot
C= Connect: changes are only effective for future socket connections
M= Mount: changes are only effective for future mountings
I= Incremental: can only be incremented
d= deprecated: deprecated and cannot be changed
Value conventions:
K= Kilo: 2^10 G = Giga: 2^30 P = Peta: 2^50
M= Mega: 2^20 T = Tera: 2^40 E = Exa: 2^60
转载请注明出处：http://blog.csdn.net/xiaofan23z
2. 修改数据库 系统参数
sfc3rc2:/home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on ThuMar 1 09:50:25 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters,OLAP, Data Mining
and Real Application Testing options
SQL>
SQL>
SQL> alter system set lock_sga=truescope=spfile;
System altered.
SQL> alter system setpre_page_sga=true scope=spfile;
System altered.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
关闭数据库
---------------------------------------------------------------------------------------------------------
3.在root下修改 内存参数
sfc3rc2:/# /usr/sbin/vmo -p -o v_pinshm=1
Setting v_pinshm to 1 in nextboot file
Setting v_pinshm to 1
sfc3rc2:/# usr/sbin/vmo -p-o maxpin%=82
Setting maxpin% to 85 in nextboot file
Setting maxpin% to 85
## Value for tunable maxpin% must begreater than or equal to (((-100 * ##pinnable_frames) / memory_frames) + 100)
## ((-100*1536/10240)+100)=81 SGA=1536M Pmemory=7936M
sfc3rc2:/#
sfc3rc2:/#
sfc3rc2:/# su - oracle
sfc3rc2:/home/oracle$
sfc3rc2:/home/oracle$
sfc3rc2:/home/oracle$ export ORACLE_SID=testdb
4##启动数据库查看已经修改后的系统参数
sfc3rc2:/home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on ThuMar 1 09:53:53 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084400 bytes
Variable Size 385876432 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL>
SQL>
SQL> show parameter lock_sga
NAME TYPE
---------------------------------------------------------------------
VALUE
------------------------------
lock_sga boolean
TRUE
SQL>
SQL>
SQL>
SQL> show parameter pre
NAME TYPE
---------------------------------------------------------------------
VALUE
------------------------------
os_authent_prefix string
ops$
pre_11g_enable_capture boolean
FALSE
pre_page_sga boolean
TRUE
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g EnterpriseEdition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters,OLAP, Data Mining
and Real Application Testing options
转载请注明出处：http://blog.csdn.net/xiaofan23z
5.查看修改后的OS内存参数
sfc3rc2:/# /usr/sbin/vmo -L
NAME CUR DEF BOOT MIN MAX UNIT TYPE
DEPENDENCIES
--------------------------------------------------------------------------------
ams_loan_policy n/a 1 1 0 2 numeric D
--------------------------------------------------------------------------------
force_relalias_lite 0 0 0 0 1 boolean D
--------------------------------------------------------------------------------
kernel_heap_psize 64K 0 0 0 16M bytes B
--------------------------------------------------------------------------------
lgpg_regions 0 0 0 0 8E-1 D
lgpg_size
--------------------------------------------------------------------------------
lgpg_size 0 0 0 0 16M bytes D
lgpg_regions
--------------------------------------------------------------------------------
low_ps_handling 1 1 1 1 2 D
--------------------------------------------------------------------------------
maxfree 1088 1088 1088 16 1587K 4KB pages D
minfree
memory_frames
--------------------------------------------------------------------------------
maxperm 1721K 1721K S
--------------------------------------------------------------------------------
maxpin 1695K 1695K S
--------------------------------------------------------------------------------
maxpin% 85 80 85 1 100 % memory D
pinnable_frames
memory_frames
--------------------------------------------------------------------------------
memory_frames 1984K 1984K 4KB pages S
--------------------------------------------------------------------------------
memplace_data 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_mapped_file 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_shm_anonymous 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_shm_named 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_stack 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_text 2 2 2 1 2 D
--------------------------------------------------------------------------------
memplace_unmapped_file 2 2 2 1 2 D
--------------------------------------------------------------------------------
minfree 960 960 960 8 1587K 4KB pages D
maxfree
memory_frames
--------------------------------------------------------------------------------
minperm 58766 58766 S
--------------------------------------------------------------------------------
minperm% 3 3 3 1 100 % memory D
--------------------------------------------------------------------------------
nokilluid 0 0 0 0 4G-1 uid D
--------------------------------------------------------------------------------
npskill 20K 20K 20K 1 2M-1 4KB pages D
--------------------------------------------------------------------------------
npswarn 80K 80K 80K 1 2M-1 4KB pages D
--------------------------------------------------------------------------------
numpsblks 2560K 2560K 4KB blocks S
--------------------------------------------------------------------------------
pinnable_frames 1219K 1219K 4KB pages S
--------------------------------------------------------------------------------
relalias_percentage 0 0 0 0 32K-1 D
--------------------------------------------------------------------------------
scrub 0 0 0 0 1 boolean D
--------------------------------------------------------------------------------
v_pinshm 1 0 1 0 1 boolean D
--------------------------------------------------------------------------------
vmm_default_pspa -1 -1 -1 -1 100 numeric D
--------------------------------------------------------------------------------
wlm_memlimit_nonpg 1 1 1 0 1 boolean D
--------------------------------------------------------------------------------
n/a means parameter not supported by thecurrent platform or kernel
Parameter types:
S= Static: cannot be changed
D= Dynamic: can be freely changed
B= Bosboot: can only be changed using bosboot and reboot
R= Reboot: can only be changed during reboot
C= Connect: changes are only effective for future socket connections
M= Mount: changes are only effective for future mountings
I= Incremental: can only be incremented
d= deprecated: deprecated and cannot be changed
Value conventions:
K= Kilo: 2^10 G = Giga: 2^30 P = Peta: 2^50
M= Mega: 2^20 T = Tera: 2^40 E = Exa: 2^60
---------------------------------------------------------------------------------
二.测试对比LOCK-SGA和非LOCK-SGA数据库性能
Lock_sga对性能的影响体现在，当系统只是有短暂的PIPO时，因为SGA不会被交换出去
系统仍然可以比较顺畅的运转，而非lock_sga的情况下，一旦OS出现PIPO，系统就会hanging
但是在确定maxpin值上需要谨慎。
以正式庫SFC3RAC1为例 每天上午9点 session=1200上下时，OS会出现短暂的pipo，新的连接会卡住。
但是如果改为lock sga，会不会在session达到1100时 OS就会出现PIPO，这点不好确定，至少测试的结果有这种趋势所以各位如有想将LOCK_SGA改为TRUE的想法 ，一定要谨慎，有可能没有改善反而使性能更差。
Host ENV：AIX 6.1，oracle 10.2.0.4
Pmemory=7936M
SGA=1536M
Maxpin=82%
Value for tunable maxpin% must be greaterthan or equal to (((-100 * ##pinnable_frames) / memory_frames) + 100)
((-100*1536/7936)+100)=81 SGA=1536M Pmemory=7936M default maxpin=80%
1.在测试机上创建一个測試庫（AIX，dbca创建数据库）
2.在另外两台客户机上模拟登录数据库 大并发
登录脚本如下
[oracle@ldbrac1 ~]$ cat teststart.sh
#!/usr/bin/sh
if [ -z "$1" ]
then
echo "Usage: teststart.sh
exit 99
fi
sql="execute dbms_lock.sleep(60);"
LOOP=0
while [ $LOOP -lt $1 ]
do
LOOP=`expr $LOOP + 1`
sqlplus -s test1/test1@testdb< execute dbms_lock.sleep(960);
!
sleep 2
echo $LOOP
done
[oracle@ldbrac1 ~]$
[oracle@ldbrac1 ~]$ ./teststart.sh 10 ##登录10次数据库，每个会话sleep 960s 之后退出
1
2
3
4
5
6
7
8
9
10
[oracle@ldbrac1 ~]$
利用登录脚本对数据库进行1000次登录 并利用vmstat 实时检测数据库状态
测试结果对比图表
Ps：if maxpin=85% when session number>650 OS begin appear PIPO
Lock_sga=true
Session>700 new session can connected normal even though OS have many PIPO
Session>800 new session will be hanging
转载请注明出处：http://blog.csdn.net/xiaofan23z
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 