Errors in fileD:\APP\ADMINISTRATOR\diag\rdbms\primary\tree\trace\tree_j001_8540.trc (incident=520695):
ORA-04031: 无法分配 4136 字节的共享内存("shared pool","unknown object","sgaheap(1,0)","modification ")
ORA-02063: 紧接着 line (起自 MINI)
库的内存管理是AMM
查看memory动态分布情况
set head off
select chr(10) from dual;
set head on
prompt "内存动态组件分配情况:"
col component for a25
col LAST_OPER_TYPE for a10
SELECT COMPONENT,
CURRENT_SIZE / 1024 / 1024 / 1024 AS CURRENT_GB,
MIN_SIZE / 1024 / 1024 / 1024 AS MINGB,
MAX_SIZE / 1024 / 1024 / 1024 AS MAXGB,
USER_SPECIFIED_SIZE / 1024 / 1024 / 1024 AS SPECIFIEDMB,
OPER_COUNT,
LAST_OPER_TYPE,
LAST_OPER_MODE,
LAST_OPER_TIME,
GRANULE_SIZE / 1024 / 1024 as GRANULE_MB
FROM V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_GB MINGB MAXGB SPECIFIEDMB OPER_COUNT LAST_OPER_ LAST_OPER_MODE LAST_OPER_TIME GRANULE_MB
------------------------- -------------------- ---------- ----------- ---------- ------------------------------------- ------------------ ----------
shared pool 14.75 13 14.75 14.75 4 GROW MANUAL 30-JUN-16 256
large pool .5 .5 .5 0 0 STATIC 256
java pool .5 .5 .5 0 0 STATIC 256
streams pool .5 .25 .5 0 1SHRINK DEFERRED 24-JUN-16 256
SGA Target 28 27.25 30 0 28 GROW DEFERRED 27-JUN-16 256
DEFAULT buffer cache 11.25 11.25 14.75 0 8 SHRINK MANUAL 30-JUN-16 256
KEEP buffer cache 0 0 0 0 0STATIC 256
RECYCLE buffer cache 0 0 0 0 0STATIC 256
DEFAULT 2K buffer cache 0 0 0 0 0STATIC 256
DEFAULT 4K buffer cache 0 0 0 0 0STATIC 256
DEFAULT 8K buffer cache 0 0 0 0 0STATIC 256
DEFAULT 16K buffer cache 0 0 0 0 0STATIC 256
DEFAULT 32K buffer cache 0 0 0 0 0STATIC 256
Shared IO Pool 0 0 0 0 0STATIC 256
PGA Target 50 48 50.75 30 31 SHRINK MANUAL 30-JUN-16 256
ASM Buffer Cache 0 0 0 0 0STATIC 256
shared pool 13G
PGA 50G
用工具oerr
[oracle@node1 ~]$ oerr ora 04031
04031, 00000, "unable to allocate %sbytes of shared memory(\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out ofmemory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
可以手动设置SHARED_POOL_RESERVED_SIZE和SHARED_POOL_SIZE
通过报错观察("shared pool","unknown object","sgaheap(1,0)","modification ")通过以下sql
SYS@tree> select KSMCHCLS,count(*) ,sum(KSMCHSIZ)/1024/1024from x$ksmsp group by KSMCHCLS
2 /
KSMCHCLS COUNT(*) SUM(KSMCHSIZ)/1024/1024
-------- ---------------------------------
recr 1869925 4066.75346
freeabl 2457879 6377.9045
R-freea 640 4.02889252
perm 35 1702.39667
R-free 79 738.409416
free 317037 2214.50211
已选择6行。
SYS@tree> select KSMCHIDX"SubPool", 'sga heap('||KSMCHIDX||',0)' sga_heap,
2 decode(round(ksmchsiz/1000),0,'0-1K',1,'1-2K', 2,'2-3K',3,'3-4K',
3 4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
4 '8-9k', 9,'9-10k','> 10K')"size",
5 count(*),ksmchcls Status,sum(ksmchsiz) Bytes
6 from x$ksmsp
7 where KSMCHCOM = 'freememory'
8 group by ksmchidx, ksmchcls,
9 'sgaheap('||KSMCHIDX||',0)',ksmchcom,ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
10 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
11 '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K')
12 order by 1,2
13 /
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
1 sga heap(1,0) 2-3K 1 R-free 1920
1 sga heap(1,0) 3-4K 1 R-free 2560
1 sga heap(1,0) 4-5K 2 R-free 8320
1 sga heap(1,0) 8-9k 1 R-free 8320
1 sga heap(1,0) > 10K 9 R-free 106727248
1 sga heap(1,0) 0-1K 32126 free 4339536
1 sga heap(1,0) 1-2K 7852 free 7086152
1 sga heap(1,0) 2-3K 2032 free 3979752
1 sga heap(1,0) 3-4K 2149 free 6440488
1 sga heap(1,0) 4-5K 5491 free 21125784
1 sga heap(1,0) 5-6k 8 free 42048
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
1 sga heap(1,0) 6-7k 4 free 25344
1 sga heap(1,0) 7-8k 5 free 34280
1 sga heap(1,0) 8-9k 13 free 107136
1 sga heap(1,0) 9-10k 55 free 504192
1 sga heap(1,0) > 10K 1002 free 347343216
2 sga heap(2,0) > 10K 7 R-free 93915472
2 sga heap(2,0) 0-1K 26778 free 3903320
2 sga heap(2,0) 1-2K 3049 free 2899776
2 sga heap(2,0) 2-3K 1456 free 2965168
2 sga heap(2,0) 3-4K 2043 free 6095696
2 sga heap(2,0) 4-5K 5392 free 20821520
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
2 sga heap(2,0) 5-6k 187 free 938728
2 sga heap(2,0) 6-7k 219 free 1315544
2 sga heap(2,0) 7-8k 162 free 1125608
2 sga heap(2,0) 8-9k 257 free 2089288
2 sga heap(2,0) 9-10k 131 free 1172800
2 sga heap(2,0) > 10K 2007 free 249414944
3 sga heap(3,0) > 10K 9 R-free 120195904
3 sga heap(3,0) 0-1K 25981 free 3822576
3 sga heap(3,0) 1-2K 2066 free 2273720
3 sga heap(3,0) 2-3K 1520 free 3085896
3 sga heap(3,0) 3-4K 2046 free 6133600
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
3 sga heap(3,0) 4-5K 5519 free 21246048
3 sga heap(3,0) 5-6k 12 free 59376
3 sga heap(3,0) 6-7k 3 free 18760
3 sga heap(3,0) 7-8k 1 free 6696
3 sga heap(3,0) 8-9k 1 free 8256
3 sga heap(3,0) > 10K 22 free 225517176
4 sga heap(4,0) > 10K 10 R-free 120529960
4 sga heap(4,0) 0-1K 19989 free 3509144
4 sga heap(4,0) 1-2K 4328 free 3722568
4 sga heap(4,0) 2-3K 1422 free 2859536
4 sga heap(4,0) 3-4K 2049 free 6125832
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
4 sga heap(4,0) 4-5K 5786 free 22357232
4 sga heap(4,0) 5-6k 1 free 4536
4 sga heap(4,0) 6-7k 1 free 6160
4 sga heap(4,0) 7-8k 1 free 7168
4 sga heap(4,0) 8-9k 17 free 139328
4 sga heap(4,0) > 10K 16 free 191409560
5 sga heap(5,0) > 10K 9 R-free 106635744
5 sga heap(5,0) 0-1K 27716 free 4342656
5 sga heap(5,0) 1-2K 3501 free 3261120
5 sga heap(5,0) 2-3K 1309 free 2687240
5 sga heap(5,0) 3-4K 2112 free 6350792
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
5 sga heap(5,0) 4-5K 5987 free 23133896
5 sga heap(5,0) 6-7k 1 free 6344
5 sga heap(5,0) 8-9k 2 free 16256
5 sga heap(5,0) 9-10k 1 free 8544
5 sga heap(5,0) > 10K 68 free 310485960
6 sga heap(6,0) 0-1K 2 R-free 656
6 sga heap(6,0) 1-2K 1 R-free 768
6 sga heap(6,0) 3-4K 1 R-free 2680
6 sga heap(6,0) 4-5K 3 R-free 11864
6 sga heap(6,0) > 10K 10 R-free 119459712
6 sga heap(6,0) 0-1K 36813 free 5460272
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
6 sga heap(6,0) 1-2K 13091 free 12010656
6 sga heap(6,0) 2-3K 3477 free 6737480
6 sga heap(6,0) 3-4K 2987 free 8926000
6 sga heap(6,0) 4-5K 11191 free 43775688
6 sga heap(6,0) 5-6k 586 free 2923912
6 sga heap(6,0) 6-7k 475 free 2852320
6 sga heap(6,0) 7-8k 328 free 2280096
6 sga heap(6,0) 8-9k 1022 free 8315944
6 sga heap(6,0) 9-10k 162 free 1455032
6 sga heap(6,0) > 10K 813 free 340146992
7 sga heap(7,0) 1-2K 2 R-free 2240
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
7 sga heap(7,0) 2-3K 1 R-free 2368
7 sga heap(7,0) 5-6k 1 R-free 5168
7 sga heap(7,0) > 10K 9 R-free 106687488
7 sga heap(7,0) 0-1K 30075 free 4337392
7 sga heap(7,0) 1-2K 2704 free 2660512
7 sga heap(7,0) 2-3K 1680 free 3379600
7 sga heap(7,0) 3-4K 2103 free 6300992
7 sga heap(7,0) 4-5K 5824 free 22423800
7 sga heap(7,0) 5-6k 1 free 4888
7 sga heap(7,0) 6-7k 7 free 44280
7 sga heap(7,0) 8-9k 5 free 41368
SubPool SGA_HEAP size COUNT(*) STATUS BYTES
---------- --------------- ----- ------------------ ----------
7 sga heap(7,0) 9-10k 7 free 64184
7 sga heap(7,0) > 10K 660 free 320295616
已选择90行。
查看4031的次数
select indx,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs
2 from sys.x$kghlu where inst_id =userenv('Instance');
INDX KGHLURCR KGHLUTRN KGHLUFSH KGHLUOPS KGHLUNFU KGHLUNFS
---------- ---------- ---------- -------------------- ---------- ----------
0 49371 49932 5522200 30304101 0 0
1 53367 68424 5303976 22297579 0 0
2 50916 57568 5509344 18520250 0 0
3 51572 69747 5460528 18050945 0 0
4 52240 70093 5491960 15437329 0 0
5 41644 47627 5578368 21132753 0 0
6 43783 52091 5537920 24707939 0 0
已选择7行。
SYS@tree> spool off
trc文件:
Trace fileD:\APP\ADMINISTRATOR\diag\rdbms\primary\tree\trace\tree_j003_9780.trc
Oracle Database 11g Enterprise EditionRelease 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 32 - type 8664, 16 PhysicalCores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:92980M/131026M,Ph+PgF:102497M/141264M
Instance name: tree
Redo thread mounted by this instance: 1
Oracle process number: 38
Windows thread id: 9780, image: ORACLE.EXE(J003)
*** 2016-06-30 16:10:03.637
*** SESSION ID:(300.24353) 2016-06-3016:10:03.637
*** CLIENT ID:() 2016-06-30 16:10:03.637
*** SERVICE NAME:(SYS$USERS) 2016-06-3016:10:03.637
*** MODULE NAME:(DBMS_SCHEDULER) 2016-06-3016:10:03.637
*** ACTION NAME:(JOB_MINI_SX_DSF) 2016-06-3016:10:03.637
DDE: Problem Key 'ORA 4031' was floodcontrolled (0x2) (incident: 520573)
ORA-04031: 无法分配 4136 字节的共享内存("shared pool","unknown object","sgaheap(1,0)","modification ")
ORA-02063: 紧接着 line (起自 MINI)
DDE: Problem Key 'ORA 4031' was flood controlled(0x2) (incident: 520574)
*** 2016-06-30 16:16:00.940
*** SESSION ID:(300.24455) 2016-06-3016:16:00.940
*** CLIENT ID:() 2016-06-30 16:16:00.940
*** SERVICE NAME:(SYS$USERS) 2016-06-3016:16:00.940
*** MODULE NAME:(DBMS_SCHEDULER) 2016-06-3016:16:00.940
*** ACTION NAME:(JOB_MINI_SX_DSF)2016-06-30 16:16:00.940
ORA-04031: 无法分配 4136 字节的共享内存("shared pool","unknown object","sgaheap(1,0)","modification ")
ORA-02063: 紧接着 line (起自 MINI)
如果找不到原因就dump Free Lists链啊
alter session set events 'immediate tracename heapdump level 2';
SELECT d.value|| '/' ||lower(rtrim(i.instance,chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
(select p.spid
from v$mystat m
,v$session s
, v$process p
where m.statistic# = 1
and s.sid = m.sid
and p.addr = s.paddr) p,
(select t.instance
from v$thread t
,v$parameter v
where v.name = 'thread'
and (v.value = 0 or t.thread# = to_number(v.value))) i,
(select value
from v$parameter
where name = 'user_dump_dest' ) d;
alter session set events 'immediate tracename heapdump off';