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

Fayum

Check out this link Oracle Help - ORA04031 – Empennage

additional info: download.oracle.com/docs/cd/B19306_01/server.102/b14231/… – Fayum

btw large_pool_size is 0 (i.e. automatically managed by ASMM) – Fayum

512M of RAM seems low for the database config + other processes you've mentioned. What do tools like top or vmstat tell you about memory at the OS level? – Sporades

top indicates most of the 512MB is being used - e.g. currently reports 25-40 MB free. I'm not familiar with vmstat, but it reports: swpd=20916 free=40768 buff=8444 cache=341248 – Fayum

Answer 1

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.

Scammon

sounds reasonable, I'll give these a go. – Fayum

I've set the min size for the large pool and increased sga_target to the same as sga_max_size. I'll see how it goes, thanks. – Fayum

I'll accept this as the answer because I think it's the best advice, even though to test this will take the next month or so to see if the errors happen again. – Fayum

Well, it's been a year now, and our regular spike of activity in April/May so far has yielded no memory errors or server downtime at all. Looking good :) – Fayum

Answer 2

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 (<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<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 >= 4108
and
  KSMCHCLS='free'
group by
  KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);

Code from

Damage

+1 for the query. Next time I get these errors I'll use it again to see if that's the problem. Thanks! – Fayum

Answer 3

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. "

Phonics

This answer may very well help someone else who has similar symptoms. FWIW in my case I was using Apex, not Java, and mostly using bind variables already. Thanks – Fayum

Answer 4

The following are not needed as they they not fix the error:

1. ps -ef|grep oracle
2. Find the smon and kill the pid for it
3. SQL> startup mount
4. SQL> 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.

Shortlived

Welcome to OS. Thank you for providing a answer to the question, but please, pay attention to the fact that the question is 5 years old and answered already, pretty much stating the same as you. Cheers and happy coding :) – Entopic

Answer 5

I have faced similar error while doing 2 node RAC Patching 19.15 RU , while starting the one node after patching, DB Instance start rebooting with below error:

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

So we have done temporary fix by setting the shared pool parameter depending on DB Size and db_cache parameter value :

SQL> alter system set shared_pool_size=10G scope=spfile;

System altered.

Bollworm

Answer 6

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> 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> alter system set shared_pool_size=100M scope=spfile;
  
  System altered.
  

• 5.-

  SQL> shutdown immediate
  
  ORA-01109: database not open
  Database dismounted.
  ORACLE instance shut down.
  

• 6.-

  SQL> 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> create pfile from spfile;
  
  File created.
  

SOLVED

Cranwell

OS: Solaris DB: oracle 10g – Cranwell

um, how exactly do you think setting my shared pool size is going to fix my problem - especially considering the fact that it is already 104,857,600 (please read the question) – Fayum

Answer 7

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 ...

Battologize