Resolving ORA-4031 unable to allocate x bytes of shared memory

Question

I need some pointers on how to diagnose and fix this problem  I don t know if this is a simple server setup problem or an application design problem  or both    Once or twice every few months this Oracle XE database reports ORA-4031 errors  It doesn t point to any particular part of the sga consistently  A recent example is   ORA-04031  unable to allocate 8208 bytes of shared memory   large pool   unknown object   sort subheap   sort key    When this error comes up  if the user keeps refreshing  clicking on different links  they ll generally get more of these kinds of errors at different times  then soon they ll get  404 not found  page errors   Restarting the database usually resolves the problem for a while  then a month or so later it comes up again  but rarely at the same location in the program  i e  it doesn t seem linked to any particular portion of code   the above example error was raised from an Apex page which was sorting 5000  rows from a table    I ve tried increasing sga max size from 140M to 256M and hope this will help things  Of course  I won t know if this has helped since I had to restart the database to change the setting     I m running Oracle XE 10 2 0 1 0 on a Oracle Enterprise Linux 5 box with 512MB of RAM  The server only runs the database  Oracle Apex  v3 1 2  and Apache web server  I installed it with pretty much all default parameters and it s been running quite well for a year or so  Most issues I ve been able to resolve myself by tuning the application code  it s not intensively used and isn t a business critical system   These are some current settings I think may be relevant   pga aggregate target        41 943 040 sga max size              268 435 456 sga target                146 800 640 shared pool reserved size   5 452 595 shared pool size          104 857 600   If it s any help here s the current SGA sizes   Total System Global Area  268435456 bytes Fixed Size                  1258392 bytes Variable Size             251661416 bytes Database Buffers           12582912 bytes Redo Buffers                2932736 bytes

User · Accepted Answer

Even though you are using ASMM  you can set a minimum size for the large pool  MMAN will not shrink it below that value   You can also try pinning some objects and increasing SGA TARGET

User · Answer

Error   ORA-04031  unable to allocate 4064 bytes of shared memory   shared pool   select increment  minvalue m      sga heap 3 0    kglsim heap      Solution  by nepasoft nepal   1 -  ps -ef grep oracle  2 - Find the smon and kill the pid for it 3 -   SQL gt  startup mount ORACLE instance started   Total System Global Area 4831838208 bytes Fixed Size                  2027320 bytes Variable Size            4764729544 bytes Database Buffers           50331648 bytes Redo Buffers               14749696 bytes Database mounted   4 -   SQL gt  alter system set shared pool size 100M scope spfile   System altered   5 -  SQL gt  shutdown immediate  ORA-01109  database not open Database dismounted  ORACLE instance shut down   6 -  SQL gt  startup ORACLE instance started   Total System Global Area 4831838208 bytes Fixed Size                  2027320 bytes Variable Size            4764729544 bytes Database Buffers           50331648 bytes Redo Buffers               14749696 bytes Database mounted  Database opened   7 -   SQL gt  create pfile from spfile   File created     SOLVED

User · Answer

Don t forget about fragmentation   If you have a lot of traffic  your pools can be fragmented and even if you have several MB free  there could be no block larger than 4KB  Check size of largest free block with a query like    select    0   lt 140   BUCKET  KSMCHCLS  KSMCHIDX    10 trunc KSMCHSIZ 10   From     count     Count      max KSMCHSIZ   Biggest     trunc avg KSMCHSIZ    AvgSize     trunc sum KSMCHSIZ    Total  from   x ksmsp where   KSMCHSIZ lt 140 and   KSMCHCLS  free  group by   KSMCHCLS  KSMCHIDX  10 trunc KSMCHSIZ 10  UNION ALL select    1  140-267   BUCKET    KSMCHCLS    KSMCHIDX    20 trunc KSMCHSIZ 20      count        max KSMCHSIZ      trunc avg KSMCHSIZ    AvgSize     trunc sum KSMCHSIZ    Total  from   x ksmsp where   KSMCHSIZ between 140 and 267 and   KSMCHCLS  free  group by   KSMCHCLS  KSMCHIDX  20 trunc KSMCHSIZ 20  UNION ALL select    2  268-523   BUCKET    KSMCHCLS    KSMCHIDX    50 trunc KSMCHSIZ 50      count        max KSMCHSIZ      trunc avg KSMCHSIZ    AvgSize     trunc sum KSMCHSIZ    Total  from   x ksmsp where   KSMCHSIZ between 268 and 523 and   KSMCHCLS  free  group by   KSMCHCLS  KSMCHIDX  50 trunc KSMCHSIZ 50  UNION ALL select    3-5  524-4107   BUCKET    KSMCHCLS    KSMCHIDX    500 trunc KSMCHSIZ 500      count        max KSMCHSIZ      trunc avg KSMCHSIZ    AvgSize     trunc sum KSMCHSIZ    Total  from   x ksmsp where   KSMCHSIZ between 524 and 4107 and   KSMCHCLS  free  group by   KSMCHCLS  KSMCHIDX  500 trunc KSMCHSIZ 500  UNION ALL select    6   4108    BUCKET    KSMCHCLS    KSMCHIDX    1000 trunc KSMCHSIZ 1000      count        max KSMCHSIZ      trunc avg KSMCHSIZ    AvgSize     trunc sum KSMCHSIZ    Total  from   x ksmsp where   KSMCHSIZ  gt   4108 and   KSMCHCLS  free  group by   KSMCHCLS  KSMCHIDX  1000 trunc KSMCHSIZ 1000     Code from

User · Answer

All of the current answers are addressing the symptom  shared memory pool exhaustion   and not the problem  which is likely not using bind variables in your sql   JDBC queries  even when it does not seem necessary to do so  Passing queries without bind variables causes Oracle to  hard parse  the query each time  determining its plan of execution  etc    https   asktom oracle com pls asktom f p 100 11 0    p11 question id 528893984337  Some snippets from the above link    Java supports bind variables  your developers must start using prepared statements and bind inputs into it  If you want your system to ultimately scale beyond say about 3 or 4 users -- you will do this right now  fix the code   It is not something to think about  it is something you MUST do  A side effect of this - your shared pool problems will pretty much disappear  That is the root cause      The way the Oracle  shared pool  a very important shared memory data structure   operates is predicated on developers using bind variables      Bind variables are SO MASSIVELY important -- I cannot in any way shape or form OVERSTATE their importance

User · Answer

This is Oracle bug  memory leak in shared pool  most likely db managing lots of partitions  Solution  In my opinion patch not exists  check with oracle support  You can try with subpools or en de able AMM

User · Answer

The following are not needed as they they not fix the error    ps -ef grep oracle Find the smon and kill the pid for it SQL gt  startup mount SQL gt  create pfile from spfile    Restarting the database will flush your pool and that solves a effect not the problem   Fixate your large pool so it can not go lower then a certain point or add memory and set a higher max memory

[oracle] Resolving ORA-4031 "unable to allocate x bytes of shared memory"

Examples related to oracle

Examples related to memory-management

Examples related to oracle10g