ORA-4030 LMS 进程占用内存很高 实例重启

补丁不生效例子

aix 7202 11.2.0.4 rac 180417 PSU
实例2 例行重启，实例1发生了意外重启

实例1告警日志
   

Fri Sep 16 00:34:57 2022
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
Fri Sep 16 00:34:59 2022
minact-scn: Master returning as live inst:2 has inc# mismatch instinc:0 cur:8 errcnt:0
Fri Sep 16 00:36:16 2022
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms2_38732932.trc (incident=1488512):
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_1488512/orcl1_lms2_38732932_i1488512.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 16 00:36:17 2022
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms0_19269850.trc (incident=1488496):
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_1488496/orcl1_lms0_19269850_i1488496.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 16 00:36:18 2022
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms1_5505754.trc (incident=1488504):
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_1488504/orcl1_lms1_5505754_i1488504.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 16 00:36:25 2022
Dumping diagnostic data in directory=[cdmp_20220916003625], requested by (instance=1, osid=19269850 (LMS0)), summary=[incident=1488496].
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms0_19269850.trc (incident=1488497):
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_1488497/orcl1_lms0_19269850_i1488497.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms2_38732932.trc (incident=1488513):
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_1488513/orcl1_lms2_38732932_i1488513.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 16 00:36:27 2022
Sweep [inc][1488513]: completed
Sweep [inc][1488512]: completed
Sweep [inc][1488504]: completed
Sweep [inc][1488497]: completed
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms1_5505754.trc (incident=1488505):
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms1_5505754.trc (incident=1488506):
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms1_5505754.trc (incident=1488507):

lms2、lms0、lms1三个进程都突然发生ORA-4030异常，内存不足，看相关trc文件

Dump file /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_1488512/orcl1_lms2_38732932_i1488512.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    AIX
Node name:    db1
Release:    2
Version:    7
Machine:    00FB76794C00
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 38732932, image: oracle@db1 (LMS2)


*** 2022-09-16 00:36:16.929
*** SESSION ID:(1892.1) 2022-09-16 00:36:16.929
*** CLIENT ID:() 2022-09-16 00:36:16.929
*** SERVICE NAME:(SYS$BACKGROUND) 2022-09-16 00:36:16.929
*** MODULE NAME:() 2022-09-16 00:36:16.929
*** ACTION NAME:() 2022-09-16 00:36:16.929

Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms2_38732932.trc
ORA-04030: out of process memory when trying to allocate 195248 bytes (kjc pga smbuf,KJC PGA send buffer)

========= Dump for incident 1488512 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------

*** 2022-09-16 00:36:22.939
99% 31 GB, 172610 chunks: "KJC PGA send buffer       "       <--------------这里很异常
         kjc pga smbuf   ds=110733230  dsprt=110005120
 1% 213 MB, 172724 chunks: "free memory               "
         pga heap        ds=110005120  dsprt=0
 0% 149 MB, 145 chunks: "permanent memory          "
         pga heap        ds=110005120  dsprt=0
 0% 75 KB, 3 chunks: "permanent memory          "
         session heap    ds=1109d66e0  dsprt=11011f5d8
 0% 54 KB, 1 chunk : "free memory               "
         top call heap   ds=11011f3b8  dsprt=0
 0% 38 KB, 1 chunk : "Fixed Uga                 "
         Fixed UGA heap  ds=1108bad28  dsprt=110005120
 0% 20 KB, 2 chunks: "free memory               "
         session heap    ds=1109d66e0  dsprt=11011f5d8
 0% 16 KB, 7 chunks: "permanent memory          "
         diag pga        ds=1107324d0  dsprt=110005120
 0% 16 KB, 2 chunks: "dbgeInitProcessCtx:InvCtx "
         diag pga        ds=1107324d0  dsprt=110005120
 0% 16 KB, 1 chunk : "kgh stack                 "
         pga heap        ds=110005120  dsprt=0

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
32 GB total:
    31 GB commented, 149 MB permanent
   213 MB free (0 KB in empty extents),
      32 GB, 1 heap: "kjc pga smbuf  " 212 MB free held
------------------------------------------------------
Summary of subheaps at depth 1
31 GB total:
    31 GB commented, 98 KB permanent
    31 KB free (0 KB in empty extents),
      31 GB, 172610 chunks: "KJC PGA send buffer       "

*** 2022-09-16 00:36:23.380

=========================================
REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS
-----------------------------------------

Dump of Real-Free Memory Allocator Heap [0x1109a4810]
mag=0xfefe0001 flg=0x5000003 fds=0x0 blksz=65536
blkdstbl=0x1109a4820, iniblk=521216 maxblk=524288 numsegs=320
In-use num=172723 siz=4028628992, Freeable num=1 siz=65536, Free num=310 siz=20316160

==========================================
INSTANCE-WIDE PRIVATE MEMORY USAGE SUMMARY
------------------------------------------

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

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

    global target: 115507 MB
    auto target: 11333 MB
    max pga: 2048 MB
    pga limit: 4096 MB
    pga limit known: 0
    pga limit errors: 0

    pga inuse: 102955 MB
    pga alloc: 105793 MB
    pga freeable: 742 MB
    pga freed: 43967956 MB
    pga to free: 0
    broker request: 0

    pga auto: 41 MB
    pga manual: 0 MB

    pga alloc (max): 105793 MB
    pga auto (max): 5444 MB
    pga manual (max): 1 MB

    # workareas : 43
    # workareas(max): 224



================================
PER-PROCESS PRIVATE MEMORY USAGE
--------------------------------

Private memory usage per Oracle process

-------------------------
Top 10 processes:
-------------------------
(percentage is of 103 GB total allocated memory)
31% pid 13: 32 GB used of 32 GB allocated (128 KB freeable)
31% pid 14: 32 GB used of 32 GB allocated
31% pid 15: 32 GB used of 32 GB allocated (64 KB freeable) <= CURRENT PROC
 0% pid 12: 14 MB used of 113 MB allocated (98 MB freeable)
 0% pid 10: 45 MB used of 56 MB allocated (7680 KB freeable)
 0% pid 86: 42 MB used of 45 MB allocated
 0% pid 87: 42 MB used of 45 MB allocated
 0% pid 89: 42 MB used of 45 MB allocated
 0% pid 973: 42 MB used of 43 MB allocated (1024 KB freeable)
 0% pid 1029: 42 MB used of 43 MB allocated (1024 KB freeable)

-------------------------
All processes:
-------------------------
(session detail when over 5290 MB allocated)

pid 2: 2119 KB used of 2350 KB allocated
pid 3: 1060 KB used of 1182 KB allocated
pid 4: 1056 KB used of 1182 KB allocated
pid 5: 1066 KB used of 1182 KB allocated
pid 6: 6161 KB used of 6494 KB allocated (128 KB freeable)
pid 7: 2121 KB used of 2718 KB allocated (320 KB freeable)
...

三个进程每个都32G，搜索MOS，发现有个bug很像

检查当前的补丁，opatch lsinv|grep 18316692 已经包含了此修复，但是LMS的pga还是很大，看来没有解决。

怎么办呢？

监控，发现内存占用太大就重启实例（LMS进程杀不得）

select pid,spid,program,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem from v$process where program like '%LMS%';

lms进程的工作机制



参考：

https://orainternals.files.wordpress.com/2008/03/riyaj_battle_of_nodes_rac_perf_myths_ppt.pdf