| ORA | 您所在的位置：网站首页 › ora02063紧接着line起自 › ORA |
|
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';
|
| CopyRight 2018-2019 实验室设备网 版权所有 |