| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> ora-4031- Advice on what to look at next time
Our production instance started getting ora-4031 errors around 6pm on Friday 19th.
I was called by our users around 9am on Sunday 21st and problem persisted and I decided to restart the instance around 11:30am Sunday 21st.
What I would like is some advice on what other information I should collect next and advice on any settings that may need altering.
First up, this is 81714 on NT4 and the instance had been up for 116 days.
The error messages seen were like the following
ORA-04031: unable to allocate 4096 bytes of shared memory
("shared pool","BEGIN :V001 := 'CDAT=' ||...","PL/SQL MPCODE","BAMIMA: Bam Buffer")
and
ORA-04031: unable to allocate 4200 bytes of shared memory
("shared pool","unknown object","sga heap","state objects")
I was following Metalink note 146599.1 and talking to support and the following was found:
09:26:35 SQL> SELECT free_space, avg_free_size, used_space, 09:26:35 2 avg_used_size, request_failures, last_failure_size 09:26:35 3 FROM v$shared_pool_reserved; FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE ---------- ------------- ---------- ------------- ---------------- ----------------- 8000000 8000000 0 0 53 4216
In 8i the parameter _shared_pool_reserved_min_alloc is hidden but its value is at the default of 4400.
And the other settings are:
shared_pool_reserved_size 8,000,000 (the default)
shared_pool_size 160,000,000
>From the Metalink note, The ORA-04031 is a result of lack of contiguous space in the library cache if:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
It suggests lowering SHARED_POOL_RESERVED_MIN_ALLOC to put more objects into the shared pool reserved space and increase SHARED_POOL_SIZE. What do others think of this and what values would be suggested? (Ixora (http://www.ixora.com.au/newsletter/2000_07.htm) has stated that the minimum value for SHARED_POOL_RESERVED_MIN_ALLOC is 4000).
Given that we are not using the reserved pool at all (based on used_space=0 in above query) I guess setting it to 4000 could be useful.
Other information follows that may help: 09:33:40 SQL> SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0; -- the first time this query was run.
KSMLRCOM KSMLRSIZ KSMLRNUM KSMLRHON
-------------------- ---------- ---------- ------------------------------- BAMIMA: Bam Buffer 4092 8 XMLDOM BAMIMA: Bam Buffer 4100 8 DBMS_SPACE_ADMIN BAMIMA: Bam Buffer 4116 392 DBMS_RCVMAN ksfqpar 4124 632 BAMIMA: Bam Buffer 4128 264 DBMS_RCVMAN BAMIMA: Bam Buffer 4132 9040 begin PG_RMIC.PB_RMIC_CHARGE... library cache 4192 1016 state objects 4216 7800 qry_text : prsqry 4320 1992 ( SELECT CM AS i163430, CAST... trigger source 4340 8216 TG_LOGON_SET_SCHEMA
Now we do use RMAN.
We have all our application objects + all the publicly executable standard Oracle code kept in the shared pool.
09:45:52 SQL> select * from v$sgastat 09:49:11 2 where pool = 'shared pool' and 09:49:16 3 upper(name) like '%FRE%' 09:49:23 4 or 09:49:24 5 upper(name) like '%MISC%'; POOL NAME BYTES ----------- -------------------------- ---------- shared pool free memory 40672808 shared pool miscellaneous 85312892 shared pool message pool freequeue 124552
The max version count in the v$sqlarea was 7.
Support suggested & I have set the following event to hopefully capture more information if it occurs again: 15:21:27 SQL> select name,value from v$parameter where name = 'event';
NAME VALUE
----- -----------------------------------event 4031 trace name errorstack level 3
Thanks for reading this far and all comments are appreciated.
Thanks,
Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: Bruce.Reardon_at_comalco.riotinto.com.au Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Apr 22 2002 - 01:23:19 CDT
 |
 |