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.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。