4Dados Consultoria & Suporte

Oracle Partner Network

Ajustando o BUFFER CACHE, SHARED POOL e o LOG BUFFER

É bem verdade que as interfaces GUI como o Spotlight, o Database/Grid Control(10g), o OEM(9x) entre outras, são muito úteis, e facilitam em muito a identificação de problemas de performance. Mas, como nem sempre teremos essas ferramentas à mão, é interessante saber utilizar as visões V$ para identificar problemas de performance.

Por isso, iremos falar um pouco da utilização de views para realizar o ajuste do BUFFER CACHE, SHARED POOL e LOG BUFFER.

 

BUFFER CACHE

O buffer cache é utilizado para armazenar os blocos lidos a partir dos discos. Significa que um buffer cache pequeno irá fazer com que o Oracle precise remover do cache os blocos de dados seguindo a lista LRU (LAST RECENTLY USED), e dependendo da frequência com que isso acontece, poderá gerar uma queda na performance.

Não existe uma mágica para dimensionar o buffer cache, o que normalmente se faz é estimar um tamanho inicial e monitorar o acerto, caso não esteja dentro do ideal, você precisará aumentar e repetir o ciclo de monitoramento.

Um detalhe importante é que quando a instância é inicializada, o buffer cache está vazio, portanto, qualquer consulta irá gerar misses no buffer. Significa dizer que validar as taxas de acerto no buffer logo após o startup é errado, você provavelmente terá uma taxa de acerto muito baixa.

O buffer é calculado usando a seguinte fórmula:

1 – (physical_reads/(db_block_gets consistent_gets))

consistent gets é o número de vezes que uma leitura consistente foi requisitada para um bloco do buffer cache.

db block gets é o número de vezes que um bloco foi requisitado para o buffer cache.

physical reads é o número total de blocos de dados lidos do disco para o buffer cache.

No exemplo acima, a taxa de acerto foi de 98 no buffer cache.

Uma consulta semelhante pode ser feita na V$SYSSTAT:

1 - (2560965/(882041189726193722)) = 0,99973906

O buffer cache também pode ser ajustado com base na view V$DB_CACHE_ADVICE.

Para que essa view seja populada é necessário que o parâmetro DB_CACHE_ADVICE esteja ON.

A declaração abaixo consulta a respectiva view, retornando estimativas de buffer e de acerto.

A análise dela é feita de forma diferente a de taxas. A consulta mostra uma redução de 2(0.98) no número de leitura físicas caso o buffer cache seja configurado para 960MB.

Lembrando que, quando falamos de memória, estamos falando de memória física, um servidor Oracle, não deve fazer swap.

Para utilizar o buffer cache de forma eficiente, as declarações SQL da aplicação devem estar ajustadas para evitar consumo desnecessário de recursos. Isso é feito verificando as declarações SQL executadas com mais freqência e as que fazem uso de uma maior quantidade de buffers.

A consulta abaixo retorna as 50 maiores consultas consumidoras de BUFFERS.

Existem duas formas de melhorar o acerto no buffer:

  • Otimizando as consultas de forma a retornarem menos blocos, e dessa forma utilizar menos buffer.
  • Aumentando o buffer cache.

SHARED POOL

O Oracle utilize a SHARED POOL para armazenar declarações PL/SQL e SQL, dados do dicionário entre outros.

Da mesma forma que o BUFFER CACHE, é impossível determinar um tamanho inicial para uma base nova. Você deve seguir o mesmo principio do BUFFER CACHE, colocar um valor e avaliar o ambiente. Lembrando que a SHARED POOL inicia vazia, e à medida que os usuários vão submetendo as declarações SQL ela vai sendo preenchida.

Para isso, observe o seguinte:

  • Utilize sempre que possível bind variables ao invés de caracteres literais nas declarações. Isso faz com que o Oracle armazene apenas uma declaração SQL. As declarações, apesar de semelhantes, ocupam duas áreas distintas na SHARED_POOL:

Substitua:

Por:

  • As aplicações devem evitar os usuários possam criar suas próprias instruções.
  • Crie padrões para as bind variables e para os espaços nas declarações SQL blocos de PL/SQL.

Por exemplo:

É diferente de:

O objetivo do tuning na SHARED_POOL é fazer com que uma declaração SQL que está no cache possa ser reutilizada o maior número de vezes possível.

Utilize a declaração abaixo para identificar a taxa de hit ratio da shared pool:

A consulta mostrou que 99,69 dos códigos de SQL e PLSQL estão sendo reaproveitados.

A declaração abaixo mostra a quantidade de bytes livres na SHARED_POOL.

A consulta abaixo também auxilia na descoberta da taxa de acerto da SHARED POOL.

Também é possível utilizar a view VSHARED_POOL_ADVICE. Para isso é preciso que o parâmetro STATISTICS_LEVEL esteja configurado como ALL ou TYPICAL.

A saída acima mostra que o tamanho da shared pool é de 1472M. Mostra também que, se o tamanho da shared pool fosse ajustado para 3072M, teria a mesma eficiência.

LOG BUFFER

Aplicações que inserem, modificam ou excluem um grande volume de registros normalmente não utilizam o tamanho default de log buffer. Apesar do tamanho do log buffer ser bem menor frente ao tamanho total da SGA, ele tem grande impacto na performace de sistemas que realizam atualização no volume dos dados.

Um tamanho inicial para o log buffer é:

MAX(0.5M, (128K * número de CPUs))

A maioria dos sistemas que possuem log buffer maior que 1M não possuem ganhos de performance.

A análise da performance do log buffer é feita por intervalo. Deve ser coletado em intervalos, e verificar se existe um aumento do valor. O ideal é que não existam alterações.

Se o valor aumentar de forma consistente, é necessário ajustar o tamanho do log buffer.

Fornecemos uma forma básica de verificação da performance do database. É preciso levar em consideração que, mesmo com todas as taxas próximas a 100, é possível que outros fatores como I/O ou CPU estejam comprometendo a performance. Abordaremos esses problemas e como identificar através de visões no próximo artigo.

Bons Tunings!