Olá a todos. É 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, nesse artigo 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 from é 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.

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
       1 - (PHYSICAL_READS / (DB_BLOCK_GETS  CONSISTENT_GETS)) "Hit Ratio"
FROM VBUFFER_POOL_STATISTICS;

NAME            PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS  Hit Ratio
--------------- -------------- ------------- --------------- ----------
DEFAULT                2382927      85639921        46004325 .981898738

1 row selected.

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

Uma consulta semelhante pode ser feita na VSYSSTAT:

SELECT NAME, VALUE
FROM VSYSSTAT
WHERE NAME IN ('db block gets from cache', 
               'consistent gets from cache', 
               'physical reads cache');

NAME                                                VALUE
---------------------------------------------- ----------
db block gets from cache                         88204118
consistent gets from cache                     9726193722
physical reads cache                              2560965

3 rows selected.

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

O buffer cache também pode ser ajustado com base na view VDB_CACHE_ADVICE.

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

show parameter db_cache_advice
db_cache_advice                      string   ON

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

COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd PhysRead Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys Reads'

SELECT size_for_estimate, buffers_for_estimate, 
       estd_physical_read_factor, estd_physical_reads
  FROM VDB_CACHE_ADVICE
 WHERE name = 'DEFAULT'
   AND block_size = (SELECT value 
                       FROM VPARAMETER 
    			      WHERE name = 'db_block_size')
   AND advice_status = 'ON';

                                Estd Phys    Estd Phys
 Cache Size (MB)      Buffers Read Factor        Reads
---------------- ------------ ----------- ------------
              80        9,915       61.70  150,251,109
             160       19,830       40.30   98,140,104
             240       29,745       21.08   51,329,557
             320       39,660        7.23   17,594,710
             400       49,575        1.36    3,309,722
             480       59,490        1.21    2,948,364
             560       69,405        1.13    2,763,717
             640       79,320        1.09    2,655,866
             720       89,235        1.06    2,581,271
             800       99,150        1.03    2,510,199
             880      109,065        1.00    2,435,219
             960      118,980         .98    2,380,060
           1,040      128,895         .97    2,350,269
           1,120      138,810         .95    2,309,334
           1,200      148,725         .93    2,275,624
           1,280      158,640         .92    2,249,930
           1,360      168,555         .92    2,231,580
           1,440      178,470         .91    2,211,413
           1,520      188,385         .90    2,193,922
           1,600      198,300         .89    2,167,724

20 rows selected.

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.

SELECT *
FROM (SELECT SQL_FULLTEXT, BUFFER_GETS
        FROM VSQL
       ORDER BY BUFFER_GETS DESC)
WHERE ROWNUM <= 50

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:

SELECT employee_id FROM employees WHERE department_id = 10;
SELECT employee_id FROM employees WHERE department_id = 20;

Por:

SELECT employee_id FROM employees WHERE department_id = :dept_id;

• 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:

SELECT employee_id FROM employees WHERE department_id = :dept_id

É diferente de:

SELECT employee_id FROM employees where department_id = :dept_id

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:

SELECT sum(pinhits) / sum(pins)
FROM VLIBRARYCACHE;

SUM(PINHITS)/SUM(PINS)
---------------------- 
            .996986356

1 row selected.

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.

SELECT * FROM VSGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';

POOL         NAME                          BYTES
-----------  ------------------------- ---------
shared pool  free memory               385236520

1 row selected.

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

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" 
FROM VROWCACHE;

 ROW CACHE
----------
.994562437

1 row selected.

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.

show parameter statistics_level;

statistics_level                     string   TYPICAL

SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
       shared_pool_size_factor "Size Factor",
       estd_lc_time_saved "Time Saved in sec"
  FROM vshared_pool_advice;

Size of Shared Pool in MB Size Factor Time Saved in sec
------------------------- ----------- -----------------
                      352       .2391           1461671
                      512       .3478           1465054
                      672       .4565           1470451
                      832       .5652           1472452
                      992       .6739           1472513
                     1152       .7826           1472536
                     1312       .8913           1472542
                     1472           1           1472542
                     1632      1.1087           1472542
                     1792      1.2174           1472542
                     1952      1.3261           1472542
                     2112      1.4348           1472542
                     2272      1.5435           1472542
                     2432      1.6522           1472542
                     2592      1.7609           1472542
                     2752      1.8696           1472542
                     2912      1.9783           1472542
                     3072       2.087           1472542

18 rows selected.

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.

SELECT NAME, VALUE
FROM VSYSSTAT
WHERE NAME = 'redo buffer allocation retries';

NAME                                                             VALUE
----------------------------------------------------------- ----------
redo buffer allocation retries                                      11

1 row selected.

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

Esse artigo forneceu 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!