Tuning de Memória | Trilha 4

📖

Definição

Arquitetura de Memória Oracle

SGA - System Global Area (Memória Compartilhada)

Memória compartilhada entre todos os processos. Componentes principais:

Buffer Cache: Cache de blocos de dados das tabelas/índices
Shared Pool: Cache de SQL, PL/SQL, data dictionary
Redo Log Buffer: Buffer circular para logs de transações
Large Pool: Para operações grandes (RMAN, shared servers)
Java Pool: Memória para JVM (se usar Java no banco)

PGA - Program Global Area (Memória Privada)

Memória privada de cada sessão/processo. Usada para:

Sort Area: Operações de ORDER BY, GROUP BY
Hash Area: Hash joins e agregações
Bitmap Merge: Merge de índices bitmap
Session Memory: Variáveis de sessão, cursores

Automatic Memory Management

MEMORY_TARGET: Oracle gerencia automaticamente SGA e PGA dinamicamente. Recomendado para maioria dos casos (menos administração, boa adaptação à carga).

🛠️

Aplicação Prática

Configurando Memória

-- Ver configuração atual
SELECT name, value/1024/1024 AS value_mb, description
FROM v$parameter
WHERE name IN (
    'memory_target', 'memory_max_target',
    'sga_target', 'sga_max_size',
    'pga_aggregate_target', 'pga_aggregate_limit'
);

-- Opção 1: Gerenciamento Automático (Recomendado)
ALTER SYSTEM SET memory_target = 4G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 8G SCOPE=SPFILE;
-- Restart necessário

-- Opção 2: Manual SGA + PGA
ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE;  -- Desabilita AMM
ALTER SYSTEM SET sga_target = 3G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=SPFILE;
-- Restart necessário

-- Opção 3: Manual detalhado (componentes individuais)
ALTER SYSTEM SET sga_target = 0 SCOPE=SPFILE;     -- Desabilita ASMM
ALTER SYSTEM SET db_cache_size = 2G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size = 800M SCOPE=SPFILE;
ALTER SYSTEM SET large_pool_size = 200M SCOPE=SPFILE;
-- Restart necessário

-- Ver uso atual de memória
SELECT
    ROUND(SUM(bytes)/1024/1024/1024, 2) AS sga_gb
FROM v$sgastat;

SELECT
    name,
    ROUND(bytes/1024/1024, 2) AS size_mb
FROM v$sgainfo
ORDER BY bytes DESC;

Monitorando Buffer Cache

-- Buffer Cache Hit Ratio (alvo: > 95%)
SELECT
    name,
    ROUND(((physical_reads - direct_reads) /
           NULLIF(db_block_gets + consistent_gets, 0)) * 100, 2) AS cache_miss_ratio,
    ROUND((1 - ((physical_reads - direct_reads) /
           NULLIF(db_block_gets + consistent_gets, 0))) * 100, 2) AS cache_hit_ratio
FROM v$buffer_pool_statistics
WHERE name = 'DEFAULT';

-- Ver tamanho recomendado pelo advisor
SELECT
    size_for_estimate / 1024 / 1024 AS size_mb,
    size_factor,
    estd_physical_read_factor,
    estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
ORDER BY size_for_estimate;

-- Top objetos em buffer cache
SELECT
    o.owner,
    o.object_name,
    o.object_type,
    COUNT(*) AS blocks,
    ROUND(COUNT(*) * 8192 / 1024 / 1024, 2) AS size_mb
FROM v$bh bh
JOIN dba_objects o ON bh.objd = o.data_object_id
GROUP BY o.owner, o.object_name, o.object_type
ORDER BY blocks DESC
FETCH FIRST 20 ROWS ONLY;

Monitorando Shared Pool

-- Library Cache Hit Ratio (alvo: > 95%)
SELECT
    namespace,
    ROUND(gethitratio * 100, 2) AS hit_ratio,
    ROUND(pinhitratio * 100, 2) AS pin_hit_ratio,
    reloads,
    invalidations
FROM v$librarycache
ORDER BY namespace;

-- Ver componentes do Shared Pool
SELECT
    pool,
    name,
    ROUND(bytes/1024/1024, 2) AS size_mb
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY bytes DESC;

-- Shared Pool Advisory
SELECT
    shared_pool_size_for_estimate / 1024 / 1024 AS size_mb,
    shared_pool_size_factor,
    estd_lc_time_saved,
    estd_lc_time_saved_factor
FROM v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;

-- Top SQL por uso de memória
SELECT
    sql_id,
    ROUND(sharable_mem/1024/1024, 2) AS mem_mb,
    executions,
    SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
ORDER BY sharable_mem DESC
FETCH FIRST 20 ROWS ONLY;

-- Flush shared pool (cuidado em produção!)
ALTER SYSTEM FLUSH SHARED_POOL;

Monitorando PGA

-- PGA atual e uso
SELECT
    ROUND(pga_alloc_mem/1024/1024, 2) AS pga_allocated_mb,
    ROUND(pga_used_mem/1024/1024, 2) AS pga_used_mb,
    ROUND(pga_max_mem/1024/1024, 2) AS pga_max_mb
FROM v$process
WHERE program LIKE '%oracle%'
ORDER BY pga_alloc_mem DESC
FETCH FIRST 20 ROWS ONLY;

-- PGA Advisory (tamanho ideal)
SELECT
    pga_target_for_estimate / 1024 / 1024 AS pga_target_mb,
    pga_target_factor,
    estd_pga_cache_hit_percentage AS cache_hit_pct,
    estd_overalloc_count AS overallocation_count
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

-- Estatísticas de PGA
SELECT
    name,
    ROUND(value/1024/1024, 2) AS value_mb
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'aggregate PGA auto target',
    'total PGA inuse',
    'total PGA allocated',
    'maximum PGA allocated'
);

-- Identificar sorts em disco (problema de PGA)
SELECT
    name,
    value
FROM v$sysstat
WHERE name IN (
    'sorts (memory)',
    'sorts (disk)',
    'workarea executions - optimal',
    'workarea executions - onepass',
    'workarea executions - multipass'
);

-- Cálculo: % de sorts em memória (alvo: > 95%)
SELECT
    ROUND((mem.value / NULLIF(mem.value + disk.value, 0)) * 100, 2) AS pct_memory_sorts
FROM
    (SELECT value FROM v$sysstat WHERE name = 'sorts (memory)') mem,
    (SELECT value FROM v$sysstat WHERE name = 'sorts (disk)') disk;

✅

Resultado Esperado

Competências a Dominar

✓

Configurar memória

AMM, manual SGA/PGA, componentes individuais

✓

Monitorar buffer cache

Hit ratios, advisory, objetos em cache

✓

Monitorar shared pool

Library cache, reloads, SQL em memória

✓

Otimizar PGA

Reduzir sorts em disco, ajustar aggregate target

✓

Usar advisory views

Dimensionar memória baseado em dados

✓

Diagnosticar problemas

Identificar contenção, falta de memória

Benchmarks de Hit Ratios

Buffer Cache Hit Ratio: > 95% = Excelente | 90-95% = Bom | < 90% = Aumentar buffer cache

Library Cache Hit Ratio: > 95% = Excelente | 90-95% = Aceitável | < 90% = Aumentar shared pool

Sorts em Memória: > 95% = Excelente | 90-95% = Bom | < 90% = Aumentar PGA

Atenção: Hit ratios altos não garantem boa performance. Analise sempre em conjunto com wait events e AWR.

Continue sua jornada

Ver Conteúdo Completo Conteúdo Compacto