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

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

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

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.

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

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

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