当前位置: 首页 > BASIC > 正文

测试会话PGA值分配与限制PGA最大值

    下面是测试一下单个进程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配置为2Gpga配置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
已经达到效果。

本文固定链接: http://www.htz.pw/2015/03/17/%e6%b5%8b%e8%af%95%e4%bc%9a%e8%af%9dpga%e5%80%bc%e5%88%86%e9%85%8d%e4%b8%8e%e9%99%90%e5%88%b6pga%e6%9c%80%e5%a4%a7%e5%80%bc.html | 认真就输

该日志由 huangtingzhong 于2015年03月17日发表在 BASIC 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 测试会话PGA值分配与限制PGA最大值 | 认真就输
关键字: ,