oracle 解决 ORA-4031“无法分配 x 字节的共享内存”

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

提问by Jeffrey Kemp

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:

此 Oracle XE 数据库每隔几个月报告一次或两次 ORA-4031 错误。它并没有始终指向 sga 的任何特定部分。最近的一个例子是：

ORA-04031: unable to allocate 8208 bytes of shared memory ("large pool","unknown object","sort subheap","sort key")

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.

当这个错误出现时，如果用户不断刷新，点击不同的链接，他们通常会在不同的时间收到更多此类错误，然后很快他们就会收到“404 not found”页面错误。

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

重新启动数据库通常可以解决问题一段时间，然后大约一个月后它再次出现，但很少在程序中的同一位置（即它似乎没有链接到代码的任何特定部分）（上面的例子错误是从 Apex 页面引发的，该页面正在对表中的 5000 多行进行排序）。

I've tried increasing sga_max_sizefrom 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 :)

我试过sga_max_size从 140M 增加到 256M，希望这会有所帮助。当然，我不知道这是否有帮助，因为我必须重新启动数据库才能更改设置:)

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.

我在具有 512MB RAM 的 Oracle Enterprise Linux 5 机器上运行 Oracle XE 10.2.0.1.0。服务器仅运行数据库、Oracle Apex (v3.1.2) 和 Apache Web 服务器。我用几乎所有的默认参数安装了它，它已经运行了一年左右。大多数问题我都可以通过调整应用程序代码自行解决；它没有被密集使用，也不是一个关键业务系统。

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:

如果有任何帮助，这里是当前的 SGA 大小：

Total System Global Area  268435456 bytes
Fixed Size                  1258392 bytes
Variable Size             251661416 bytes
Database Buffers           12582912 bytes
Redo Buffers                2932736 bytes

采纳答案by Jeffrey Kemp

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.

即使您使用的是 ASMM，您也可以为大池设置最小大小（MMAN 不会将其缩小到该值以下）。您还可以尝试固定一些对象并增加 SGA_TARGET。

回答by slovon

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:

不要忘记碎片化。如果您有大量流量，您的池可能会碎片化，即使您有几 MB 可用空间，也不可能有大于 4KB 的块。使用如下查询检查最大空闲块的大小：

 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

代码来自

回答by mancini0

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.

当前所有的答案都在解决症状（共享内存池耗尽），而不是问题，这可能不是在您的 sql \ JDBC 查询中使用绑定变量，即使似乎没有必要这样做。传递没有绑定变量的查询会导致 Oracle 每次“硬解析”查询，确定其执行计划等。

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:528893984337

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

“Java 支持绑定变量，您的开发人员必须开始使用准备好的语句并将输入绑定到其中。如果您希望您的系统最终扩展到超过 3 或 4 个用户——您将立即执行此操作（修复代码）。它是不需要考虑，这是您必须做的事情。这样做的副作用 - 您的共享池问题几乎会消失。这就是根本原因。”

"The way the Oracle shared pool (a very important shared memory data structure) operates is predicated on developers using bind variables."

“Oracle 共享池（一种非常重要的共享内存数据结构）的运行方式取决于使用绑定变量的开发人员。”

" Bind variables are SO MASSIVELY important -- I cannot in any way shape or form OVERSTATE their importance. "

“绑定变量非常重要——我不能以任何方式塑造或形成它们的重要性。”

回答by Tonny de Groot

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;

1. ps -ef|grep oracle
2. 找到 smon 并为它杀死 pid
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.

固定您的 large_pool，使其不能低于某个点或添加内存并设置更高的最大内存。

回答by PAWAN RAJ Shakya

Error

错误

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select increment$,minvalue,m...","sga heap(3,0)","kglsim heap")

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

解决方案：由 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.
  

• 1.-

  ps -ef|grep oracle
  

• 2.- 找到 smon 并为它杀死 pid

• 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

解决了

回答by andjelko miovcic

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

这是 Oracle 错误，shared_pool 中的内存泄漏，很可能是管理大量分区的数据库。解决方案：我认为补丁不存在，请与 oracle 支持联系。您可以尝试使用 subpools 或 en(de)able AMM ...