AIX下LOCK_SGA配置以及性能测试（模拟大并发）

本文主要是介绍AIX下LOCK_SGA配置以及性能测试（模拟大并发），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

转载请注明出处：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

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> 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:/# su - 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

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> show parameter lock_sga

NAME TYPE

---------------------------------------------------------------------

VALUE

------------------------------

lock_sga boolean

TRUE

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 <TIMES>"
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 ~]$