Conselheiros do Oracle 10g
Robert Freeman
O presidente tem conselheiros; advogados fazem aconselhamento; quando ingressamos na faculdade, um orientador nos é designado; portanto, por que os DBAs Oracle não deveriam contar com alguns bons conselheiros? Bem, no Oracle Database 10g nós temos. Este artigo é o primeiro de uma serie que irá revelar os Oracle Database 10g Advisors (Conselheiros do Oracle Database 10g) com alguns detalhes.
Os novos conselheiros do Oracle 10g dependem, em grande parte, da nova infra-estrutura do Oracle. Esta consiste dos seguintes componentes:
· Advanced Workload Repository (AWR – Repositório Avançado de Carga de Trabalho);
· Automated Database Diagnostic Monitor (ADDM – Monitor Automatizado de Diagnóstico de Banco de Dados).
Neste artigo introduziremos o principal componente da infra-estrutura que constitui os conselheiros: o Advanced Workload Repository.
O AWR
O banco de dados Oracle utiliza o AWR para detecção e análise de problemas, bem como para auto-ajuste. Diferentes estatísticas são coletadas pelo AWR, incluindo eventos de espera, estatísticas de modelo de tempo (time model statistics), estatísticas de histórico de sessão ativa, estatísticas de sistema (system statistics) e a nível de sessão (session-level statistics), estatísticas de uso de objetos e ainda informações sobre cláusulas SQL mais intensivas em recursos. Outras funcionalidades do Oracle 10g usam o AWR, incluindo o ADDM e outros conselheiros que iremos discutir nesta serie de artigos.
O AWR coleta estatísticas do banco de dados a cada 60 minutos (isto pode ser configurado), estes dados são guardados por uma semana e então eliminados. As estatísticas coletadas pelo AWR são armazenadas no banco de dados. Para coletar adequadamente as estatísticas do banco de dados, o parâmetro statistics_level deverá ser configurado para TYPICAL (o padrão) ou ALL.
Caso deseje explorar o repositório AWR, sinta-se à vontade. O AWR é formado por várias tabelas pertencentes ao esquema SYS. Tipicamente são armazenadas no tablespace SYSAUX (atualmente não existe nenhum método para mover estes objetos para outros tablespace). Todos os nomes de tabelas AWR começam com o identificador “WR”, seguido de um mnemônico que identifica o tipo de designação das tabelas, seguido pelo símbolo de dólar ($). As tabelas AWR possuem três diferentes tipos de designações:
· Metadados: WRM$;
· Dados históricos: WRH$;
· Tabelas AWR relacionadas às funções dos conselheiros: WRI$.
A maioria dos nomes de tabelas AWR são bastante auto-explicativos, tal como WRM$_SNAPSHOT ou WRH$_ACTIVE_SESSION_HISTORY.
Além do repositório AWR, o Oracle 10g também oferece várias tabelas através das quais é possível consultar o repositório AWR. Todos os nomes de tabelas começam com DBA_HIST, seguido pelo nome que descreve a tabela. Isto inclui nomes de tabelas tais como DBA_HIST_FILESTATS, DBA_HIST_DATAFILE, ou DBA_HIST_SNAPSHOT.
Gerenciando manualmente o AWR
Apesar do AWR ter sido projetado para funcionar em modo automático, há possibilidade de operá-lo manualmente. Podemos, por exemplo, modificar o intervalo de coleção de snapshots (snapshot collection interval) (ler Nota 1) e o critério de retenção (retention criteria), além de criar e remover snapshots. Iremos dar uma olhada neste processo com mais detalhe nas próximas secções.
Nota 1. Snapshot
Snapshot é uma réplica de um master em um determinado ponto do tempo.
Coleção e retenção manual de snapshots
Podemos modificar o intervalo de coleção de snapshots utilizando o pacote dbms_workload_repository. O procedimento dbms_workload_repository.modify_snapshot_settings é utilizado neste exemplo para modificar:
· uma coleta de snapshots para que ocorra a cada 15 minutos,
· a retenção de dados de snapshots para 20160 minutos.
-- Isto faz com que o repositório seja atualizado a cada 15 minutos
-- e retenha todos os dados por 2 semanas.
Exec dbms_workload_repository.modify_snapshot_settings
(retenção=>20160, intervalo=> 15);
Vale aqui uma observação importante: configurar o parâmetro de intervalo para zero desabilitará todas as coletas de estatísticas.
Para visualizar todas as configurações atuais do intervalo e retenção do AWR, usamos uma visualização DBA_HIST_WR_CONTROL. Eis aqui um exemplo de uso desta visualização:
SELECT * FROM dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION
---------- -------------------- --------------------
2139184330 +00000 01:00:00.0 +00007 00:00
Neste exemplo, podemos observar que o intervalo de snapshot foi configurado para ser coletado a cada hora (o padrão), e o período de retenção foi configurado para sete dias.
Criando ou removendo snapshots
Podemos usar o pacote dbms_workload_repository para criar ou remover snapshots. O procedimento dbms_workload_repository.create_snapshot cria um snapshot manualmente no AWR:
EXEC dbms_workload_repository.create_snapshot;
Podemos ver quais snapshots existem atualmente no AWR utilizando o visualizador DBA_HIST_SNAPSHOT, como visto na Listagem 1.
SELECT snap_id, begin_interval_tempo, end_interval_tempo
FROM dba_hist_snapshot
ORDER BY 1;
SNAP_ID END_INTERVAL_TIME
---------- -------------------------
1107 03-OCT-04 01.24.04.449 AM
1108 03-OCT-04 02.00.54.717 AM
1109 03-OCT-04 03.00.23.138 AM
1110 03-OCT-04 10.58.40.235 PM
Listagem 1. Visualizando snapshots.
Para cada snapshot é atribuído um único ID, que é exibido na coluna SNAP_ID. Se tivermos dois snapshots, o primeiro irá sempre ter uma SNAP_ID menor que o último. A coluna END_INTERVAL_TIME exibe a hora em que o snapshot atual foi coletado.
Eventualmente, desejaremos dar baixa (drop) de snapshots manualmente. O procedimento dbms_workload_repository.drop_snapshot_range pode ser utilizado para remover uma faixa de snapshots do AWR. Este procedimento faz uso de dois parâmetros, low_snap_id e high_snap_id, como visto neste exemplo:
EXEC dbms_workload_repository.drop_snapshot_range -
(low_snap_id=>1107, high_snap_id=>1108);
Os snapshots automatizados do AWR
O Oracle 10g utiliza uma tarefa programada (a GATHER_STATS_JOB) para coletar as estatísticas AWR. Esta tarefa é criada e habilitada automaticamente quando for criado um novo banco de dados Oracle sob o Oracle 10g. Para ver esta tarefa, utilizamos o visualizador DBA_SCHEDULER_JOBS (ver Listagem 2).
SELECT a.job_name, a.enabled, c.window_name, c.schedule_name,
c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a,
dba_scheduler_wingroup_members b,
dba_scheduler_windows c
WHERE job_name=’GATHER_STATS_JOB’
And a.schedule_name=b.window_group_name
And b.window_name=c.window_name;
Listagem 2. Utilizando o DBA_SCHEDULER_JOBS.
Podemos desabilitar esta tarefa usando o procedimento dbms_scheduler.disable:
Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);
E podemos habilitar a tarefa usando o procedimento dbms_scheduler.enable:
Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);
Os relatórios de snapshots do AWR
O Oracle disponibiliza relatórios que podemos rodar para analisar os dados no AWR. Existem dois relatórios: awrrpt.sql e awrrpti.sql, disponíveis na pasta $ORACLE_HOME/rdbms/admin. A saída desses relatórios são essencialmente as mesmas, exceto pelo fato de que o script awrrpti.sql permite que seja definida uma instância específica a ser relatada.
Os relatórios são muito similares aos antigos relatórios statspack (anteriores ao Oracle 10g), em que você define uma ID de início e uma de fim de snapshot e o nome do arquivo de saída dos relatórios. Adicionalmente, podemos optar por produzir o relatório tanto no formato texto quanto no formato HTML.
Os pontos de partida (baselines) do AWR
Ponto de partida é definido como uma faixa de snapshot que pode ser utilizada para ser comparada com outros pares de snapshots. O principal propósito de um ponto de partida é o de preservar as estatísticas típicas de tempo de execução no repositório AWR, permitindo rodar a qualquer momento, os relatórios de snapshots AWR no ponto de partida de snapshots preservados, e compará-los com os snapshots mais recentes contidos no AWR. Isto nos permite comparar o desempenho atual (e as configurações) com o desempenho relativo a um ponto de partida estabelecido, auxiliando na identificação de problemas de desempenho do banco de dados. Nesta seção, iremos aprender como criar, remover e utilizar pontos de partida.
Criando pontos de partida
Podemos usar o procedimento create_baseline contido no dbms_workload_repository do pacote PL/SQL para criar um ponto de partida:
EXEC dbms_workload_repository.create_baseline -
(start_snap_id=>1109, end_snap_id=>1111, -
baseline_name=>’EOM Baseline’);
Pontos de partida podem ser observados utilizando o visualizador DBA_HIST_BASELINE, como visto no exemplo da Listagem 3.
SELECT baseline_id, baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;
BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
----------- --------------- ------------- -----------
1 EOM Baseline 1109 1111
Listagem 3. Visualizando baselines.
Neste caso, a coluna BASELINE_ID identifica cada ponto de partida que foi definido. O nome atribuído para o ponto de partida é listado, assim como as IDs de início e fim de snapshot.
Removendo pontos de partida
Podemos remover um ponto de partida usando o procedimento dbms_workload_repository.drop_baseline. O exemplo abaixo remove o “EOM Baseline” que acabamos de criar:
EXEC dbms_workload_repository.drop_baseline
(baseline_name=>’EOM Baseline’, Cascade=>FALSE);
Note que o parâmetro cascade irá remover todos os snapshots associados se for configurado como TRUE. Caso contrário, os snapshots serão removidos automaticamente pelo processo automatizado do AWR.
Gerenciando o AWR com Oracle Enterprise Manager
Temos demonstrado o uso do pacote dbms_workload_repository para gerenciar o repositório AWR. O Oracle também permite gerenciar o AWR através Oracle Enterprise Manager. O OEM disponibiliza uma boa interface para o gerenciamento do AWR. A partir da página principal do OEM, acessamos a página de administração (ver Figura 1).
Figura 1. Utilizando o OEM.
Na base da página, sob “Workload”, perceba o link para o Automatic Workload Repository. A seleção desta opção nos leva para a página AWR que é vista na Figura 2.
Figura 2. Acessando AWR via OEM.
Esta página disponibiliza um resumo das configurações atuais do AWR e nos dá uma opção para modificá-las. Podemos também olhar detalhes a respeito dos snapshots no AWR e criar pontos de partida de snapshots (chamados, no OEM, de snapshots preserved sets).
Vejamos agora a página de edição de configurações do AWR e, depois, o gerenciamento de snapshots.
A página de edição de configurações do AWR
Pressionando o botão EDIT na página AWR, somos encaminhados para a página de edição de configurações do AWR. Esta página nos permite especificar:
· o período de retenção de snapshots;
· a freqüência para coleta de snapshots (ou se a coleta será desligada);
· o atual nível de coleta das estatísticas de banco de dados.
A Figura 3 apresenta a página Edit settings do AWR.
Figura 3. Configurando o AWR.
Para efetivar a mudança, simplesmente clicamos no botão de radio apropriado e/ou mudamos o valor apropriado e pressionamos OK. Também notemos o botão show SQL (mostrar SQL). Este botão mostra o comando SQL que o Oracle irá executar para realizar a mudança.
Detalhes dos snapshots
Podemos também obter detalhes de snapshots clicando no link snapshots na página AWR (ver Figura 4).
Figura 4. Detalhes de snapshots.
A página de snapshots exibe os últimos snapshots coletados pelo AWR e permite, caso desejado, que revisemos antigos snapshots. Clicando em um número específico de snapshot, podemos observar detalhes informativos a seu respeito, ou se desejado, podemos obter um relatório para impressão baseado no snapshots selecionado.
Snapshots preservados
Desejando criar ou gerenciar coleções de snapshots preservados (também conhecidos como pontos de partida) em uma página AWR, clicamos no link preserved snapshot sets, o que nos conduz à página de coleções de snapshots preservados (ver Figura 5).
Figura 5. Coleção de snapshots preservados.
Podemos clicar no botão Create Preserved Snapshot Set (Criar Coleção de Snapshots Preservados), no canto superior direito da tela, para criar uma nova coleção de snapshots. O Oracle irá solicitar o início e fim dos snapshots a serem designados para a coleção de snapshots preservados. Uma vez criados os snapshots, podemos utilizar as opções disponibilizadas no campo Actions para realizar várias ações tais como:
· criar coleções de ajuste de SQL (SQL tuning sets);
· criar relatórios statspack muito similares aos relatórios de versões anteriores do Oracle;
· criar uma tarefa ADDM, que irá analisar a coleção de snapshots;
· produzir um relatório de análise.
Podemos também deletar as coleções de snapshots preservados e podemos comparar duas coleções de snapshots. Comparar snapshots nos permite verificar as diferenças existentes entre um ponto de partida de snapshots e uma coleção de snapshots recente. Usando o relatório gerado por esta ação, podemos determinar se o desempenho atual do sistema está divergindo em relação ao desempenho do ponto de partida.
O que vem por aí
O AWR é a estrutura base para o funcionamento dos novos conselheiros introduzidos no Oracle 10g para facilitar a administração do banco de dados. O AWR junto com o Automatic Database Diagnostic Monitor (ADDM), que iremos analisar no próximo artigo, formam a infra-estrutura básica para os outros conselheiros de banco de dados.
Robert Freeman é consultor Oracle, possuindo uma experiência neste SGBD de mais de 15 anos.
