Diagnóstico com AWR e ASH | Trilha 4

📖

Definição

AWR - Automatic Workload Repository

O AWR é o repositório centralizado de estatísticas de performance do Oracle. A cada hora (padrão), um snapshot é tirado automaticamente, capturando métricas de CPU, I/O, SQL, sessões, waits e muito mais. Esses snapshots são armazenados no banco por 8 dias (padrão).

Componentes do AWR:

Snapshots: Capturas automáticas de estatísticas a cada hora
Baselines: Períodos de referência (ex: horário normal de operação)
AWR Reports: Relatórios comparando dois snapshots
Tabelas DBA_HIST_*: Armazenam histórico de estatísticas
MMON: Processo de background que coleta os dados

ASH - Active Session History

O ASH registra informações detalhadas sobre sessões ativas a cada segundo. Captura: SQL sendo executado, evento de espera, objeto acessado, módulo/programa. Mantém dados em memória (V$ACTIVE_SESSION_HISTORY) e persiste amostras no AWR (DBA_HIST_ACTIVE_SESS_HISTORY).

V$ACTIVE_SESSION_HISTORY

Dados em memória (última hora). Use para análise em tempo real de problemas de performance acontecendo agora.

DBA_HIST_ACTIVE_SESS_HISTORY

Dados persistidos (últimos 8 dias). Use para análise histórica de problemas que ocorreram no passado.

ADDM - Automatic Database Diagnostic Monitor

O ADDM é um advisor que analisa automaticamente dados do AWR e identifica problemas de performance, suas causas e recomenda soluções priorizadas por impacto.

Análise Automática

ADDM roda automaticamente após cada snapshot AWR, analisando o período entre snapshots. Identifica top SQL, contenção de recursos, problemas de I/O, configuração inadequada.

Recomendações Priorizadas

Cada finding mostra: impacto em DB Time, recomendações específicas (criar índice, ajustar parâmetro, fazer tuning de SQL) e benefício esperado.

🛠️

Aplicação Prática

Gerando AWR Reports

-- Conectar como SYSDBA
CONNECT / AS SYSDBA

-- Listar snapshots disponíveis (últimos 7 dias)
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 20 ROWS ONLY;

-- Gerar AWR Report via SQL*Plus (HTML)
@?/rdbms/admin/awrrpt.sql

-- Ou gerar via script direto
@?/rdbms/admin/awrrpti.sql  -- para instância específica (RAC)

-- Gerar AWR Report programaticamente
SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid      => (SELECT dbid FROM v$database),
    l_inst_num  => 1,
    l_bid       => 1000,  -- Snapshot ID inicial
    l_eid       => 1010   -- Snapshot ID final
));

-- Criar snapshot manual (útil antes/depois de mudanças)
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- Alterar intervalo de snapshot (padrão: 60 minutos)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    interval  => 30,   -- minutos
    retention => 10080 -- minutos (7 dias)
);

-- Ver configurações atuais
SELECT * FROM dba_hist_wr_control;

Analisando com ASH

-- Ver sessões ativas agora (última hora - memória)
SELECT
    sample_time,
    session_id,
    session_serial#,
    user_id,
    sql_id,
    event,
    wait_class,
    current_obj#,
    program,
    module
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24  -- última hora
ORDER BY sample_time DESC;

-- Top SQL por tempo de espera (últimas 24h - histórico)
SELECT
    sql_id,
    COUNT(*) AS samples,
    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS pct_activity,
    event,
    wait_class
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 1
GROUP BY sql_id, event, wait_class
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- Identificar períodos de contenção (picos de espera)
SELECT
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute,
    wait_class,
    COUNT(*) AS waits
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TIMESTAMP '2025-12-17 14:00:00'
                     AND TIMESTAMP '2025-12-17 15:00:00'
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI'), wait_class
ORDER BY minute, waits DESC;

-- Ver o que um SQL específico estava esperando
SELECT
    sample_time,
    session_id,
    event,
    wait_class,
    current_obj#,
    (SELECT object_name FROM dba_objects WHERE object_id = current_obj#) AS object_name
FROM v$active_session_history
WHERE sql_id = 'g2xb5k8f3zp9n'
ORDER BY sample_time DESC;

Consultando ADDM Reports

-- Listar ADDM tasks disponíveis
SELECT
    task_id,
    task_name,
    created,
    status
FROM dba_advisor_tasks
WHERE advisor_name = 'ADDM'
ORDER BY created DESC;

-- Ver findings de um período específico
SELECT
    task_name,
    finding_id,
    type,
    message,
    impact,
    ROUND(impact_value, 2) AS impact_seconds
FROM dba_advisor_findings
WHERE task_name = (
    SELECT task_name
    FROM dba_advisor_tasks
    WHERE advisor_name = 'ADDM'
    ORDER BY created DESC
    FETCH FIRST 1 ROW ONLY
)
ORDER BY impact_value DESC;

-- Ver recomendações do último ADDM
SELECT
    f.message AS finding,
    r.type AS recommendation_type,
    r.benefit_type,
    a.message AS action_message,
    a.command AS sql_to_execute
FROM dba_advisor_findings f
JOIN dba_advisor_recommendations r ON f.task_name = r.task_name
                                   AND f.finding_id = r.finding_id
LEFT JOIN dba_advisor_actions a ON r.task_name = a.task_name
                                AND r.rec_id = a.rec_id
WHERE f.task_name = (
    SELECT task_name
    FROM dba_advisor_tasks
    WHERE advisor_name = 'ADDM'
    ORDER BY created DESC
    FETCH FIRST 1 ROW ONLY
)
ORDER BY f.impact_value DESC, r.rec_id;

-- Gerar ADDM report manualmente para um período
DECLARE
    l_task_name VARCHAR2(100);
BEGIN
    l_task_name := 'ADDM_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');

    DBMS_ADVISOR.CREATE_TASK(
        advisor_name => 'ADDM',
        task_name    => l_task_name
    );

    DBMS_ADVISOR.SET_TASK_PARAMETER(
        task_name => l_task_name,
        parameter => 'START_SNAPSHOT',
        value     => 1000
    );

    DBMS_ADVISOR.SET_TASK_PARAMETER(
        task_name => l_task_name,
        parameter => 'END_SNAPSHOT',
        value     => 1010
    );

    DBMS_ADVISOR.EXECUTE_TASK(l_task_name);

    DBMS_OUTPUT.PUT_LINE('ADDM task created: ' || l_task_name);
END;
/

✅

Resultado Esperado

Competências a Dominar

✓

Gerar AWR Reports

Criar relatórios comparando períodos, interpretar seções principais

✓

Usar ASH para troubleshooting

Identificar o que sessões estavam fazendo em momentos específicos

✓

Interpretar ADDM findings

Entender recomendações e priorizar ações por impacto

✓

Analisar top SQL

Identificar queries que mais consomem recursos

✓

Diagnosticar wait events

Entender por que sessões estão esperando

✓

Configurar coleta AWR

Ajustar intervalo e retenção conforme necessidade

Principais Seções de um AWR Report

Load Profile: Transações/seg, SQL executions, redo size - indica volume de trabalho

Top Events: Principais wait events e % de DB Time - onde o banco gasta tempo

SQL Statistics: Top SQL por elapsed time, CPU, I/O, executions - queries problemáticas

Instance Efficiency: Hit ratios (buffer cache, library cache) - eficiência da memória

Segment Statistics: Objetos mais acessados - identifica hot objects

📝

Exercícios Práticos

Exercício 1: Análise de Performance Histórica

O sistema estava lento ontem entre 14h e 15h. Use AWR e ASH para identificar a causa.

Ver Solução

-- 1. Identificar snapshot IDs do período
SELECT snap_id, TO_CHAR(begin_interval_time, 'DD-MON HH24:MI')
FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN TIMESTAMP '2025-12-16 14:00:00'
                             AND TIMESTAMP '2025-12-16 15:00:00'
ORDER BY snap_id;

-- 2. Top wait events no período
SELECT event, wait_class, COUNT(*) AS waits
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TIMESTAMP '2025-12-16 14:00:00'
                     AND TIMESTAMP '2025-12-16 15:00:00'
GROUP BY event, wait_class
ORDER BY waits DESC
FETCH FIRST 10 ROWS ONLY;

-- 3. Top SQL no período
SELECT sql_id, COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TIMESTAMP '2025-12-16 14:00:00'
                     AND TIMESTAMP '2025-12-16 15:00:00'
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

Exercício 2: Monitoramento em Tempo Real

O banco está lento agora. Use V$ACTIVE_SESSION_HISTORY para descobrir o que está acontecendo.

Ver Solução

-- Sessions ativas e seus wait events
SELECT
    session_id,
    sql_id,
    event,
    wait_class,
    COUNT(*) AS occurrences
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '5' MINUTE
GROUP BY session_id, sql_id, event, wait_class
ORDER BY occurrences DESC;

-- SQL Text dos top SQL IDs
SELECT sql_id, sql_text
FROM v$sql
WHERE sql_id IN (
    SELECT DISTINCT sql_id
    FROM v$active_session_history
    WHERE sample_time > SYSDATE - INTERVAL '5' MINUTE
);

Continue sua jornada

Ver Conteúdo Completo Conteúdo Compacto