Olá pessoal, no artigo de hoje pretendo falar um pouco sobre o erro “ORA-04031 unable to allocate X bytes of shared memory”.
Esse erro ele costuma aparecer no alert.log do banco de dados. Esse erro ele não necessariamente vai evitar que você acesse a instância do banco de dados, em alguns casos ele pode somente te impossibilitar de habilitar o FLASHBACK por exemplo.
Mas para os casos desse erro impossibilitar o login de alguma aplicação ou do próprio sqlplus ao banco de dados, será necessário o restart da instância para a normalização do ambiente. Nesse artigo vou explicar um pouco das causas desse erro e como podemos redimensionar a SGA de uma forma que evite o máximo possível essa situação e que ao mesmo tempo evite qualquer alocação de memória extra desnecessária.
Ao longo da vida útil do banco de dados, é comum que mais memória precise ser alocada ao sistema conforme o seu crescimento, um dos primeiros motivos desse erro acontecer é simplesmente a necessidade de aumento da sua Shared Pool. Em ambientes ASMM esse problema normalmente já vai ser resolvido apenas redimensionando a SGA, sem a necessidade de executarmos um alter system no shared_pool_size por exemplo.
A fragmentação da shared pool também pode ser uma causa desse problema, o que seria resolvido com um.
alter system flush shared_pool;
O flush na shared_pool poderia melhorar a situação, mas do meu ponto de vista um aumento nos parâmetros da SGA tende a ser uma medida muito mais resolutiva. Em ambientes ASMM, nós podemos realizar o resize da SGA apenas configurando os parâmetros abaixo:
SGA_TARGETSGA_MAX_SIZE
o SGA_TARGET é um parâmetro dinâmico, o que significa que não seria necessário um restart da instancia para ele funcionar. Já o SGA_MAX_SIZE ele necessita de um restart da instância, o que já precisaria ser algo um pouco mais planejado, porém em ambientes RAC tende a ser muito mais simples.
Lembrando que o ideal seria deixarmos o SGA_MAX_SIZE com um valor um pouco maior que o SGA_TARGET pois este seria apenas uma meta definida para a alocação de memória na SGA durante a administração do Oracle sobre a instancia.
Mas quais seriam as formas de descobrir um valor recomendável para a SGA do banco?
O próprio Oracle já consegue nos indicar alguns valores possíveis, podemos consultar isso através da V$SGA_TARGET_ADVICE
SELECT sga_size, sga_size_factor, estd_db_time_factor FROM v$sga_target_advice ORDER BY sga_size ASC;
ESTD_DB_TIME_FACTOR –> mostra o fator relativo de DB Time estimado para cada tamanho de SGA simulado pelo advisor.
SGA_SIZE –> indica o tamanho da SGA.
SGA_SIZE_FACTOR –> representa o multiplicador do tamanho atual da SGA usado pelo advisor para gerar as estimativas.
A configuração atual sempre terá o ESTD_DB_TIME_FACTOR = 1
O resultado da consulta seria algo como o exemplo abaixo:
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME_FACTOR-------- --------------- -------------------17280 .375 3.791423040 .5 1.081528800 .625 1.027134560 .75 1.010640320 .875 1.003846080 1 1.000051840 1.125 .997657600 1.25 .996663360 1.375 .995869120 1.5 .995274880 1.625 .994780640 1.75 .994586400 1.875 .994392160 2 .994214 rows selected.
O ideal seria encontrarmos um valor onde o SGA_SIZE diminua o máximo possível do ESTD_DB_TIME_FACTOR sem precisarmos alocar mais memória que o necessário, no exemplo acima o valor atual (ESTD_DB_TIME_FACTOR=1) já funciona muito bem, mas em alguns casos teremos uma discrepância muito grande nessa relação.
A V$SGA_TARGET_ADVICE também possui os campos ESTD_SHARED_POOL_SIZE, ESTD_BUFFER_CACHE_SIZE, ESTD_PHYSICAL_READS e ESTD_DB_TIME. Que por exemplo já iria nos ajudar a ter uma ideia de qual o tamanho esperado para a Shared_pool após a mudança dos parâmetros da SGA.
Conclusão
Apesar do erro ORA-04031 em muitos casos já ser mediado através do restart da instância, aumentar a alocação de memória na SGA deve resolver o problema em definitivo por um bom tempo. Para isso a utilização do SGA Advisory facilita muito na escolha do futuro SGA_TARGET.
Lembrando também que esse erro pode ser causado por uso excessivo de literais (hard parse), memory leaks, bugs Oracle, problemas de autotuning da SGA, Large Pool insuficiente, Streams Pool insuficiente ou Java Pool insuficiente. Apesar de eu ter mencionado bastante a Shared Pool nos exemplos acima, qualquer um desses componentes também pode ser o responsável pelo ORA-04031. Nos casos em que a causa esteja relacionada à insuficiência de memória em alguma estrutura da SGA, a view V$SGA_TARGET_ADVICE pode auxiliar na análise e no dimensionamento adequado da memória.
Iago DBA 