This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Apr 12, 2013 5:22 AM by Jaroslaw Bicz RSS

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

549694 Newbie
Currently Being Moderated
Hi,

I am using a Oracle 10g with solaris.
For the last few days I am getting this error.

insert into ENTITY_FUNCTION (
*
ERROR at line 1:
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","kglsim heap")

I have to manually allocate memory for shared pool, but it is not working.
I am also attaching the output of some querries I have tried:

SQL> show parameters size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 8192
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recovery_file_dest_size big integer 2G
db_recycle_cache_size big integer 0
global_context_pool_size string
hash_area_size integer 131072
java_max_sessionspace_size integer 0
java_pool_size big integer 0
large_pool_size big integer 0
max_dump_file_size string UNLIMITED
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size big integer 0
parallel_execution_message_size integer 2152

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 4G
shared_pool_reserved_size big integer 208037478
shared_pool_size big integer 400M
sort_area_retained_size integer 0
sort_area_size integer 65536
streams_pool_size big integer 0
workarea_size_policy string AUTO

SQL> sho parameters sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 4G


SQL> select * from ( select POOL, NAME, BYTES, BYTES/1048576 as MBytes from v$sgastat where pool='shared pool' order by BYTES desc ) where rownum <= 25;

POOL NAME BYTES MBYTES
------------ -------------------------- ---------- ----------
shared pool free memory 3.6893E+19 3.5184E+13
shared pool library cache 3032510000 2892.0269
shared pool sql area 59354056 56.6044388
shared pool CCursor 43099440 41.102829
shared pool PCursor 37529896 35.7912979
shared pool ASH buffers 16252928 15.5
shared pool private strands 8057856 7.68457031
shared pool row cache 7480368 7.13383484
shared pool kglsim object batch 7349760 7.00927734
shared pool event statistics per sess 5079360 4.84405518
shared pool FileOpenBlock 4612104 4.39844513
shared pool CURSOR STATS 4405344 4.20126343
shared pool KTI-UNDO 4295864 4.09685516
shared pool KCB Table Scan Buffer 4198400 4.00390625
shared pool kglsim hash table bkts 4194304 4
shared pool KSFD SGA I/O b 4190248 3.9961319
shared pool KGLS heap 4119136 3.92831421
shared pool sessions 2877128 2.74384308
shared pool kglsim heap 2689344 2.5647583
shared pool trace buffer 2359296 2.25
shared pool KQR M PO 2273904 2.16856384
shared pool Heap0: KGL 1987728 1.89564514

POOL NAME BYTES MBYTES
------------ -------------------------- ---------- ----------
shared pool PL/SQL MPCODE 1698728 1.62003326
shared pool kks stbkt 572864 1.5
shared pool PL/SQL DIANA 1469488 1.40141296

25 rows selected.

SQL>
SQL> select * from V$LIBRARY_CACHE_MEMORY;

LC_NAMESPACE LC_INUSE_MEMORY_OBJECTS LC_INUSE_MEMORY_SIZE LC_FREEABLE_MEMORY_OBJECTS LC_FREEABLE_MEMORY_SIZE
--------------- ----------------------- -------------------- -------------------------- -----------------------
BODY 0 0 35 1
CLUSTER 0 0 0 0
INDEX 0 0 0 0
JAVA DATA 0 0 0 0
JAVA RESOURCE 0 0 0 0
JAVA SOURCE 0 0 0 0
OBJECT 0 0 0 0
OTHER/SYSTEM 0 0 4 0
PIPE 0 0 0 0
SQL AREA 512 2 7698 69
TABLE/PROCEDURE 0 0 581 2
TRIGGER 0 0 0 0

12 rows selected.


Please halp.
  • 1. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    Raman Newbie
    Currently Being Moderated
    Hi,

    try to do flushing of shared_pool and try to restart the system.. it seems your SGA defragmented
    ====================================
    SQL> alter system flush shared_pool;

    System altered.
    ===================================
    ##As per metalink##

    ORA-04031 unable to allocate string bytes of shared memory ("string","string","string","string")

    Cause: More shared memory is needed than was allocated in the shared pool.

    Action: If the shared pool is out of memory, 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.

    --Thanks
    Raman
  • 2. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    Raman Newbie
    Currently Being Moderated
    ## I have to manually allocate memory for shared pool, but it is not working. ##

    how much did you allocate?? are you using spfile or pfile??

    Thanks

    --Raman                                                                                                                                                                                                                                                                                                                                       
  • 3. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    247514 Expert
    Currently Being Moderated
    1. Since you have SGA_TARGET, shared_pool_size is ignored in parameter file

    2. You might have shared fragment problem caused by not using binding variable in queries. So even it appears you have free space in shared pool, you can't allocation enough space for a query.

    3. To future trouble shoot the problem run statspack reports.
  • 4. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    549694 Newbie
    Currently Being Moderated
    I have used 'alter system flush'. but i am getting the same error after 10 - 15 minutes. Even after restarting the db, it is giving the same error after 10 - 15 hours.
    But i guess this is not the permanent fix.
    I am using an spfile. I allocated 400M with 'alter system .... scope spfile' and then restarted the db.
  • 5. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    549694 Newbie
    Currently Being Moderated
    I think when i specified SGA_TARGET, the value for SHARED_POOL_SIZE is maintained as a minimum value.

    The thing I noticed are 7689 LC_FREEABLE_MEMORY_OBJECTS in SQL Area.
  • 6. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    561093 Oracle ACE
    Currently Being Moderated
    Increase your SHARED_POOL_SIZE parameter and bounce the database
  • 7. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    549694 Newbie
    Currently Being Moderated
    can you please explain what do you mean by 'bounce the database' ?
  • 8. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    561093 Oracle ACE
    Currently Being Moderated
    restart the database
  • 9. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    247514 Expert
    Currently Being Moderated
    By setting SGA_TARGET you are using automatic SGA management. There's no need to manual set the value of shared_pool_size in this case, Oracle will internally transfer memory between the SGA components.

    http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#sthref1257

    Like I said, your problem is more likely to be shared pool fragment problem. No matter how much memory you give to shared pool, eventually the problem will come back again if you don't deal with the source of the problem.
  • 10. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    627172 Newbie
    Currently Being Moderated
    Hi,

    I am also facing this same error. Oracle note: 396940.1mentions that The memory pool in the SGA are comprised of memory chunks in various sizes.When the database starts is started, you have a large chunk of memory allocated in the various pools and tracked in free list hash buckets. Over time, as memory is allocated and deallocated, the memory chunks are moved around into different free list buckets inside the pool according to their size. An ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a memory chunk large enough to satisfy an internal allocation request on behalf of a user's operation.

    On Viewing the X$KSMSP view to see more detail on free memory chunks available, i found the following ouput:

    SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS BYTES

    ---------- --------------- ---------------- ---------- ---------- -------- ----------

    1 sga heap(1,0) free memory 0-1K 66145 free 7180932

    1 sga heap(1,0) free memory 1-2K 29545 free 23842992

    1 sga heap(1,0) free memory 2-3K 6825 free 14908428

    1 sga heap(1,0) free memory 3-4K 15728 free 48203932

    1 sga heap(1,0) free memory 4-5K 7597 free 29292104

    1 sga heap(1,0) free memory 5-6k 369 free 1823176

    1 sga heap(1,0) free memory 6-7k 89 free 540484

    1 sga heap(1,0) free memory 7-8k 70 free 501408

    1 sga heap(1,0) free memory 8-9k 55 free 437264

    1 sga heap(1,0) free memory 9-10k 9 free 79284

    1 sga heap(1,0) free memory > 10K 499 free 9241472

    1 sga heap(1,0) free memory > 10K 82 R-free 109957692

    The document mentions that if the majority of free memory chunks are sized 0-1K. Then it would indicate this database is likely to see an ORA-04031 error in the near future.

    The error was temporarily resolved by flushing the shared pool, but i want to know if anybody has the knowledge about
    1. How oracle determines the chunk size it has to allocate for a query?
    2. How can we control the allocation of this chunk size ?
    3 .Is there any OS specific settings that can affect this size ? because we are facing this problem only on one server. The server is on Linux.

    Error that i got:
    E03020003: Database error [CAI/PT][ODBC Oracle Dynamic driver][Oracle]ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","SELECT DISTINCT F.FORMOBJID,...","sga heap(1,0)","kglsim heap")
    Automatic SGA management is disabled.
  • 11. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    user593778 Newbie
    Currently Being Moderated
    I have the same Prob on different servers.
    Talking with Oracle Support the Problem is, when automatic memory management is turned on, sometimes resizing the SGA (shared_pool) becomes to small. (BUG in Oracle)
    Workaround: set shared_pool_size init-Parameter ,so shared pool could not reduced to a size lower the init-parameter-size.
    In most cases the Problems come on Linux Installations with 32bit.
  • 12. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    How does the shared pool look like? A typical cause is fragmentation of the shared pool due to a lack of sharable SQL. But that is not the sole cause (e.g. have a look at Metalink note 556140.1).

    However, based on what you described it sounds like Oracle is indeed being badly abused by non-sharable SQLs. This is a common occurrence using Java based applications... unfortunately. Sadly, J2EE developers mostly have their heads up their backsides when it comes to correctly using Oracle.

    Assuming that it is caused by abusive non-sharable SQL (easy to verify by looking at V$SQLAREA), the very worse thing you can do is to increase the shared pool.

    This only moves the wall a few metres away.. allowing you to run even faster into it. Performance will degrade even more. The error will not go away.

    In this case you will need to look at temporary workarounds until the SQL abusing applications are corrected, by:
    - forcing cursor sharing
    - reducing the size of shared pool (and not increasing it!), and flushing it often to rid it of the abusive SQLs
  • 13. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > Increase your SHARED_POOL_SIZE parameter and bounce the database

    Exactly what one should NOT be doing if this SGA memory problem is a result of shared pool fragmentation by non-sharable SQL.

    I suggest that the actual cause be identified first before shooting from the hip and giving advice that could very well worsen the situation.
  • 14. Re: ORA-04031: unable to allocate 4032 bytes of shared memory
    732311 Newbie
    Currently Being Moderated
    Hi,
    I have the same problem, but in this moment I can't restart the db.
    This is the error:
    bash-3.00$ sqlplus /NOLOG
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 12 14:38:29 2010
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    SQL> connect / as sysdba
    Connected to an idle instance.
    SQL> shutdown immediate
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select u.name, o.name, trigg...","sql area","kglhin: temp")
    You suggest something? How i can restart the db?

    Cheers,
    Lain
1 2 Previous Next