sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle 会话PGA值分配与限制PGA最大值测试案例

时间:2016-11-06 18:52   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库内存管理测试文档,详细介绍会话PGA值分配与限制PGA最大值测试案例。

下面是测试一下单个进程PGA最大值与怎么限制PGA进程的最大值,下面测试中单个进程内存分配达到了12G,但是在生产环境中,见过最大PGA分配只有接近2G。

1,环境介绍

oracleplus.net> 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
oracleplus.net> !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内存
oracleplus.net> 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)

oracleplus.net> @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

重启数据库
再次测试
oracleplus.net> @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 "

这里通过alter system set events来配置没有生效,最后通过参数文件来配置生效。
event="10261 trace name context forever, level 1048576"

oracleplus.net> 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就报错了

oracleplus.net> @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内存分配

Oracle oracleplus.net
下面通过ulimit来限制
oracleplus.net> !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,重启数据库

oracleplus.net> 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
oracleplus.net> !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

重启数据库
oracleplus.net> 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的时候就触发报错了。
oracleplus.net> @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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 会话PGA值分配与限制PGA最大值测试案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1187.html

Oracle研究中心

关键词:

Oracle PGA配置笔记

Oracle PGA限制最大值测试