首页 » ORACLE 9i-23ai » Memory Notification: Heap size nnnnK exceeds notification threshold (51200K)

Memory Notification: Heap size nnnnK exceeds notification threshold (51200K)

今天一台生产服务器报警提示PGA使用过5G,记的pga_aggregate_target 设置的为3G,排查一下这个问题是哪个进程占用的

db version :10204
os version:centos 5.6 x64

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 3000M

SQL> select ksppinm, ksppstvl, ksppdesc 
from x$ksppi x, x$ksppcv y where x.indx = y.indx 
and ksppinm in ('_pga_max_size','_smm_max_size'); 

KSPPINM              KSPPSTVL             KSPPDESC
-------------------- -------------------- --------------------------------------------------
_pga_max_size        629145600            Maximum size of the PGA memory for one process
_smm_max_size        307200               maximum work area size in auto mode (serial)

SQL> SELECT * FROM V$PGASTAT;
NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                   3145728000 bytes
aggregate PGA auto target                                        2723668992 bytes
global memory bound                                               314572800 bytes
total PGA inuse                                                   119418880 bytes
total PGA allocated                                               222068736 bytes
maximum PGA allocated                                            5383633920 bytes
total freeable PGA memory                                          54198272 bytes
process count                                                           224
max processes count                                                     227
PGA memory freed back to OS                                      2.2598E+11 bytes
total PGA used for auto workareas                                         0 bytes
maximum PGA used for auto workareas                               373331968 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                                531456 bytes
over allocation count                                                   738
bytes processed                                                  2.4579E+12 bytes
extra bytes read/written                                         1.3761E+12 bytes
cache hit percentage                                                   64.1 percent
recompute count (total)                                             1302881

19 rows selected.

SQL> select pga_used_mem/1024/1024,username,program,spid 
from v$process order by 1;
PGA_USED_MEM/1024/1024 USERNAME        PROGRAM                                          SPID
---------------------- --------------- ------------------------------------------------ ------------
            .894818306 oracle          oracle@topbox                                    28672
            1.64495564 oracle          oracle@topbox (MMON)                             3735
            10.4304094 oracle          oracle@topbox (LGWR)                             3725
            4865.13068 oracle          oracle@topbox                                    29741

[oracle@topbox ~]$ ps -ef |grep 29741
oracle   29741     1 95 15:33 ?        00:06:17 oracletopbox (LOCAL=NO)
oracle   30213 29921  0 15:39 pts/0    00:00:00 grep 29741

note:
we can confirmation 29741 is a client process

--alert record
Memory Notification: Library Cache Object loaded into SGA
Heap size 106731K exceeds notification threshold (51200K)
KGL object name :select t.icnum,t.realname,t.idcard,      substr(td.dian_org_id,0,3) shengOrg,td.shi_org_id shiOrg,td.xian_org_id xianOrg,td.dian_org_i
d dianOrg  from topbox_userinfo t,topbox_userinfodetail td   where                 t.icnum = td.icnum and                  ((t.idcard='xxxxxxxx' an
d t.realname='xxxxxxx' and td.dian_org_id like '540%' ) or (t.idcard='xxxxx'

根据sql我大致确认sql的用途,是一个web app在根据excel里的记录拼sql在库中查询,利用dbms_xplan查看执行计划是大概有1W行,在udump中也有记录process id 的trace 文件

[oracle@topbox udump]$ vi topbox_ora_29741.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0
System name:    Linux
Node name:      topbox
Release:        2.6.18-92.el5
Version:        #1 SMP Tue Jun 10 18:51:06 EDT 2008
Machine:        x86_64
Instance name: topbox
Redo thread mounted by this instance: 1
Oracle process number: 88
Unix process pid: 29741, image: oracle@topbox

*** 2012-10-16 15:42:02.481
*** ACTION NAME:() 2012-10-16 15:42:02.481
*** MODULE NAME:(JDBC Thin Client) 2012-10-16 15:42:02.481
*** SERVICE NAME:(SYS$USERS) 2012-10-16 15:42:02.481
*** SESSION ID:(2111.25293) 2012-10-16 15:42:02.481
Memory Notification: Library Cache Object loaded into SGA
Heap size 106084K exceeds notification threshold (51200K)
LIBRARY OBJECT HANDLE: handle=25bab3888 mtx=0x25bab39b8(0) cdp=1
name=
hash=46940c25d3811dca158c7c2239c03915 timestamp=10-16-2012 15:33:28
namespace=CRSR flags=RON/KGHP/TIM/PN0/DBN/[10010040]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=6 hpc=0004 hlc=0004
lwt=0x25bab3930[0x25bab3930,0x25bab3930] ltm=0x25bab3940[0x25bab3940,0x25bab3940]
pwt=0x25bab38f8[0x25bab38f8,0x25bab38f8] ptm=0x25bab3908[0x25bab3908,0x25bab3908]
ref=0x25bab3960[0x25bab3960,0x25bab3960] lnd=0x25bab3978[0x25bab3978,0x25bab3978]
  LIBRARY OBJECT: object=25c62c150
  type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
  CHILDREN: size=16
  child#    table reference   handle
  ------ -------- --------- --------
       0 25eac0b90 25eac0800 27bd9e698
  DATA BLOCKS:
  data#     heap  pointer    status pins change whr alloc(K)  size(K)
  ----- -------- -------- --------- ---- ------ --- -------- --------
      0 27af91e48 25c62c268 I/P/A/-/-    0 NONE   00      1.19     2.17
LIBRARY OBJECT HANDLE: handle=27bd9e698 mtx=0x27bd9e7c8(0) cdp=0
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=X latch#=6 hpc=0002 hlc=0002
lwt=0x27bd9e740[0x27bd9e740,0x27bd9e740] ltm=0x27bd9e750[0x27bd9e750,0x27bd9e750]
pwt=0x27bd9e708[0x27bd9e708,0x27bd9e708] ptm=0x27bd9e718[0x27bd9e718,0x27bd9e718]
ref=0x27bd9e770[0x25eac0800,0x25eac0800] lnd=0x27bd9e788[0x27bd9e788,0x27bd9e788]
  LIBRARY OBJECT: object=28062fa20
  type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
  DEPENDENCIES: count=2 size=16
  dependency#    table reference   handle position flags
  ----------- -------- --------- -------- -------- -------------------
            0 282825598 2828252d8 28091dd00      165 DEP[01]
            1 282825598 282825360 28397ab10      147 DEP[01]
  ACCESSES: count=2 size=16
  dependency# types
  ----------- -----
            0 0009
            1 0009
  TRANSLATIONS: count=2 size=16
  original    final
  -------- --------
  28091dd00 28091dd00
  28397ab10 28397ab10
  DATA BLOCKS:
  data#     heap  pointer    status pins change whr alloc(K)  size(K)
  ----- -------- -------- --------- ---- ------ --- -------- --------
      0 25f8b43b8 28062fb38 I/P/A/-/-    0 NONE   00      3.14     4.16
      6 25c7ca988 25ed18260 I/P/A/-/E    0 NONE   00  104662.41 106084.10

NOTE:
看出heap 6占用了104662.41k,判断这不是oracle的异常,而是这个sql确实太不可思议,也可以dump memory 生成trace 明细

oradebug dump heapdump 536870917;
oradebug dump processstate 10;
ALTER SESSION SET EVENTS   'immediate trace name heapdump level level';
level Description
1 PGA summary
2 SGA summary
4 UGA summary
8 Callheap (Current)
16 Callheap (User)
1025 PGA with contents
2050 SGA with contents
4100 UGA with contents

tip:
pga_aggregate_target This parameter controls the maximum amount of memory PGA which can be usedby the queries when WORKAREA_SIZE_POLICY is set to Auto, also have an effect on the execution plans of the cost based optimizer.pga_aggregate_target Just is a target,, as opposed to a hard limit , to make a hard-limit setting, but that does not currently exist.

In 9i, PGA_AGGREGATE_TARGET parameter controls the sizing of workareas for all dedicated server connections, but it has no effect on shared servers (MTS) connections,in 10g can controls MTS.

Tuned so that the overall size of PGA don’t exceeds PGA_AGGREGATE_TARGET. It should be noted that it is a target: Oracle will tune the tunable part of PGA memory so that total PGA memory don’t exceed PGA_AGGREGATE_TARGET, but not if the untunable memory becomes higher that PGA_AGGREGATE_TARGET (seeNote:223730.1).

The following views get populated when workarea_size_policy is set to Auto in the database.
1. V$SQL_WORKAREA
2. V$SQL_WORKAREA_ACTIVE
3. V$PROCESS contains new columns (PGA_USED_MEM, PGA_ALLOC_MEM AND PGA_MAX_MEM)
4. V$PGASTAT
However, when WORKAREA_SIZE_POLICY and PGA_AGGREGATE_TARGET are not set,
views v$sql_workarea and v$sql_workarea_active are empty.
Oracle 9.2 has also new views (seeNote:223730.1for some explanation)
1. V$SQL_WORKAREA_HISTOGRAM
2. V$PGA_TARGET_ADVICE
3. V$PGA_TARGET_ADVICE_HISTOGRAM V$SYSSTAT and V$SESSTAT contains new ‘work area’ statistics for monitoring the automatic memory tuning.

about Heap size 106084K exceeds notification threshold (51200K),It is just an informative message wen can ignore it.
a hidden parameter to change the threshold

SQL> l
  1  SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value",
  2             c.ksppstvl "Instance Value"
  3        FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  4       WHERE a.indx = b.indx AND a.indx = c.indx
  5*            AND a.ksppinm LIKE '%kgl_large_heap%'
SQL> /

Parameter                                Session Value        Instance Value
---------------------------------------- -------------------- ---------------------
_kgl_large_heap_warning_threshold        52428800             52428800

we can change the undocumented parameter _kg_large_heap_warning_threshold , consists in increasing the threshold level by executing the following statement:

alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile;

followed by a db restart.

we can increase or decrease it as necessary, because the concept of threshold depends on how much memory is allocated for the SGA,If we work a lot with large objects in memory and the alert log is cluttered by many of these notifications, but the performances of the database are still ok, we might want to increase this value to avoid logging useless entries.Tanel’s heapdump_analyzer is really useful in such cases.

note:
DO NOT post hidden parameters In your production database unless you can explain IN DEEP AND DETAILS what are ALL the consequences of it and are ready to pay if they do any harm.

打赏

,

对不起,这篇文章暂时关闭评论。