Solved

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

Posted on 2002-04-11
21
8,057 Views
Last Modified: 2008-03-03
I keep getting this error:

ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","unknown object","sga heap","state objects")

I have raised the amount of the shared pool many times, but after some days I get the same error again. I have read everything I could on the web, and came to the conclusion that the cause could be fragmentation.

I know that using bind variables may help to save shared memory, but I need to find another way since I have no control over the query that are executed by a tool from a third party.

Thank you
0
Comment
Question by:cip
  • 5
  • 5
  • 4
  • +5
21 Comments
 
LVL 4

Expert Comment

by:Bhatti
ID: 6933876
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
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6933968
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
0
 
LVL 3

Expert Comment

by:graf27
ID: 6934016
ON 9.0.1.2, its a BUG
0
 
LVL 1

Author Comment

by:cip
ID: 6934035
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?
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6934149
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

0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6934206
Good job.  Right from the book.  Paste?



Andrew
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6934422
excuse me, Metalink "Diagnosing and Resolving Error ORA-04031", Doc ID: 146599.1
0
 
LVL 1

Author Comment

by:cip
ID: 6934466
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.
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6934482
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
0
 
LVL 1

Author Comment

by:cip
ID: 6934536
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:asimkovsky
ID: 6934620
Log in as SYS.

Andrew
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6934684
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';
0
 
LVL 1

Author Comment

by:cip
ID: 6934832
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?

0
 
LVL 5

Expert Comment

by:sora
ID: 6935768
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
0
 
LVL 3

Expert Comment

by:graf27
ID: 6936905
The current release on Solaris (32 Bit) without ORA-4031 Bug is: 8.1.7.3 !
0
 
LVL 1

Author Comment

by:cip
ID: 6936958
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

0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6963420
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
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6963430
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
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7063154
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 **
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7082366
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
0
 
LVL 6

Accepted Solution

by:
Mindphaser earned 300 total points
ID: 7096086
moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now