ORA-04031: unable to allocate 4200 bytes of shared memory

I get this information from Oracle.

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 set for the SHARED_POOL_SIZE initialization parameter.

I think SHARED_POOL_SIZE can help you.

Best regards

Bhatti

Set up a job through DBMS_JOB to flush the shared pool every hour on the hour (or less frequent for a busy system, maybe every four hours.)  Be sure to pin your frequently used packages, procedures, functions, etc.

Andrew

ON 9.0.1.2, its a BUG

Thanks.

I'm trying the DBMS_SHARED_POOL and DBMS_JOB to see if they can help.

graft27 I'm using version 8.1.7, does that version have the bug?

hi there,

The ORA-04031 error is usually due to fragmentation in the library cache or shared pool reserved space.  Before of increasing the shared pool size consider to tune the application to use shared sql and tune SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, and SHARED_POOL_RESERVED_MIN_ALLOC.
First determine if the ORA-04031 was a result of fragmentation in the library
cache or in the shared pool reserved space by issuing the following query:

SELECT free_space, avg_free_size, used_space,
avg_used_size, request_failures, last_failure_size
FROM    v$shared_pool_reserved;
The ORA-04031 is a result of lack of contiguous space in the shared pool
reserved space if:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > 
SHARED_POOL_RESERVED_MIN_ALLOC.

To resolve this  consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower
the number of objects being cached into the shared pool reserved space and
increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the
available memory in the shared pool reserved space.

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

or

REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

The first step would be to consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to
put more objects into the shared pool reserved space and increase
SHARED_POOL_SIZE.
 
Eric

Good job.  Right from the book.  Paste?



Andrew

excuse me, Metalink "Diagnosing and Resolving Error ORA-04031", Doc ID: 146599.1

Thanks ORACLEtune,

I ran the query as suggested, getting this output:

FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
  20971520      20971520          0             0                0                0

I think there are no failures because the last time I reset the shared pool was yesterday (with a db restart). Shared pool is now set to 400mb. Should I wait for the next failure to do the appropriate tests?

I also cannot find the SHARED_POOL_RESERVED_MIN_ALLOC parameter anywhere, maybe it's another version, I have 8.1.7.

I'll try the metalink as soon as I'll be able to get a csi.

It has become an undocumented parameter and starts with an underscore ( _shared_pool_reserved_min_alloc ).  It can be found by querying the X$ tables:

SELECT   a.ksppinm "Parameter",
         b.ksppstvl "Value",
         a.ksppdesc "Description"
FROM     x$ksppi a, x$ksppcv b
WHERE    a.indx = b.indx                 AND
         a.ksppinm LIKE '/_%' ESCAPE '/' AND
      a.ksppinm like lower('%shared_pool%')
ORDER BY 1

Andrew

Andrew, I get this error trying your query:

FROM     x$ksppi a, x$ksppcv b
                    *
ERROR at line 4:
ORA-00942: table or view does not exist

Sorry, I think I need more details.

Thanks

Log in as SYS.

Andrew

here are stats on my shared pool:

10:10:43 sys@DES6I>/

SHARED_POOL_SIZE SUM_OBJ_SIZE SUM_SQL_SIZE SUM_USER_SIZE MIN_SHARED_POOL
---------------- ------------ ------------ ------------- ---------------
      22,194,304   12,727,499    9,113,944        69,125      28,483,738

10:10:48 sys@DES6I>

This script provides the following items: Current Shared Pool Size, Sum of Shared Pool Objects, Sum of SQL Size, Sum of User Size and the Minumum Recommended Shared Pool Size for Instance.


column shared_pool_size format 999,999,999
column sum_obj_size format 999,999,999
column sum_sql_size format 999,999,999
column sum_user_size format 999,999,999
column min_shared_pool format 999,999,999
select to_number(value) shared_pool_size,
                         sum_obj_size,
                         sum_sql_size,
                         sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
  from (select sum(sharable_mem) sum_obj_size
          from v$db_object_cache),
               (select sum(sharable_mem) sum_sql_size
          from v$sqlarea),
               (select sum(250 * users_opening) sum_user_size
          from v$sqlarea), v$parameter
 where name = 'shared_pool_size';

ORACLEtune, these are my stats:

SHARED_POOL_SIZE SUM_OBJ_SIZE SUM_SQL_SIZE SUM_USER_SIZE MIN_SHARED_POOL
---------------- ------------ ------------ ------------- ---------------
     419,430,400    6,845,597      894,801         1,250      10,064,142

I'll keep an eye to the db the next days to see how those stats change (I guess the recommended size for the shared pool will increase as the db is used). I have also increased the value of the size of SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE as suggested in the documentation. I still cannot say if the information you provided has solved the problem, I'll have to wait some days to see if the error repeats. Should I accept one of the answers meanwhile?

I am almost certain that you are hitting bug 1397603 and you should upgrade to 8.1.7.2

1. are the third party tools using java? If so increase the java_pool_size in your <init>sid.ora

2. use CURSOR_SHARING=FORCE

3. Do you use MTS? If so, then configure the large pool.

4. set the event in the init<sid>.ora and see which SQLs or packages get reported. You can set event using this line in your init<sid>.ora file and restart the instance.

"4031 trace name errorstack level 4" will cause a trace file to be generated

5. what is the size of your shared pool, now?

6. 8.1.7.0 and 8.1.7.1 on solaris had some memory leak bugs. This is fixed in 8.1.7.2. upgrade to this patchset. It is a simple job and can be done with a minimal downtime of about an hour.

7. I have found, that for temporary resolutions, until the problem is resolved, Andrew's suggestion of periodically flushing the shared pool using a cron job etc to be very useful


sora

The current release on Solaris (32 Bit) without ORA-4031 Bug is: 8.1.7.3 !

Thanks sora and graf27, my application is running on Microsoft Windows 2000 Server.

Symptoms are *very* similar to bug 1397603. Metalink says that if the problem goes away when you put _db_handles_cached = 0, then I'm hitting the bug. I'll do some testing.

Thanks

hi there,

Hope you and your database are well.  Below is another doc on shared pool sizing (a fun excersize), it may be of use to you:

Calculating the Size of the Shared Pool


Shared pool is the amount of fixed, preallocated space in the SGA for use by multi-threaded server session PGA, shared SQL area, and other small, dynamically allocated SGA data structures.


Shared Pool
===========
Since shared pool usage is highly application dependent, it is necessary to examine each database application individually in order to project a recommended shared pool value.

While analyzing shared pool sizing, it is helpful to first increase the shared pool to a very large value, so that the dynamically allocated SGA structures may be allowed to expand to a desirable size.  Once this sizing exercise has been completed, the shared pool may be downsized to the
appropriate value.

Shared pool calculation is especially critical when the multi-threaded server is in use because the PGA for each multi-threaded server database user will be allocated from shared pool.


Formula
-------

        Max Session Memory * No. of concurrent users

        + Total Shared SQL Area Usage

        + PLSQL Sharable Memory

        + Minimum 30% Free Space
        ----------------------------------------------
        = Minimum Allowable Shared Pool


Example
-------

   Find the SID for an example user session:

        SQLDBA> select sid from v$process p, v$session s
             2> where p.addr=s.paddr and s.username='OPS$JSMITH';
        SID
        ----------
                 29
        1 rows selected.


   Get the maximum session memory for this session:

        SQLDBA> select value from v$sesstat s, v$statname n
             2> where s.statistic# = n.statistic#
             3>   and n.name = 'session uga memory max'
             4>   and sid=29;
        VALUE
        ----------
            273877
        1 rows selected.


   Get the total shared SQL area:

        SQLDBA> select sum(sharable_mem) from v$sqlarea;
        SUM(SHARAB
        ----------
           8936625
        1 row selected.


   Get the PLSQL sharable memory area:

        SQLDBA> select sum(sharable_mem) from v$db_object_cache;
        SUM(SHARAB
        ----------
           4823537
        1 row selected.


   Example shared pool calculation:

        274K shared memory * 400 users

        +    9M Shared SQL Area

        +    5M PLSQL Sharable Memory

        +   60M Free Space (30%)
        ---------------------------------
        =  184M Shared Pool


   In this example, the recommended shared pool value is 184M.


Out of Shared Memory (Error ORA-4031)
-------------------------------------
The 2 most common causes of the error "Out of shared memory" are lack of available shared pool and lack of available contiguous shared pool into which to map large PL/SQL packages.  In order to avoid the latter, it is recommended that the application DBA pin all large packages using the sys.dbms_shared_pool.keep procedure.
.

Eric

earlier you stated: "Should I accept one of the answers meanwhile?"

you are doing the right thing... wait until you are comfortable with an answer/comment/solution.

 we are here to serve & learn.
Eric

Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

Lacking timely response from cip or contributing experts, but seeing that there is useful information here, I recommend:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer