oracle - sga_target - sga_max_size

Resolución de ORA-4031 "no se pueden asignar x bytes de memoria compartida"

Question

Necesito algunos consejos sobre cómo diagnosticar y solucionar este problema. No sé si esto es un simple problema de configuración del servidor o un problema de diseño de la aplicación (o ambos).

Una o dos veces cada pocos meses, esta base de datos Oracle XE informa errores ORA-4031. No apunta a ninguna parte particular del sga de manera consistente. Un ejemplo reciente es:

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

Cuando aparece este error, si el usuario continúa actualizando, haciendo clic en diferentes enlaces, generalmente obtendrá más de este tipo de errores en diferentes momentos, y luego pronto obtendrán errores "404 no encontrados" en la página.

El reinicio de la base de datos generalmente resuelve el problema por un tiempo, luego un mes más tarde vuelve a aparecer, pero rara vez en la misma ubicación en el programa (es decir, no parece estar vinculado a ninguna parte particular del código) (el ejemplo anterior se generó un error desde una página de Apex que estaba ordenando más de 5000 filas de una tabla).

He intentado aumentar sga_max_size de 140M a 256M y espero que esto ayude a las cosas. Por supuesto, no sabré si esto me ayudó, ya que tuve que reiniciar la base de datos para cambiar la configuración :)

Estoy ejecutando Oracle XE 10.2.0.1.0 en una caja de Oracle Enterprise Linux 5 con 512 MB de RAM. El servidor solo ejecuta la base de datos, Oracle Apex (v3.1.2) y el servidor web Apache. Lo instalé con casi todos los parámetros predeterminados y ha estado funcionando bastante bien durante aproximadamente un año. La mayoría de los problemas que he podido resolver yo mismo ajustando el código de la aplicación; No se usa intensivamente y no es un sistema crítico para el negocio.

Estas son algunas de las configuraciones actuales que creo que pueden ser relevantes:

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

Si hay alguna ayuda, aquí están los tamaños actuales de SGA:

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

Answer 1

Aunque esté utilizando ASMM, puede establecer un tamaño mínimo para el grupo grande (MMAN no lo reducirá por debajo de ese valor). También puedes intentar fijar algunos objetos y aumentar SGA_TARGET.

Answer 2

Error: ORA-04031: no se pueden asignar 4064 bytes de memoria compartida ("pool compartido", "seleccionar incremento $, minvalue, m ...", "sga heap (3,0)", "kglsim heap")

Solution: by nepasoft nepal

1 ps -ef | grep oracle

2 encontrar el smon y matar al pid por ello

3 SQL> inicio de montaje

La instancia de ORACLE comenzó.

Área global total del sistema 4831838208 bytes Tamaño fijo 2027320 bytes Tamaño variable 4764729544 bytes Base de datos Buffers 50331648 bytes Redo Buffers 14749696 bytes Base de datos montada. SQL>

4 SQL> alterar el conjunto del sistema shared_pool_size = 100M scope = spfile;

Sistema alterado.

5 SQL> apagado inmediato

ORA-01109: base de datos no abierta

Base de datos desmontada. La instancia de ORACLE se apaga.

6 SQL> inicio

La instancia de ORACLE comenzó.

Área global total del sistema 4831838208 bytes Tamaño fijo 2027320 bytes Tamaño variable 4764729544 bytes Base de datos Buffers 50331648 bytes Redo Buffers 14749696 bytes Base de datos montada. Base de datos abierta.

7 SQL> crear pfile desde spfile;

Archivo creado.

Resuelto

Answer 3

Esto es error de Oracle, pérdida de memoria en shared_pool, muy probablemente db administrando muchas particiones. Solución: En mi opinión, el parche no existe, verifique con el soporte de Oracle. Puede probar con subagrupaciones o en (des) AMM habilitado ...

Answer 4

Los siguientes no son necesarios ya que no solucionan el error:

1. 1 ps -ef | grep oracle
2. Encuentra el smon y mata al pid por ello.
3. SQL> inicio de montaje SQL>
4. Crea pfile desde spfile;

Al reiniciar la base de datos, se vaciará el grupo y eso soluciona un efecto, no el problema.

Arregle su gran_pool para que no pueda bajar más de un punto o agregue memoria y establezca una memoria máxima superior.

Answer 5

No te olvides de la fragmentación. Si tiene mucho tráfico, sus grupos pueden estar fragmentados e incluso si tiene varios MB libres, no podría haber un bloque mayor que 4KB. Compruebe el tamaño del bloque libre más grande con una consulta como:

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

Código de

Answer 6

Todas las respuestas actuales abordan el síntoma (agotamiento de la agrupación de memoria compartida) y no el problema, que probablemente no esté utilizando las variables de enlace en sus consultas sql / JDBC, incluso cuando no parece necesario hacerlo. Pasar consultas sin variables de enlace hace que Oracle "analice" la consulta cada vez, determinando su plan de ejecución, etc.

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

Algunos fragmentos del enlace de arriba:

"Java admite variables de enlace, sus desarrolladores deben comenzar a usar declaraciones preparadas y entradas de enlace en él. Si desea que su sistema aumente en última instancia más allá de aproximadamente 3 o 4 usuarios, lo hará ahora (arregle el código). Es no es algo en lo que pensar, es algo que DEBE hacer. Un efecto secundario de esto: los problemas de su piscina compartida prácticamente desaparecerán. Esa es la causa raíz ".

"La forma en que funciona el pool compartido de Oracle (una estructura de datos de memoria compartida muy importante) se basa en los desarrolladores que utilizan variables de enlace".

"Las variables vinculantes son TAN MÁSIVAMENTE importantes. No puedo, de ninguna manera, dar forma o SUPERVISAR su importancia".