oracle

Olá a todos. Nesta segunda parte, falarei um pouco sobre como otimizar o uso das estruturas de memória do Oracle, afim de obter o máximo destas. Lembrando que essas informações serão voltadas exclusivamente para o Oracle 11gR2.

 

1 – Shared Pool

Existem algumas queries úteis para ajudar a determinar se a Shared Pool está subdimensionado. Vale lembrar que uma shared Pool subdimensionada pode causar um elevado número de hard parses (Library Cache Miss) o que é ruim para a performance geral do banco de dados.

Abaixo é possível ver uma query simples que pode ajudar a determinar se a Shared Pool está subdimensionada. Caso a coluna “request_failures” esteja maior que 0, e estiver crescendo continuamente, isso pode indica que a Shared Pool esta subdimensionada:
SELECT REQUEST_MISSES,
REQUEST_FAILURES
FROM V$SHARED_POOL_RESERVED;

A Shared Pool possui uma estrutura interna, chamada de Shared Pool Reserved Size, que é reservado para grandes requisições contínuas. É usada para evitar degradação de performance no caso de a fragmentação na Shared Pool forçar o Oracle a buscar pedaços não usados da Shared pool para satisfazer a requisição corrente. Seu default é 5% da SHARED_POOL_SIZE. A view V$SHARED_POOL_RESERVED contém informações úteis sobre ela. Para interpretar se a Shared Pool Reserved Size precisa ser redimensionada, basta executar a query acima, a interpretando da seguinte forma:

– SHARED_POOL_RESERVED_SIZE pequena demais: Quando a coluna REQUEST_FAILURES for maior que zero e estiver crescendo constante,
aumentar a SHARED_POOL_RESERVED_SIZE e a SHARED_POOL_SIZE proporcionalmente. Isso indica que a SHARED_POOL_SIZE também está subdimensionada.

– SHARED_POOL_RESERVED_SIZE grande demais: Quando a coluna REQUEST_MISSES for 0 ou não estiver aumentando ou quando a coluna
FREE_SPACE for maior ou igual a 50% do tamanho mínimo da SHARED_POOL_RESERVED_SIZE.

A Library Cache, que faz parte da Shared Pool, também é passiva de otimizações. Essa estrutura interna armazena os SQLs que já sofreram o parse, planos de execução, blocos PL/SQL e classes Java. A query abaixo pode ser usada para monitorar a Library Cache (atentar para as colunas RATIO):
SELECT NAMESPACE,
GETS,
RELOADS,
PINS,
INVALIDATIONS,
ROUND(GETHITRATIO * 100, 2) GETHITRATIO,
ROUND(PINHITRATIO * 100, 2) PINHITRATIO
FROM V$LIBRARYCACHE;

Invalidações ocorrem quando o objeto referenciado pela query dentro da Library Cache sofre alguma alteração (adição de colunas, grants, criação de indices e etc). Portanto, recomenda-se evitar, o quanto possível, alterações estruturais em objetos frequentemente acessados, pois isso invalida a query que já sofreu o parse, sendo que na próxima execução desta query, um hard parse será necessário.

Outra métrica relativamente útil, relacionada a Library Cache, é seu hit ratio. Eu mencionei “relativamente” porque o hit ratio não é uma indicação fiel de que algo está errado ou 100% otimizado. Já vi casos de bases com um hit ratio baixo, mas sem problemas aparentes, portanto, usem essa métrica juntamente com outras análises. Para mensurar o hit ratio do Library Cache:
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",
SUM(PINS - RELOADS)*100/SUM(PINS) as hit_ratio
FROM V$LIBRARYCACHE;
Por fim, a Shared Pool possui um Advisor, como qualquer outra estrutura.

SELECT SHARED_POOL_SIZE_FOR_ESTIMATE AS ESTIMATE_SIZE,
SHARED_POOL_SIZE_FACTOR AS FACTOR,
ESTD_LC_TIME_SAVED AS TIME_SAVED,
ESTD_LC_LOAD_TIME AS LOAD_TIME
FROM V$SHARED_POOL_ADVICE;

2 – Log Buffer

O Log Buffer geralmente possui um tamanho bem menor em relação as outras estruturas de memória do banco, mas também possui um papel importante no assunto performance. Algumas recomendações básicas para otimizar o uso dessa estrutura de meória são:

– Realizar COMMITs em lote

– Usar o NOLOGGING para operações regulares, evitando a geração de REDO. Porém isso torna as transações irrecuperáveis na eventualidade de um crash da instância. Usar essa opção com cautela.

Para monitorar o Log Buffer, afim de identificar se é necessário o seu aumento, executar a query abaixo. Caso o valor do “redo buffer allocation retries” esteja aumentando gradativamente, isso pode indica que o Log Buffer está subdimensionado, e como consequência disso, processos estão esperando para escrever os
redo log entries no Log Buffer.
SELECT NAME,
VALUE
FROM V$SYSSTAT
WHERE NAME = 'redo buffer allocation retries';

3 – Buffer Cache

Para melhorar o uso do Buffer Cache, não basta apenas aumentar seu tamanho. A principal atitude a ser tomada é a otimização das queries do banco de dados, principalmente aquelas que são executadas com muita frequência.

É possível mensurar o ratio do Buffer cache. Esse ratio mede a taxa em que o Oracle encontra os datablocks no buffer cache. Caso esteja muito baixo, isso pode indicar um subdimensionamento do Buffer Cache. Mara obter esse ratio, além de outras informações:
SELECT PR.VALUE AS "phy. reads",
PRD.VALUE AS "phy. reads direct",
PRDL.VALUE AS "phy. reads direct (lob)",
SLR.VALUE AS "session logical reads",
(1 - (PR.VALUE - PRD.VALUE - PRDL.VALUE) / SLR.VALUE)*100 AS "hit ratio"
FROM V$SYSSTAT PR, V$SYSSTAT PRD, V$SYSSTAT PRDL, V$SYSSTAT SLR
WHERE PR.NAME = 'physical reads'
AND PRD.NAME = 'physical reads direct'
AND PRDL.NAME = 'physical reads direct (lob)'
AND SLR.NAME = 'session logical reads';

Essa estrutura de memória também possui um advisor, com possíveis recomendações de redimensionamento:

SELECT size_for_estimate,
size_factor,
buffers_for_estimate,
abs(((estd_physical_read_factor)*100)-100) as pct_ganho_phys_reads,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name          = 'DEFAULT'
AND block_size    = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

4 – PGA

A PGA tem papel importante na performance de queries do banco de dados, principalmente aquelas queries que realizam muitos sorts e hashing. Uma query que pode fornecer uma visão geral de como a PGA está, segue a seguir:

SELECT NAME,
ROUND(VALUE/1024/1024, 2) ||' MBs' AS VALOR
FROM V$PGASTAT
WHERE NAME IN ('aggregate PGA target parameter', 'total PGA allocated', 'total PGA inuse')
UNION
SELECt 'PGA cache hit percentage (Obj: 100%)', VALUE || '%'
FROM V$PGASTAT WHERE NAME = 'cache hit percentage'
UNION
SELECT 'Overallocations', VALUE || ''
FROM V$PGASTAT WHERE NAME = 'over allocation count'

OBS1: Atentar para a coluna “overallocations”, que indica se estão ocorrendo escritas na tablespace temporária, gerando disk I/O o que não é desejável. Recomenda-se que esse valor seja 0. Caso esteja muito alto, verificar a possibilidade de redimensionar a PGA.

O advisor da PGA pode ser obtido a partir da seguinte query:

SELECT PGA_TARGET_FACTOR,
round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;

5 – AMM – Automatic Memory Management

Caso se esteja usando a feature de AMM (MEMORY_TARGET <> 0), é possível otimiza-la como um todo. Ao usar essa feature, todas as estruturas de memória acima mencionadas, entre outras, serão automaticamente ajustadas pelo Oracle, conforme o ambiente demandar. Essa estrutura também possui um advisor, cujas informações podem ser obtidas a partir da seguinte view:

V$MEMORY_TARGET_ADVICE

 

6 – Considerações finais

Na minha humilde opinião, as estruturas de memória que mais impactam o desempenho do banco de dados são o Buffer Cache e a Shared Pool e ambas são diretamente influenciadas em como os sqls são escritos e como a aplicação é escrita. SQLs mal-escritos irão realizar muitos gets desnecessários, usando assim o Buffer Cache de forma inadequada, e como consequência, gerando disk I/O, o que para ambientes críticos, deve ser evitado ao máximo. Aplicações que não usam bind variables irão influenciar negativamente a Shared Pool, realizando hard parses desnecessários e como consequência onerando o CPU do ambiente. Portanto, foquem em otimizações dos SQLs mais usados no ambiente e na otimização do código da aplicação que acessa o banco de dados.

Juntamente com as queries acima apresentadas, eu recomendo que, ao analisar as estruturas de memória da instância, você também analisa um relatório AWR/Statspack em conjunto. Ele possui infirmações valiosas que podem auxiliar a determinar se uma estrutura de memória precisa ser alterada.

Por enquanto é só pessoal. Keep tuning.