下面是测试一下单个进程PGA最大值与怎么限制PGA进程的最大值，下面测试中单个进程内存分配达到了12G，但是在生产环境中，见过最大PGA分配只有接近2G。
1，环境介绍
|
www.htz.pw > select * from v$version where rownum<3;
BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi PL/SQL Release 10.2.0.4.0 – Production
www.htz.pw > !lsb_release -a LSB Version: :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch Distributor ID: RedHatEnterpriseAS Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8) Release: 4 Codename: NahantUpdate8 |
2，查看当前参数配置
|
set lines 200 col instance_name for a20 col parameter for a30 SELECT a.ksppinm AS parameter, c.ksppstvl AS instance_value FROM x$ksppi a, x$ksppsv c WHERE a.indx = c.indx AND a.ksppinm LIKE ‘/_%’ ESCAPE ‘/’ AND a.ksppinm IN (‘_smm_max_size’, ‘_pga_max_size’) UNION SELECT a.name, a.display_value FROM v$parameter a WHERE a.name = ‘pga_aggregate_target’ /
PARAMETER INSTANCE_VALUE —————————— ——————– _pga_max_size 2147483648 _smm_max_size 1048576 pga_aggregate_target 15G |
这里可以看到_smm_max_size配置为1G，_pga_max_size配置为2G，pga配置15G。按网上多数的说话，单个进程PGA内存分配不应该操过1G。
运用程序，大量分配PGA内存
www.htz.pw > exec testTabMem.addToTab(500000000);
BEGIN testTabMem.addToTab(500000000); END;
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16408 bytes (koh-kghu
sessi,pl/sql vc2)
www.htz.pw > @dump_sess_pga.sql
USERNAME SID SPID LOGON_TIME PROGRAM PID_REMOTE Used MB Allocated MB Freeable MB Max MB
--------------- ----- -------- ------------ ---------------------- ------------ -------- ------------ ----------- --------
SYS 150 10932 17-MAR-15 sqlplus@orcl9i (TNS V1 10931 1.0 1.0 .0 1.0
SYS 145 10895 17-MAR-15 10894 4084.0 4091.0 .0 4091.0
- pick one of the SID's from the above list and enter it when prompted
- for ORASID to see a breakdown summary
Enter value for sid: 145
PID Category Allocated bytes Used bytes Max allocated bytes
-------------------- --------------- ----------------- ---------- --------------------
20 SQL 53488 20896 433640
20 PL/SQL 35296 28744 507024
20 Other 4289645581 428964558
这里可以看到，触发ORA-04030报错时，PGA已经分析了4091.0M，远远大于1G。
===============================================
PGA memory detail for pid 20, OS pid 10895
===============================================
2332225976 bytes,142141 chunks: "pl/sql vc2 "
koh-kghu sessi ds=0x2a97cb4228 dsprt=0x2a97c96738
1050098328 bytes,65459 chunks: "free memory "
session heap ds=0x2a97c96738 dsprt=0x668af40
888729104 bytes,54166 chunks: "pmucalm coll "
koh-kghu sessi ds=0x2a97cb4228 dsprt=0x2a97c96738
5235440 bytes,65443 chunks: "free memory "
top uga heap ds=0x668af40 dsprt=(nil)
180456 bytes, 3 chunks: "free memory
3，修改max_map_count参数
关于max_map_count参数的说明就见linux内核的官方文档，上面有详细说明，下面将max_map_count增长，允许12G的段。
修改下面的参数
vm.max_map_count=200000
重启数据库
再次测试
www.htz.pw > @sess_pga.sql
Enter value for sid:
SESS_SERIAL PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX
OSPID MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED
------------------------- -------- --------- ------------ ------- --------------- -------- -------
145:7:11133 0 1 0 2 SQL 0 0
PL/SQL 0 0
Freeable 0 0
Other 0
146:5:11138 0 0 0 0 PL/SQL 0 0
Other 0
147:7:11131 8692 8707 0 8707 SQL 0 0
PL/SQL 0 0
Other 8707
148:1:11129 0 1 1 2 SQL 0 0
PL/SQL 0 0
Freeable 1 0
Other 0
159:3:11081 12472 12493 0 12493 SQL 0 0
PL/SQL 0 0
Other 12492
|
4，通过10261来限制PGA内存分配
通过10261实例限制最大内存使用量
event = "10261 trace name context forever, level <PGA Size (KB)>"
这里通过alter system set events来配置没有生效，最后通过参数文件来配置生效。
event="10261 trace name context forever, level 1048576"
www.htz.pw > exec testTabMem.addToTab(500000000);
BEGIN testTabMem.addToTab(500000000); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [723], [65472], [top uga heap], [],
[], [], [], []
ORA-06512: at "SYS.TESTTABMEM", line 10
ORA-06512: at line 1
这里看到PGA分配1024就报错了
www.htz.pw > @sess_pga.sql
Enter value for sid: 159
PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX
MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED
-------- --------- ------------ ------- --------------- -------- -------
1021 1024 0 1024 SQL 0 0
PL/SQL 0 0
Other 1024
alert报下面的错误
Tue Mar 17 22:52:37 2015
Errors in file /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_11406.trc:
ORA-00600: internal error code, arguments: [723], [65472], [top uga heap], [], [], [], [], []
|
5，通过ulimit来限制PGA内存分配
下面通过ulimit来限制 www.htz.pw > !ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) 1048576 file size (blocks, -f) unlimited pending signals (-i) 1024 max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) 1024 open files (-n) 65536 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 2047 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited 将data seg size更改为1G，重启数据库 www.htz.pw > exec testTabMem.addToTab(500000000); BEGIN testTabMem.addToTab(500000000); END; * ERROR at line 1: ORA-00600: internal error code, arguments: [723], [65472], [top uga heap], [], [], [], [], [] ORA-06512: at "SYS.TESTTABMEM", line 10 ORA-06512: at line 1 报错时，已经分配12G内存，通过ulimit来限制失败 |
5，通过max_map_count参数实现
vm.max_map_count=16384
[oracle@www.htz.pw ~]$ulimit -a
www.htz.pw > !ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) 1024
open files (-n) 65536
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 2047
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
重启数据库
www.htz.pw > exec testTabMem.addToTab(500000000);
BEGIN testTabMem.addToTab(500000000); END;
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16408 bytes (koh-kghu
sessi,pl/sql vc2)
这里可以看到，在1G的时候就触发报错了。
www.htz.pw > @sess_pga.sql
Enter value for sid: 159
PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX
MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED
-------- --------- ------------ ------- --------------- -------- -------
1017 1019 0 1019 SQL 0 0
已经达到效果。
|
测试会话PGA值分配与限制PGA最大值：等您坐沙发呢！