ORA-04031: n?o e possivel alocar 4032 bytes de memoria compa

DBA Geral (instalação, configuração do banco, scripts do Sql*Plus, shell scripts, batch scripts, etc...)
Responder
cream3
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 17
Registrado em: Seg, 17 Mai 2010 10:49 pm
Localização: Belo Horizonte

Pessoal, a duas semanas o banco de dados Oracle 8i em servidor windows 2000 com 2Gb de memória começou a derrubar todo mundo e não subir mais, tenho que reniciar o servidor para que funcione tudo dinovo.

Erro no tracert:

Selecionar tudo

*** SESSION ID:(7.1) 2012-10-17 10:30:38.458
Error in executing triggers on database startup
*** 2012-10-17 10:30:38.817
ksedmp: internal or fatal error
ORA-00604: ocorreu um erro no nivel 1 SQL recursivo
ORA-04031: n?o e possivel alocar 4032 bytes de memoria compartilhada ("shared pool","java/lang/StringSYS","joxlod: in ehe","ioc_allocate_pal")
ORA-06512: em "SYS.DBMS_JAVA", line 0
ORA-06512: em line 2

meu init.ora

Selecionar tudo

b_files = 1024

control_files = ("D:\Oracle\oradata\TESTE\control01.ctl", "D:\Oracle\oradata\TESTE\control02.ctl", "D:\Oracle\oradata\TESTE\control03.ctl")

open_cursors = 100
max_enabled_roles = 100
#db_file_multiblock_read_count = 8
db_file_multiblock_read_count = 16

db_block_buffers = 51658
#db_block_buffers = 25829

#shared_pool_size = 35265536
#shared_pool_size = 82067456
shared_pool_size = 117332992

large_pool_size = 614400
#java_pool_size = 20971520
java_pool_size = 0

#log_checkpoint_interval = 10000
#log_checkpoint_timeout = 1800
FAST_START_IO_TARGET  = 90
em muitos topicos na internet informa para dinimuir ou almentar a shared_pool_size que atualmente está em 177Mb.
Reparei que o java_pool_size está setado a 0.

Alguma sugestão??
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Olá Cream3,

Primeiro, uma descrição do erro e ação corretiva, segundo a ORACLE:

Selecionar tudo

Problem: ORA-04031 - unable to allocate string bytes of shared memory ("string","string","string","string") 

Cause: More shared memory is needed than was allocated in the shared pool.
 
Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size". 
Você então deveria focar nos parâmetros SHARED_POOL_RESERVED_SIZE, SHARED_POOL_SIZE e LARGE_POOL_SIZE.

O especialista Burleson menciona em seu artigo (http://www.dba-oracle.com/sf_ora_04031_ ... memory.htm) que se pode usar a querie abaixo para identificar o consumo de pool corrente (currente pool size) e comparar o mesmo com o parâmetro LARGE_POOL_SIZE:

Selecionar tudo

SELECT 
   name, 
   SUM(bytes) 
FROM 
   V$SGASTAT 
WHERE 
   pool='LARGE POOL' 
GROUP BY 
   ROLLUP(name);
Se por acaso este valor da querie estiver bem próximo ao parâmetro, você poderia experimentar dobrar (ou aumentar em 1.5) o parâmetro de inicialização LARGE_POOL_SIZE. No artigo, Burleson menciona que a ocorrência de ORA-04031 pode ser reduzida significativamente.

ENTRETANTO, pelo que pude entender, seu banco de dados funcionava normalmente até um tempo atrás.

De lá para cá, você saberia dizer ou que mudou? Algumas sugestões/exemplos:

- Foi implantado um novo sistema;
- Uma manutenção foi implementada;
- Aumentou a quantidade de acessos dos usuários;
- Foram adicionados novos recursos à aplicação, como uma rotina batch de processamento.

Pelo que pude entender desta mensagem de erro, sua SGA parece estar sofrendo fragmentação. Isso pode ser causado por falta de variáveis BIND em seu código PL/SQL java.

Em um artigo de seu forum ASKTOM (http://asktom.oracle.com/pls/asktom/f?p ... 8893984337), Tom kyte alerta que às vezes este erro pode ser causado por má codificação dos comandos SQL, especificamente a falta de variáveis BIND.

Por exemplo, você tem um sistema que executa este tipo de comando (exemplos retirados do link anterior):

Selecionar tudo

INSERT INTO gfx_suggestion (suggestion, suggestion_id, timestamp, 
suggestion_type_fl,
name, email, business_unit_key) select 'test suggestion. RJ 04/19/01', 
max(suggestion_id)+1, sysdate, 'T', 'Ron Jennings' , 'rj@rwd.com', '5' from 
gf_suggestion 
Quando na verdade, deveria executar desta forma (com variáveis bind):

Selecionar tudo

INSERT INTO gfx_suggestion (suggestion, suggestion_id, timestamp, 
suggestion_type_fl,
name, email, business_unit_key) select :1, max(suggestion_id)+1, sysdate, :2, :3, :4, :5 
from gf_suggestion
 
Um excesso de comandos "hard-code" como o primeiro insert pode gerar uma fragmentação da SGA com estes comandos SQL escritos de forma "pobre". Note que o segundo comando eventualmente poderia ser usado mais de uma vez pelo ORACLE, pois os valores não estão em "hard-code", mas sob a forma de variáveis.

Mesmo que você resolva o seu problema aumentando a LARGE_POOL_SIZE, eu recomendaria você ter uma conversar com seus desenvolvedores, para verificar a qualidade do código PL/SQL gerado.

Este erro pode estar até relacionado com BUGS do produto.

O link abaixo oferece uma visão abrangente sobre este problema:

http://jametong.itpub.net/post/5042/27713

Espero que consiga resolver seus problemas com estas informações.

Abraços,

Sergio Coutinho
cream3
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 17
Registrado em: Seg, 17 Mai 2010 10:49 pm
Localização: Belo Horizonte

Sergio, bom dia!

Primeiramente gostaria de parabenilaza-lo pela resposta. Foi praticamente um artigo inteiro. Conteúdo de altissima qualidade.

Bom,

respondendo as perguntas:

De lá para cá, você saberia dizer ou que mudou? Algumas sugestões/exemplos: (Sou recente na empresa, mas, pelo que percebi não mudou praticamente nada a um bom tempo. Percebi também que a instalação do Oracle neste "Servidor/Desktop" foi realizado com next, next finish em torno de um ano e meio atras. Toda configuração está padrão.
Os acessos são internos em ERP escrito em Delph 3.

- Foi implantado um novo sistema; >> Não foi implementado um novo sistema.
- Uma manutenção foi implementada; >> Não foi implementada manutenção.
- Aumentou a quantidade de acessos dos usuários; >> Quantidade de acesso de usuários aumentou, mas, creio que não seria uma preocupação tão grande devido a não chegar nem ao menos 30 sessões no servidor.
- Foram adicionados novos recursos à aplicação, como uma rotina batch de processamento. >> Não foram implementadas novas rotinas.

A consulta sugerida no artigo de Burleson nos trouxe estes resultados:

free memory: 614400
<null>/alias : 614400

Outra analise foi os codigos escritos no Delph e uma conversa com os programadores responsáveis pelas manutenções do sistema. A aplicação está toda escrita com variavéis BIDS.

Algumas outras verificações segue abaixo:

-- Indicado que deve ser maior que 90%, caso contrário aumentar o parametro de inicialização DB_CACHE_SIZE.

Selecionar tudo

select   100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from  v$sysstat v1, v$sysstat v2, v$sysstat v3
where
  v1.name = 'db block gets' and
  v2.name = 'consistent gets' and
  v3.name = 'physical reads'

Selecionar tudo

DB Cache	Data	                10:00	15:00
DB Cache	15/10/2012	97,8	        98,8
DB Cache	16/10/2012	98,6   	98
DB Cache	18/10/2012	98,7	         98,4
DB Cache	19/10/2012	98,51	97,8
--O número de espera deve ser sempre zero. Caso contrário, aumentar o tamanho do LOG_REDO, até o retorno do número a zero. (redo log space requests)

Selecionar tudo

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');

Selecionar tudo

Tempo Espera Redo Log	Data	             10:00	15:00
Tempo Espera Redo Log	15/10/2012	7	21
Tempo Espera Redo Log	16/10/2012	27	41
Tempo Espera Redo Log	18/10/2012	8	80
Tempo Espera Redo Log	19/10/2012	7	36
--Aumentar tamanho do buffer se for menor que 5.000.

Selecionar tudo

Select Round(e.value/s.value,5) "Redo Log Ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries';

Selecionar tudo

Buffer de Redo	Data	              10:00	15:00
Buffer de Redo	15/10/2012	2104	   1910
Buffer de Redo	16/10/2012	1907	  1948
Buffer de Redo	18/10/2012	2009	   691
Buffer de Redo	19/10/2012	2048	   1325
--when "Hit Ratio" < 99%, increase shared_pool_size

Selecionar tudo

select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round((sum(pins)/(sum(reloads)+sum(pins)))*100,2) 
"Hit Ratio, %" from V$LIBRARYCACHE;

Selecionar tudo

Acesso Cache Banco "Hit Ratio"	Data	                10:00	15:00
Acesso Cache Banco "Hit Ratio"	15/10/2012	99,99%	100%
Acesso Cache Banco "Hit Ratio"	16/10/2012	99,99	100
Acesso Cache Banco "Hit Ratio"	18/10/2012	99,99	100
Acesso Cache Banco "Hit Ratio"	19/10/2012	99,99	100

--when "Misses Ratio" > 1% increase shared_pool_size

Selecionar tudo

select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round(sum(reloads)/sum(pins)*100,2) 
AS "Misses Ratio, %" from V$LIBRARYCACHE;
ou

Selecionar tudo

SELECT 
   (SUM(getmisses)/SUM(gets))*100 RATIO
FROM
   v$rowcache;

Selecionar tudo

Acesso disco "Misses Ratio"	Data	                10:00	15:00
Acesso disco "Misses Ratio"	15/10/2012	0,734	0,342
Acesso disco "Misses Ratio"	16/10/2012	0,645	0,41
Acesso disco "Misses Ratio"	18/10/2012	0,621	0,37
Acesso disco "Misses Ratio"	19/10/2012	0,606	0,32
Ainda não consegui identificar o gargalo, mas, uma das verificações que me chama muita a atenção é sobre acesso a disco apesar do hit ratio me demonstrar 99,99%

Parece que o recomendado é: se numero de acessos a disco for muito maior que zero aumentar a SORT_AREA_SIZE

Selecionar tudo

Select name, value from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');

Selecionar tudo

Acesso disco	Data	              10:00	15:00
Acesso disco	15/10/2012	133	887
Acesso disco	16/10/2012	178	745
Acesso disco	18/10/2012	158	508
Acesso disco	19/10/2012	252	1042
Ainda não estou muito seguro para efetivar tais modificações.
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Cream3,

Eu acabei pesquisando um pouco mais sobre o assunto dentro do próprio METALINK da ORACLE.

Segundo a nota 115256.1, este erro pode acontecer quando se executam operações de LOADJAVA (carga de classes java ou javabeans) no Oracle 8i. Será que os sistemas que acessam seu banco se utilizam de recursos de java?

A nota pede para baixar o banco, reconfigirar os valores de JAVA_POOL_SIZE e SAHRED_POOL size para valores maiores. Ela menciona que uma instalação típica de Oracle 8i (8.1.6/8.1.5) geralmente apresenta a SHARED_POOL_SIZE com 52428800 (50MB) e a JAVA_POOL_SIZE com 20971520 (20MB).

Valores menores que estes descritos podem ocasionar a mensagem de erro ORA-04031.

Uma explicação adicional na nota é que esta mensagem de erro pode ser confundida com falta de memória na SHARED GLOBAL AREA (SGA), mas - no final das contas - seria sintoma de um JAVA_POOL_SIZE configurado com um valor baixo.

Um outra nota - 105472.1 - apresenta maiores informações sobre como configurar a JVM usada pelo Oracle 8i.

Então, talvez seja interessante fazer uma experiência e tentar reconfigurar/redimensionar o JAVA_POOL_SIZE de ZERO para o valor mencionado na nota (20MB). Faça um acompanhamento depois no alert log para ver se os erros diminuem e nada mais errado esteja ocorrendo.

Espero que isso resolva o seu problema.

Abraços e boa sorte !

Sergio Coutinho
cream3
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 17
Registrado em: Seg, 17 Mai 2010 10:49 pm
Localização: Belo Horizonte

Bom, até o momento parou.
Realizei o aumento da memória JAVA_POOL_SIZE de 0 para 20M.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 7 visitantes