当前位置: 首页 > Oracle, oracle 11g > 正文

由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误

今天,用友NC的一个数据库又出问题了,这次遇到的是ORA-04030错误:

Mon Jun 10 22:05:19 2019
Dumping diagnostic data in directory=[cdmp_20190610220519], requested by (instance=1, osid=101922 (J001)), summary=[incident=48330].
Errors in file /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/incident/incdir_48329/ncdb1_j001_101922_i48329.trc:
ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Dumping diagnostic data in directory=[cdmp_20190610220521], requested by (instance=1, osid=101922 (J001)), summary=[incident=48331].
Mon Jun 10 22:06:19 2019
Sweep [inc2][48331]: completed
Sweep [inc2][48329]: completed
Mon Jun 10 22:08:56 2019
Errors in file /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/trace/ncdb1_j001_101922.trc  (incident=48332):
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/incident/incdir_48332/ncdb1_j001_101922_i48332.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

下面是相关的trace文件的头部的部分信息:

*** 2019-06-10 22:12:35.316
*** SESSION ID:(650.32147) 2019-06-10 22:12:35.316
*** CLIENT ID:() 2019-06-10 22:12:35.316
*** SERVICE NAME:(SYS$USERS) 2019-06-10 22:12:35.316
*** MODULE NAME:(DBMS_SCHEDULER) 2019-06-10 22:12:35.316
*** ACTION NAME:(ORA$AT_SQ_SQL_SW_1803) 2019-06-10 22:12:35.316

通过ACTION NAME部分可用看出来,这是AUTO SQL TUNING触发的错误。下面是触发这个错误的SQL。

/* SQL Analyze(650,1) */
select SUM(nvl(nnum, 0)) nnum,
       SUM(nvl(nmny, 0)) nmny,
       datatype,
       pk_brsetting
  from ((((select sum(nvl(nnum, 0)) nnum,
                  sum(nvl(nmny, 0)) nmny,
                  'begin' datatype,
                  '1001D21000000021WO54' pk_brsetting
             from (((select sum(nvl(nabnum, 0)) nnum,
                            sum(nvl(nabmny, 0)) nmny
                       from ia_periodnab
                      where pk_group = '0001A1100000000003Z3'
                        and pk_book = '1001A110000000001GQU'
                        and pk_org = '1001A110000000001GTH'
                        and dr = 0
                        and caccountperiod = '2018-12') union
                   all((select sum(nvl(ninnum, 0)) nnum,
                                sum(nvl(ninmny, 0)) nmny
                           from ia_monthin
                          where pk_group = '0001A1100000000003Z3'
                            and pk_book = '1001A110000000001GQU'
                            and pk_org = '1001A110000000001GTH'
                            and dr = 0
                            and caccountperiod >= '2019-01'
                            and caccountperiod <= '2019-04') union all
                        (select sum(-nvl(noutnum, 0)) nnum,
                                sum(-nvl(noutmny, 0)) nmny
                           from ia_monthout
                          where pk_group = '0001A1100000000003Z3'
                            and pk_book = '1001A110000000001GQU'
                            and pk_org = '1001A110000000001GTH'
                            and dr = 0
                            and caccountperiod >= '2019-01'
                            and caccountperiod <= '2019-04')))) a) union all
        (select sum(nvl(nnum, 0)) nnum,
                  sum(nvl(nmny, 0)) nmny,
                  'end' datatype,
                  '1001D21000000021WO54' pk_brsetting
             from (((select sum(nvl(nabnum, 0)) nnum, sum(nvl(nabmny, 0)) nmny
                       from ia_periodnab
                      where pk_group = '0001A1100000000003Z3'
                        and pk_book = '1001A110000000001GQU'
                        and pk_org = '1001A110000000001GTH'
                        and dr = 0
                        and caccountperiod = '2018-12') union
                   all((select sum(nvl(ninnum, 0)) nnum,
                                sum(nvl(ninmny, 0)) nmny
                           from ia_monthin
                          where pk_group = '0001A1100000000003Z3'
                            and pk_book = '1001A110000000001GQU'
                            and pk_org = '1001A110000000001GTH'
                            and dr = 0
                            and caccountperiod >= '2019-01'
                            and caccountperiod <= '2019-05') union all
                        (select sum(-nvl(noutnum, 0)) nnum,
                                sum(-nvl(noutmny, 0)) nmny
                           from ia_monthout
                          where pk_group = '0001A1100000000003Z3'
                            and pk_book = '1001A110000000001GQU'
                            and pk_org = '1001A110000000001GTH'
                            and dr = 0
                            and caccountperiod >= '2019-01'
                            and caccountperiod <= '2019-05')))) b)) union
        all((select sum(nvl(nnum, 0)) nnum,
                     sum(nvl(nmny, 0)) nmny,
                     'begin' datatype,
                     '1001D21000000021WZP4' pk_brsetting
                from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny
                          from ia_goodsledger
                         where pk_group = '0001A1100000000003Z3'
                           and pk_book = '1001A110000000001GQU'
                           and pk_org = '1001A110000000001GTH'
                           and dr = 0
                           and fintransitflag = 0
                           and caccountperiod < '2019-05') union all
                      (select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny
                          from ia_goodsledger
                         where pk_group = '0001A1100000000003Z3'
                           and pk_book = '1001A110000000001GQU'
                           and pk_org = '1001A110000000001GTH'
                           and dr = 0
                           and fintransitflag = 1
                           and caccountperiod < '2019-05'))) c) union all
             (select sum(nvl(nnum, 0)) nnum,
                     sum(nvl(nmny, 0)) nmny,
                     'end' datatype,
                     '1001D21000000021WZP4' pk_brsetting
                from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny
                          from ia_goodsledger
                         where pk_group = '0001A1100000000003Z3'
                           and pk_book = '1001A110000000001GQU'
                           and pk_org = '1001A110000000001GTH'
                           and dr = 0
                           and fintransitflag = 0
                           and caccountperiod <= '2019-05') union all
                      (select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny
                          from ia_goodsledger
                         where pk_group = '0001A1100000000003Z3'
                           and pk_book = '1001A110000000001GQU'
                           and pk_org = '1001A110000000001GTH'
                           and dr = 0
                           and fintransitflag = 1
                           and caccountperiod <= '2019-05'))) d))) union
        all((((select sum(nvl(nnum, 0)) nnum,
                      sum(nvl(nmny, 0)) nmny,
                      'credit' datatype,
                      '1001D21000000021WO54' pk_brsetting
                 from (select sum(nvl(noutnum, 0)) nnum,
                              sum(nvl(noutmny, 0)) nmny
                         from ia_monthout
                        where pk_group = '0001A1100000000003Z3'
                          and pk_book = '1001A110000000001GQU'
                          and pk_org = '1001A110000000001GTH'
                          and dr = 0
                          and caccountperiod >= '2019-05'
                          and caccountperiod <= '2019-05') g) union all
             (select sum(nvl(nnum, 0)) nnum,
                      sum(nvl(nmny, 0)) nmny,
                      'debit' datatype,
                      '1001D21000000021WO54' pk_brsetting
                 from (select sum(nvl(ninnum, 0)) nnum,
                              sum(nvl(ninmny, 0)) nmny
                         from ia_monthin
                        where pk_group = '0001A1100000000003Z3'
                          and pk_book = '1001A110000000001GQU'
                          and pk_org = '1001A110000000001GTH'
                          and dr = 0
                          and caccountperiod >= '2019-05'
                          and caccountperiod <= '2019-05') h)))) union all
        (select sum(nnum) nnum, sum(nmny) nmny, datatype, pk_brsetting
           from (((select nvl(nnum, 0) nnum,
                          nvl(nmny, 0) nmny,
                          case
                            when fintransitflag = 0 then
                             'debit'
                            else
                             'credit'
                          end datatype,
                          '1001D21000000021WZP4' pk_brsetting
                     from ia_goodsledger
                    where pk_group = '0001A1100000000003Z3'
                      and pk_book = '1001A110000000001GQU'
                      and pk_org = '1001A110000000001GTH'
                      and dr = 0
                      and caccountperiod >= '2019-05'
                      and caccountperiod <= '2019-05'))) o
          group by datatype, pk_brsetting)) s
 group by datatype, pk_brsetting

从这里也可用看出这个错误是由AUTO SQL TUNING触发的,这个错误的原因是遇到了单个进程最多只能使用PGA只有4G的限制导致,下面是trace中的相关信息。

Dumping Work Area Table (level=1)
=====================================

  Global SGA Info
  ---------------

    global target:    12856 MB
    auto target:       8467 MB
    max pga:           2048 MB
    pga limit:         4096 MB
    pga limit known:  0
    pga limit errors:     0

    pga inuse:         3447 MB
    pga alloc:         4705 MB
    pga freeable:       164 MB
    pga freed:        13398681 MB
    pga to free:          0 %
    broker request:       0

    pga auto:             0 MB
    pga manual:           0 MB

    pga alloc  (max):  5882 MB
    pga auto   (max):  2084 MB
    pga manual (max):     1 MB

    # workareas     :     1
    # workareas(max):   106

可用看到,当前的PGA设置是12G,但是单个进程最多只能使用4G,根据MOS文档PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030: (koh-kghu sessi,pmuccst: adt/record) or ORA-04030: (koh-kghucall ,pmucalm coll) Errors (文档 ID 1325100.1)的说明,这是因为操作系统默认单个进程最多只能打开65530个内存映射条目限制的。

[root@SL010A-NCDB1 ~]# cat /proc/sys/vm/max_map_count 
65530

数据库也有和这个相对应的隐含参数_realfree_heap_pagesize_hint,默认是65536。

SQL> col NAME for a30
SQL> col VALUE for a20
SQL> col DESCRIB for a45
SQL> set lines 200
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ
  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%realfree%';

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ---------------------------------------------
_realfree_heap_max_size        32768                minimum max total heap size, in Kbytes
_realfree_heap_pagesize_hint   65536                hint for real-free page size in bytes
_realfree_heap_mode            0                    mode flags for real-free heap
_use_realfree_heap             TRUE                 use real-free based allocator for PGA memory

_realfree_heap_pagesize_hin隐含参数的意思是realfree当前的分配大小是65536 bytes,也就是64K,也就对应操作系统上每个内存映射条目的内存大小是64K,而操作系统上又限制每个进程最多能打开65530个内存映射条目,因此,每个进程使用PGA就不能超过4G。

SQL> select 65536*65530/1024/1024/1024 GB  from dual;

        GB
----------
3.99963379

那么对应的就有两种解决方案,一种是调整操作系统单个进程打开内存映射条目的大小,另一种就是在数据库调整对应的分配单元大小。

操作系统调整单个进程打开内存映射条目大小,需要修改sysctl.conf文件,在最下面增加下面这一行即可。

[root@SL010A-NCDB1 ~]# vi /etc/sysctl.conf
--在最下面增加下面这行
vm.max_map_count=262144

然后通过sysctl –p命令使之生效。这样每个映射条目大小64K,262144个条目就是16G,应该足够用了。

或者在数据库调整realfree的分配单元的大小,但是这个隐含参数是静态参数,需要重启数据库才能生效。

SQL> alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile;

System altered.

然后重启数据库,使之生效。

以上两种方法,不管是修改操作系统的限制还是修改数据库的参数,只修改一个就可用,如果两种方法都修改,需要设置合理的值,避免单个进程使用的内存限制过大,万一有个进程出了问题,可能直接就把内存耗尽了。

对本案例来讲,还有一种解决方法,当然只对本案例有效,因为本案例ORA-04030错误是由于AUTO SQL TUNING导致的,而AUTO SQL TUNING对我来讲又没啥用,完全可用通过禁用AUTO SQL TUNING来解决这个问题。可用通过下马的方法关闭AUTO SQL TUNING。

BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

如果需要开启AUTO SQL TUNING,可用通过下面的方法来开启。

BEGIN
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

针对单个进程只能使用4G的PGA导致的ORA-04030错误的问题,具体还要看是什么原因导致的,本案例特殊,由于是AUTO SQL TUNING触发的,而AUTO SQL TUNING对我这套数据库来讲并没什么用,所以可用通过禁用AUTO SQL TUNING的方式来解决,如果是业务程序的存储过程等PL/SQL导致的这个错误,就不能这样解决了,只能从上面的修改操作系统单个进程打开内存映射条目数或者修改数据库对应的每个映射条目内存分配大小来解决了。

 

 

本文固定链接: http://www.dbdream.com.cn/2019/06/%e7%94%b1%e4%ba%8epga%e5%8d%95%e4%b8%aa%e8%bf%9b%e7%a8%8b%e5%8f%aa%e8%83%bd%e4%bd%bf%e7%94%a84gb%e5%a4%a7%e5%b0%8f%e9%99%90%e5%88%b6%e5%af%bc%e8%87%b4oracle-11g%e7%89%88%e6%9c%acauto-sql-tuning/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2019年06月12日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , ,

由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter