Frequent ORA-04031
Hi All,
We are experiencing frequent ORA-04031 Error. Every time it seems like related to BAMIMA: Bam Buffer.
Below is the error message we get every time....
#############################################################
ORA-04031: unable to allocate 86840 bytes of shared memory ("shared pool","CSI_DATASTRUCTURES_PUB_W","PL/SQL MPCODE","BAMIMA: Bam Buffer") ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at "APPS.CSI_JAVA_INTERFACE_PKG_W", line 544 ORA-06512: at line 1.
#############################################################
Every time we get this error as a workaround we are flushing shared pool. We would like to have a permamnent fix for this error.
Previous time we observed the error we captured status from shared pool using sqls mentioned in the note on how to trouble shoot the error. Below are the details.
###
PMLDVI> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
2 To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
3 FROM X$KSMSP GROUP BY KSMCHCLS;
CLASS NUM SIZ AVG SIZE
-------- ---------- ---------- ------------
R-free 3143 31298556 9.72k
R-freea 3321 22229092 6.54k
R-perm 3791 43872580 11.30k
R-recr 3 564 .18k
free 52920 165384092 3.05k
freeabl 455464 491624024 1.05k
perm 8305 126528064 14.88k
recr 164651 92139468 .55k
PMLDVI> select component, current_size, min_size, max_size, granule_size from v$sga_dynamic_components
/ 2 3
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------
shared pool 905969664 905969664 905969664 16777216
large pool 201326592 201326592 201326592 16777216
buffer cache 486539264 486539264 486539264 16777216
PMLDVI> select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
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 = 'free memory'
8 group by ksmchidx, ksmchcls,
9 'sga heap('||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');
SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS BYTES
---------- --------------- ---------------- ---------- ---------- -------- ----------
1 sga heap(1,0) free memory 0-1K 14803 free 1659324
1 sga heap(1,0) free memory 1-2K 3866 free 3029472
1 sga heap(1,0) free memory 2-3K 1546 free 2800644
1 sga heap(1,0) free memory 3-4K 9592 free 29320940
1 sga heap(1,0) free memory 4-5K 6981 free 26635544
1 sga heap(1,0) free memory 5-6k 68 free 329244
1 sga heap(1,0) free memory 6-7k 9 free 52448
1 sga heap(1,0) free memory 7-8k 24 free 169648
1 sga heap(1,0) free memory 8-9k 30 free 239672
1 sga heap(1,0) free memory 9-10k 3 free 25852
1 sga heap(1,0) free memory > 10K 2 free 21636
1 sga heap(1,0) free memory 0-1K 110 R-free 23632
1 sga heap(1,0) free memory 1-2K 118 R-free 117192
1 sga heap(1,0) free memory 2-3K 93 R-free 183976
1 sga heap(1,0) free memory 3-4K 97 R-free 301824
1 sga heap(1,0) free memory 4-5K 285 R-free 1131096
1 sga heap(1,0) free memory 5-6k 44 R-free 217448
1 sga heap(1,0) free memory 6-7k 34 R-free 204612
1 sga heap(1,0) free memory 7-8k 40 R-free 281616
1 sga heap(1,0) free memory 8-9k 149 R-free 1210056
1 sga heap(1,0) free memory 9-10k 42 R-free 372648
1 sga heap(1,0) free memory > 10K 529 R-free 7758560
2 sga heap(2,0) free memory 0-1K 12287 free 598420
2 sga heap(2,0) free memory 2-3K 4 free 7856
2 sga heap(2,0) free memory 3-4K 2 free 6276
2 sga heap(2,0) free memory 4-5K 5 free 19316
2 sga heap(2,0) free memory 6-7k 1 free 5576
2 sga heap(2,0) free memory 9-10k 1 free 9264
2 sga heap(2,0) free memory > 10K 4080 free 96903548
2 sga heap(2,0) free memory 0-1K 57 R-free 7852
2 sga heap(2,0) free memory 1-2K 42 R-free 41756
2 sga heap(2,0) free memory 2-3K 52 R-free 105100
2 sga heap(2,0) free memory 3-4K 59 R-free 184188
2 sga heap(2,0) free memory 4-5K 134 R-free 537868
2 sga heap(2,0) free memory 5-6k 58 R-free 286236
2 sga heap(2,0) free memory 6-7k 53 R-free 322096
2 sga heap(2,0) free memory 7-8k 73 R-free 511620
2 sga heap(2,0) free memory 8-9k 127 R-free 1016488
2 sga heap(2,0) free memory 9-10k 52 R-free 465852
2 sga heap(2,0) free memory > 10K 894 R-free 15876048
###
Also the init variable values the db using is as below...
shared_pool_size = 850M
db_cache_size = 450M
shared_pool_reserved_size = 100000000
sharedpool_reserved_min_alloc = 4100
olap_page_pool_size = 4194304
All these variables seem like having proper values....but still the issues are occuring almost this error is getting reported for every twodays.
If any body has experienced this situation, please let me know how you could get around with this.
If already these variables are at proper values.. please let me know whether there is any way i can detect the condiction before and prior the error is raised so that i can automatically detect and flush shared pool, so that i can atleast prevent the error from occuring.
Thanks & Regards.