Oracle Session & MEMORY_TARGET 测试（三）

原创布衣 2024-12-16

292

背景

Oracle Session 相关介绍（一）介绍过Session&Process 的关系，在这里介绍测试Process与MEMORY_TARGET的关系变化。

测试环境介绍

系统版本

[oracle@db ~]$ cat /etc/redhat-release 
CentOS release 6.7 (Final)

内存

[oracle@db ~]$ free -g
             total       used       free     shared    buffers     cached
Mem:            62         62          0          3          0          4
-/+ buffers/cache:         57          5
Swap:            9          0          9

CPU信息

[oracle@db ~]$ cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
4
[oracle@db ~]$ cat /proc/cpuinfo| grep "cpu cores"| uniq
cpu cores       : 6
[oracle@db ~]$ cat /proc/cpuinfo| grep "processor"| wc -l
24

Oracle 版本:Release 11.2.0.3.0
SGA

SQL> select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
 (select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual);
NAME                      TOTAL       USED       FREE    PCTUSED
-------------------- ---------- ---------- ---------- ----------
SGA                  3950.27734    2704.22    1246.06      68.46

SQL> select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
NAME                      TOTAL       USED       FREE    PCTUSED
-------------------- ---------- ---------- ---------- ----------
PGA                        1376     155.45    1220.55       11.3

AMM的内存管理

AMM：automatic memory management(11g新加的特性），让数据库完全管理SGA与PGA的大小，只需要设置目标内存大小初始化参数（MEMORY_TARGET）和可选的最大内存大小初始化参数（MEMORY_MAX_TARGET）。然后，实例将调整为目标内存大小，并根据需要在系统全局区域（SGA）和实例程序全局区域（实例PGA）之间重新分配内存。由于目标内存初始化参数是动态的，因此可以随时更改目标内存大小，而无需重新启动数据库。最大内存大小是一个上限，因此不会意外地将目标内存大小设置得过高，并为Oracle数据库实例留出足够的内存，以防将来您想增加实例总内存。由于某些SGA组件不能轻易收缩或必须保持最小大小，因此该实例还阻止将目标内存大小设置得太小。

processes=30000

SQL> show parameter memory_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
memory_target                        big integer 3968M
SQL> alter system set processes=30000 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 4368M

解决：ORA-00838，调整：MEMORY_TARGET =4368M

SQL> alter system set memory_target=4368M     scope=spfile;
System altered.
SQL> alter system set MEMORY_MAX_TARGET=4368M scope=spfile; 
System altered.
SQL> alter system set processes=30000 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

点击这里了解：ORA-27300：OS system dependent operation:semget failed with status: 28

ASMM的内存管理

Oracle 10g时，Orale推出ASMM(Automatic Shared Memory Managed),实现了SGA和PGA各自内部的自调节。
建议使用ASMM，因为Linux启用hugepage特性需要使用ASMM，而不能使用AMM。

AMM调整为ASMM

1、memory_target 设置为 0

SQL> alter system set memory_target=0     scope=spfile;
SQL> alter system set MEMORY_MAX_TARGET=0 scope=spfile;

2、修改sga大小：

SQL> alter system set sga_max_size=3968M scope=spfile;
SQL> alter system set sga_target=3968M scope=spfile;

3、修改pga大小：

alter system set pga_aggregate_target=600M scope=spfile;

4、启库失败

SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 6442450944 cannot be set to more than MEMORY_MAX_TARGET 0.

启库失败提示：GA_TARGET的值应该小于MEMORY_MAX_TARGET；
原因：因为MEMORY_MAX_TARGET被“显式”的赋值，与SGA_TARGET赋值相冲突;

5、解决方案
生成pfile,然后删除memory_max_target、memory_target两个参数重新启库

SQL> create pfile from spfile;
File created.
[oracle@db dbs]$ vim inittwo.ora
......
*.global_names=FALSE
*.memory_max_target=0   <== 删除
*.memory_target=0       <== 删除
*.open_cursors=300
......

6、启库成功

SQL> startup nomount pfile='/u01/oracle/11.2.0.3/product/dbs/inittwo.ora';
ORACLE instance started.
Total System Global Area 6413680640 bytes
Fixed Size                  2240344 bytes
Variable Size            2533359784 bytes
Database Buffers         3858759680 bytes
Redo Buffers               19320832 bytes
SQL> set line 800
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 6413680640 bytes
Fixed Size                  2240344 bytes
Variable Size            2533359784 bytes
Database Buffers         3858759680 bytes
Redo Buffers               19320832 bytes
Database mounted.
Database opened.

7、已调整为ASMM

SQL> show parameter memory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3968M
sga_target                           big integer 3968M
SQL>  show parameter pga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 600M

ASMM测试：Process=40000

SQL> alter system set processes=40000 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates