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


    感谢dbdream

    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/

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

     

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

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

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

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

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

    001 /* SQL Analyze(650,1) */
    002 select SUM(nvl(nnum, 0)) nnum,
    003        SUM(nvl(nmny, 0)) nmny,
    004        datatype,
    005        pk_brsetting
    006   from ((((select sum(nvl(nnum, 0)) nnum,
    007                   sum(nvl(nmny, 0)) nmny,
    008                   'begin' datatype,
    009                   '1001D21000000021WO54' pk_brsetting
    010              from (((select sum(nvl(nabnum, 0)) nnum,
    011                             sum(nvl(nabmny, 0)) nmny
    012                        from ia_periodnab
    013                       where pk_group = '0001A1100000000003Z3'
    014                         and pk_book = '1001A110000000001GQU'
    015                         and pk_org = '1001A110000000001GTH'
    016                         and dr = 0
    017                         and caccountperiod = '2018-12') union
    018                    all((select sum(nvl(ninnum, 0)) nnum,
    019                                 sum(nvl(ninmny, 0)) nmny
    020                            from ia_monthin
    021                           where pk_group = '0001A1100000000003Z3'
    022                             and pk_book = '1001A110000000001GQU'
    023                             and pk_org = '1001A110000000001GTH'
    024                             and dr = 0
    025                             and caccountperiod >= '2019-01'
    026                             and caccountperiod <= '2019-04') union all
    027                         (select sum(-nvl(noutnum, 0)) nnum,
    028                                 sum(-nvl(noutmny, 0)) nmny
    029                            from ia_monthout
    030                           where pk_group = '0001A1100000000003Z3'
    031                             and pk_book = '1001A110000000001GQU'
    032                             and pk_org = '1001A110000000001GTH'
    033                             and dr = 0
    034                             and caccountperiod >= '2019-01'
    035                             and caccountperiod <= '2019-04')))) a) union all
    036         (select sum(nvl(nnum, 0)) nnum,
    037                   sum(nvl(nmny, 0)) nmny,
    038                   'end' datatype,
    039                   '1001D21000000021WO54' pk_brsetting
    040              from (((select sum(nvl(nabnum, 0)) nnum, sum(nvl(nabmny, 0)) nmny
    041                        from ia_periodnab
    042                       where pk_group = '0001A1100000000003Z3'
    043                         and pk_book = '1001A110000000001GQU'
    044                         and pk_org = '1001A110000000001GTH'
    045                         and dr = 0
    046                         and caccountperiod = '2018-12') union
    047                    all((select sum(nvl(ninnum, 0)) nnum,
    048                                 sum(nvl(ninmny, 0)) nmny
    049                            from ia_monthin
    050                           where pk_group = '0001A1100000000003Z3'
    051                             and pk_book = '1001A110000000001GQU'
    052                             and pk_org = '1001A110000000001GTH'
    053                             and dr = 0
    054                             and caccountperiod >= '2019-01'
    055                             and caccountperiod <= '2019-05') union all
    056                         (select sum(-nvl(noutnum, 0)) nnum,
    057                                 sum(-nvl(noutmny, 0)) nmny
    058                            from ia_monthout
    059                           where pk_group = '0001A1100000000003Z3'
    060                             and pk_book = '1001A110000000001GQU'
    061                             and pk_org = '1001A110000000001GTH'
    062                             and dr = 0
    063                             and caccountperiod >= '2019-01'
    064                             and caccountperiod <= '2019-05')))) b)) union
    065         all((select sum(nvl(nnum, 0)) nnum,
    066                      sum(nvl(nmny, 0)) nmny,
    067                      'begin' datatype,
    068                      '1001D21000000021WZP4' pk_brsetting
    069                 from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny
    070                           from ia_goodsledger
    071                          where pk_group = '0001A1100000000003Z3'
    072                            and pk_book = '1001A110000000001GQU'
    073                            and pk_org = '1001A110000000001GTH'
    074                            and dr = 0
    075                            and fintransitflag = 0
    076                            and caccountperiod < '2019-05') union all
    077                       (select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny
    078                           from ia_goodsledger
    079                          where pk_group = '0001A1100000000003Z3'
    080                            and pk_book = '1001A110000000001GQU'
    081                            and pk_org = '1001A110000000001GTH'
    082                            and dr = 0
    083                            and fintransitflag = 1
    084                            and caccountperiod < '2019-05'))) c) union all
    085              (select sum(nvl(nnum, 0)) nnum,
    086                      sum(nvl(nmny, 0)) nmny,
    087                      'end' datatype,
    088                      '1001D21000000021WZP4' pk_brsetting
    089                 from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny
    090                           from ia_goodsledger
    091                          where pk_group = '0001A1100000000003Z3'
    092                            and pk_book = '1001A110000000001GQU'
    093                            and pk_org = '1001A110000000001GTH'
    094                            and dr = 0
    095                            and fintransitflag = 0
    096                            and caccountperiod <= '2019-05') union all
    097                       (select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny
    098                           from ia_goodsledger
    099                          where pk_group = '0001A1100000000003Z3'
    100                            and pk_book = '1001A110000000001GQU'
    101                            and pk_org = '1001A110000000001GTH'
    102                            and dr = 0
    103                            and fintransitflag = 1
    104                            and caccountperiod <= '2019-05'))) d))) union
    105         all((((select sum(nvl(nnum, 0)) nnum,
    106                       sum(nvl(nmny, 0)) nmny,
    107                       'credit' datatype,
    108                       '1001D21000000021WO54' pk_brsetting
    109                  from (select sum(nvl(noutnum, 0)) nnum,
    110                               sum(nvl(noutmny, 0)) nmny
    111                          from ia_monthout
    112                         where pk_group = '0001A1100000000003Z3'
    113                           and pk_book = '1001A110000000001GQU'
    114                           and pk_org = '1001A110000000001GTH'
    115                           and dr = 0
    116                           and caccountperiod >= '2019-05'
    117                           and caccountperiod <= '2019-05') g) union all
    118              (select sum(nvl(nnum, 0)) nnum,
    119                       sum(nvl(nmny, 0)) nmny,
    120                       'debit' datatype,
    121                       '1001D21000000021WO54' pk_brsetting
    122                  from (select sum(nvl(ninnum, 0)) nnum,
    123                               sum(nvl(ninmny, 0)) nmny
    124                          from ia_monthin
    125                         where pk_group = '0001A1100000000003Z3'
    126                           and pk_book = '1001A110000000001GQU'
    127                           and pk_org = '1001A110000000001GTH'
    128                           and dr = 0
    129                           and caccountperiod >= '2019-05'
    130                           and caccountperiod <= '2019-05') h)))) union all
    131         (select sum(nnum) nnum, sum(nmny) nmny, datatype, pk_brsetting
    132            from (((select nvl(nnum, 0) nnum,
    133                           nvl(nmny, 0) nmny,
    134                           case
    135                             when fintransitflag = 0 then
    136                              'debit'
    137                             else
    138                              'credit'
    139                           end datatype,
    140                           '1001D21000000021WZP4' pk_brsetting
    141                      from ia_goodsledger
    142                     where pk_group = '0001A1100000000003Z3'
    143                       and pk_book = '1001A110000000001GQU'
    144                       and pk_org = '1001A110000000001GTH'
    145                       and dr = 0
    146                       and caccountperiod >= '2019-05'
    147                       and caccountperiod <= '2019-05'))) o
    148           group by datatype, pk_brsetting)) s
    149  group by datatype, pk_brsetting

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

    01 Dumping Work Area Table (level=1)
    02 =====================================
    03  
    04   Global SGA Info
    05   ---------------
    06  
    07     global target:    12856 MB
    08     auto target:       8467 MB
    09     max pga:           2048 MB
    10     pga limit:         4096 MB
    11     pga limit known:  0
    12     pga limit errors:     0
    13  
    14     pga inuse:         3447 MB
    15     pga alloc:         4705 MB
    16     pga freeable:       164 MB
    17     pga freed:        13398681 MB
    18     pga to free:          0 %
    19     broker request:       0
    20  
    21     pga auto:             0 MB
    22     pga manual:           0 MB
    23  
    24     pga alloc  (max):  5882 MB
    25     pga auto   (max):  2084 MB
    26     pga manual (max):     1 MB
    27  
    28     # workareas     :     1
    29     # 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个内存映射条目限制的。

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

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

    01 SQL> col NAME for a30
    02 SQL> col VALUE for a20
    03 SQL> col DESCRIB for a45
    04 SQL> set lines 200
    05 SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ
    06   2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
    07   3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%realfree%';
    08  
    09 NAME                           VALUE                DESCRIB
    10 ------------------------------ -------------------- ---------------------------------------------
    11 _realfree_heap_max_size        32768                minimum max total heap size, in Kbytes
    12 _realfree_heap_pagesize_hint   65536                hint for real-free page size in bytes
    13 _realfree_heap_mode            0                    mode flags for real-free heap
    14 _use_realfree_heap             TRUE                 use real-free based allocator for PGA memory

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

    1 SQL> select 65536*65530/1024/1024/1024 GB  from dual;
    2  
    3         GB
    4 ----------
    5 3.99963379

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

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

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

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

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

    1 SQL> alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile;
    2  
    3 System altered.

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

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

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

    1 BEGIN
    2 dbms_auto_task_admin.disable(
    3 client_name => 'sql tuning advisor',
    4 operation => NULL,
    5 window_name => NULL);
    6 END;
    7 /

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

    1 BEGIN
    2 dbms_auto_task_admin.enable(
    3 client_name => 'sql tuning advisor',
    4 operation => NULL,
    5 window_name => NULL);
    6 END;
    7 /

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

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

     

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

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

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

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

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

    001 /* SQL Analyze(650,1) */
    002 select SUM(nvl(nnum, 0)) nnum,
    003        SUM(nvl(nmny, 0)) nmny,
    004        datatype,
    005        pk_brsetting
    006   from ((((select sum(nvl(nnum, 0)) nnum,
    007                   sum(nvl(nmny, 0)) nmny,
    008                   'begin' datatype,
    009                   '1001D21000000021WO54' pk_brsetting
    010              from (((select sum(nvl(nabnum, 0)) nnum,
    011                             sum(nvl(nabmny, 0)) nmny
    012                        from ia_periodnab
    013                       where pk_group = '0001A1100000000003Z3'
    014                         and pk_book = '1001A110000000001GQU'
    015                         and pk_org = '1001A110000000001GTH'
    016                         and dr = 0
    017                         and caccountperiod = '2018-12') union
    018                    all((select sum(nvl(ninnum, 0)) nnum,
    019                                 sum(nvl(ninmny, 0)) nmny
    020                            from ia_monthin
    021                           where pk_group = '0001A1100000000003Z3'
    022                             and pk_book = '1001A110000000001GQU'
    023                             and pk_org = '1001A110000000001GTH'
    024                             and dr = 0
    025                             and caccountperiod >= '2019-01'
    026                             and caccountperiod <= '2019-04') union all
    027                         (select sum(-nvl(noutnum, 0)) nnum,
    028                                 sum(-nvl(noutmny, 0)) nmny
    029                            from ia_monthout
    030                           where pk_group = '0001A1100000000003Z3'
    031                             and pk_book = '1001A110000000001GQU'
    032                             and pk_org = '1001A110000000001GTH'
    033                             and dr = 0
    034                             and caccountperiod >= '2019-01'
    035                             and caccountperiod <= '2019-04')))) a) union all
    036         (select sum(nvl(nnum, 0)) nnum,
    037                   sum(nvl(nmny, 0)) nmny,
    038                   'end' datatype,
    039                   '1001D21000000021WO54' pk_brsetting
    040              from (((select sum(nvl(nabnum, 0)) nnum, sum(nvl(nabmny, 0)) nmny
    041                        from ia_periodnab
    042                       where pk_group = '0001A1100000000003Z3'
    043                         and pk_book = '1001A110000000001GQU'
    044                         and pk_org = '1001A110000000001GTH'
    045                         and dr = 0
    046                         and caccountperiod = '2018-12') union
    047                    all((select sum(nvl(ninnum, 0)) nnum,
    048                                 sum(nvl(ninmny, 0)) nmny
    049                            from ia_monthin
    050                           where pk_group = '0001A1100000000003Z3'
    051                             and pk_book = '1001A110000000001GQU'
    052                             and pk_org = '1001A110000000001GTH'
    053                             and dr = 0
    054                             and caccountperiod >= '2019-01'
    055                             and caccountperiod <= '2019-05') union all
    056                         (select sum(-nvl(noutnum, 0)) nnum,
    057                                 sum(-nvl(noutmny, 0)) nmny
    058                            from ia_monthout
    059                           where pk_group = '0001A1100000000003Z3'
    060                             and pk_book = '1001A110000000001GQU'
    061                             and pk_org = '1001A110000000001GTH'
    062                             and dr = 0
    063                             and caccountperiod >= '2019-01'
    064                             and caccountperiod <= '2019-05')))) b)) union
    065         all((select sum(nvl(nnum, 0)) nnum,
    066                      sum(nvl(nmny, 0)) nmny,
    067                      'begin' datatype,
    068                      '1001D21000000021WZP4' pk_brsetting
    069                 from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny
    070                           from ia_goodsledger
    071                          where pk_group = '0001A1100000000003Z3'
    072                            and pk_book = '1001A110000000001GQU'
    073                            and pk_org = '1001A110000000001GTH'
    074                            and dr = 0
    075                            and fintransitflag = 0
    076                            and caccountperiod < '2019-05') union all
    077                       (select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny
    078                           from ia_goodsledger
    079                          where pk_group = '0001A1100000000003Z3'
    080                            and pk_book = '1001A110000000001GQU'
    081                            and pk_org = '1001A110000000001GTH'
    082                            and dr = 0
    083                            and fintransitflag = 1
    084                            and caccountperiod < '2019-05'))) c) union all
    085              (select sum(nvl(nnum, 0)) nnum,
    086                      sum(nvl(nmny, 0)) nmny,
    087                      'end' datatype,
    088                      '1001D21000000021WZP4' pk_brsetting
    089                 from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny
    090                           from ia_goodsledger
    091                          where pk_group = '0001A1100000000003Z3'
    092                            and pk_book = '1001A110000000001GQU'
    093                            and pk_org = '1001A110000000001GTH'
    094                            and dr = 0
    095                            and fintransitflag = 0
    096                            and caccountperiod <= '2019-05') union all
    097                       (select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny
    098                           from ia_goodsledger
    099                          where pk_group = '0001A1100000000003Z3'
    100                            and pk_book = '1001A110000000001GQU'
    101                            and pk_org = '1001A110000000001GTH'
    102                            and dr = 0
    103                            and fintransitflag = 1
    104                            and caccountperiod <= '2019-05'))) d))) union
    105         all((((select sum(nvl(nnum, 0)) nnum,
    106                       sum(nvl(nmny, 0)) nmny,
    107                       'credit' datatype,
    108                       '1001D21000000021WO54' pk_brsetting
    109                  from (select sum(nvl(noutnum, 0)) nnum,
    110                               sum(nvl(noutmny, 0)) nmny
    111                          from ia_monthout
    112                         where pk_group = '0001A1100000000003Z3'
    113                           and pk_book = '1001A110000000001GQU'
    114                           and pk_org = '1001A110000000001GTH'
    115                           and dr = 0
    116                           and caccountperiod >= '2019-05'
    117                           and caccountperiod <= '2019-05') g) union all
    118              (select sum(nvl(nnum, 0)) nnum,
    119                       sum(nvl(nmny, 0)) nmny,
    120                       'debit' datatype,
    121                       '1001D21000000021WO54' pk_brsetting
    122                  from (select sum(nvl(ninnum, 0)) nnum,
    123                               sum(nvl(ninmny, 0)) nmny
    124                          from ia_monthin
    125                         where pk_group = '0001A1100000000003Z3'
    126                           and pk_book = '1001A110000000001GQU'
    127                           and pk_org = '1001A110000000001GTH'
    128                           and dr = 0
    129                           and caccountperiod >= '2019-05'
    130                           and caccountperiod <= '2019-05') h)))) union all
    131         (select sum(nnum) nnum, sum(nmny) nmny, datatype, pk_brsetting
    132            from (((select nvl(nnum, 0) nnum,
    133                           nvl(nmny, 0) nmny,
    134                           case
    135                             when fintransitflag = 0 then
    136                              'debit'
    137                             else
    138                              'credit'
    139                           end datatype,
    140                           '1001D21000000021WZP4' pk_brsetting
    141                      from ia_goodsledger
    142                     where pk_group = '0001A1100000000003Z3'
    143                       and pk_book = '1001A110000000001GQU'
    144                       and pk_org = '1001A110000000001GTH'
    145                       and dr = 0
    146                       and caccountperiod >= '2019-05'
    147                       and caccountperiod <= '2019-05'))) o
    148           group by datatype, pk_brsetting)) s
    149  group by datatype, pk_brsetting

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

    01 Dumping Work Area Table (level=1)
    02 =====================================
    03  
    04   Global SGA Info
    05   ---------------
    06  
    07     global target:    12856 MB
    08     auto target:       8467 MB
    09     max pga:           2048 MB
    10     pga limit:         4096 MB
    11     pga limit known:  0
    12     pga limit errors:     0
    13  
    14     pga inuse:         3447 MB
    15     pga alloc:         4705 MB
    16     pga freeable:       164 MB
    17     pga freed:        13398681 MB
    18     pga to free:          0 %
    19     broker request:       0
    20  
    21     pga auto:             0 MB
    22     pga manual:           0 MB
    23  
    24     pga alloc  (max):  5882 MB
    25     pga auto   (max):  2084 MB
    26     pga manual (max):     1 MB
    27  
    28     # workareas     :     1
    29     # 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个内存映射条目限制的。

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

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

    01 SQL> col NAME for a30
    02 SQL> col VALUE for a20
    03 SQL> col DESCRIB for a45
    04 SQL> set lines 200
    05 SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ
    06   2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
    07   3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%realfree%';
    08  
    09 NAME                           VALUE                DESCRIB
    10 ------------------------------ -------------------- ---------------------------------------------
    11 _realfree_heap_max_size        32768                minimum max total heap size, in Kbytes
    12 _realfree_heap_pagesize_hint   65536                hint for real-free page size in bytes
    13 _realfree_heap_mode            0                    mode flags for real-free heap
    14 _use_realfree_heap             TRUE                 use real-free based allocator for PGA memory

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

    1 SQL> select 65536*65530/1024/1024/1024 GB  from dual;
    2  
    3         GB
    4 ----------
    5 3.99963379

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

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

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

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

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

    1 SQL> alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile;
    2  
    3 System altered.

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

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

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

    1 BEGIN
    2 dbms_auto_task_admin.disable(
    3 client_name => 'sql tuning advisor',
    4 operation => NULL,
    5 window_name => NULL);
    6 END;
    7 /

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

    1 BEGIN
    2 dbms_auto_task_admin.enable(
    3 client_name => 'sql tuning advisor',
    4 operation => NULL,
    5 window_name => NULL);
    6 END;
    7 /

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

  • 相关阅读:
    Ubuntu下安装了java但启动eclipse报错说没装java
    Servlet之Filter详解
    使用mybatis-generator自动生成model、dao、mapping文件
    深入浅出MyBatis
    彻底理解字符编码
    Java多线程系列
    【Swagger2】解决swagger文档地址请求404的问题
    【Git】Git如何合并某一次commit的内容到指定分支
    【Iterm2】如何解决iterm2窗口自动隐藏的问题
    【Git】.DS_Store 是什么文件
  • 原文地址:https://www.cnblogs.com/feiyun8616/p/12884511.html
Copyright © 2020-2023  润新知