Conselheiros do Oracle 10g – Parte II

Robert Freeman

No primeiro artigo desta série, apresentamos o Advanced Workload Repository (AWR - Repositório Avançado de Carga de trabalho), que é um componente fundamental para a nova arquitetura do consultor de banco de dados do Oracle Database 10g. Vimos o que é o AWR, como gerenciá-lo e como tirar proveito dos dados nele contidos. O Oracle Database 10g provê muito mais do que uma simples coleção de estatísticas, e introduz o processo de análise e informação das estatísticas coletadas via uma nova ferramenta chamada Automated Database Diagnostic Monitor (ADDM - Monitor Automático de Diagnóstico de Banco de Dados Automatizado). Neste artigo, apresentaremos o ADDM.

Introdução ao ADDM

Imagine que tenhamos um banco de dados e queiramos não só administrá-lo, mas gerenciá-lo efetivamente. Até agora, a abordagem nativa do Oracle para análise de banco de dados era implementada por um processo manual que envolvia a coleta e análise de relatórios do statspack. A análise manual dos problemas de banco de dados não é propriamente uma solução escalável, e seu sucesso não é repetível. O que precisamos é um modo automatizado para executar esta análise, de forma a poder analisar muitos bancos de dados empresariais com resultados que sejam consistentes. Bem-vindo ao Oracle database 10g e ao ADDM.

Como mencionamos na primeira parte deste artigo, os snapshots do AWR acontecem, por padrão, a cada hora (mas podem ser programados para acontecer com maior ou menor freqüência). Uma vez que o snapshot de AWR tenha sido realizado, a análise do ADDM acontece automaticamente desde que o parâmetro STATISTICS_LEVEL seja estabelecido como TYPICAL ou ALL. O novo processo MMON de segundo plano (background process) do Oracle Database 10g executa a análise ADDM. Os resultados da execução do ADDM são armazenados no AWR, e podem ser acessados por vários meios: OEM, por consulta manual ou por uma API provida pelo Oracle.

Um dos parâmetros que o ADDM usa para executar sua análise é o DBIO_EXPECTED. Este parâmetro não é especificado no arquivo de parâmetros de banco de dados (database parameter file - pfile), mas através do pacote DBMS_ADVISOR provido pelo Oracle. Este parâmetro define o tempo de resposta de E/S do disco de sistema esperado pelo Oracle, e seu valor padrão é de 10 milisegundos. Se tivermos discos mais lentos, podemos reajustar este valor. No exemplo da Listagem 1, reajustamos o tempo de resposta esperado do disco para 20 milisegundos, desde que esse é nosso tempo de resposta medido para os nossos discos.

 

Exec dbms_advisor.set_default_task_parameter(’ADDM’, -
’DBIO_EXPECTED’, 20000);

Listagem 1. Ajuste do tempo de resposta esperado para o disco.

Objetivos da análise do ADDM

O objetivo do ADDM é melhorar o valor de uma estatística chamada db time. O db time é um valor que o Oracle calcula para indicar o tempo cumulativo que é gasto no processamento das requisições do usuário. O objetivo do ADDM é reduzir o valor do db time total, e seu alvo não são os usuários individuais ou os tempos de resposta de usuário. Portanto, o db time que é usado é um valor agregado geral do tempo de CPU do sistema e dos tempos de espera (wait times). Podemos observar o valor atual do db time para o sistema inteiro examinando a view V$SYS_TIME_MODEL, ou então para uma determinada sessão, através da view V$SESS_TIME_MODEL, como mostrado na Listagem 2.

select sum(value) "DB time” from v$sess_time_model
where stat_name='DB time';

    DB time

----------

    109797

Listagem 2. Verificação do db time de uma sessão.

 

Em um esforço para reduzir o DB Time, o ADDM analisa vários problemas diferentes relacionados a banco de dados, incluindo:

·         problemas relacionados à memória, tais como a shared pool latch contention (contenção por travamento do pool compartilhado), problemas com o log buffer ou problemas relacionados ao database buffer cache (buffer de cache do banco de dados);

·         gargalos de CPU;

·         problemas de Disk I/O performance;

·         problemas de configuração do banco de dados;

·         problemas relacionados a espaço tais como tablespaces que estouram o espaço disponível;

·         problemas relacionados ao tuning das aplicações e do SQL, tais como parsing (análise gramatical) excessiva e locking (bloqueio) excessivo.

Análise dos resultados do ADDM

Os resultados da análise do ADDM são armazenados no AWR na forma de laudos (findings). Há três tipos diferentes de laudos:

·         problem (problema): indica a causa raiz de um problema que está provocando uma queda de desempenho no banco de dados;

·         symptom (sintoma): indica um problema de desempenho, o qual normalmente aponta para um ou mais laudos de problemas específicos;

·         information (informação): estas são apenas informações básicas relacionadas ao banco de dados que não estão atreladas a um problema específico.

 

O ADDM também categoriza os laudos com base na indicação do laudo quanto à proporção do DB time que parece estar sendo consumido. O Oracle provê recomendações associadas com os laudos que podem ser usados para reduzir o valor do DB time total e melhorar o desempenho global do banco de dados Oracle. Cada laudo pode ter múltiplas recomendações com alternativas diferentes para resolver o problema.

Cada recomendação consiste em dois elementos: a ação e a razão. A ação é a sugestão de correção que o Oracle quer que seja implementada para resolver o problema identificado no laudo. A razão explica os motivos pelos quais o Oracle está sugerindo a ação, e pode incluir informação adicional de como implementar a ação.

Localizando os laudos

Há vários modos de verificar os laudos do ADDM. Nas próximas seções, descobriremos como examinar os laudos do ADDM manualmente. Então, veremos os benefícios decorrentes do uso do OEM para observar os laudos do ADDM. Finalmente, veremos como rodar o script do addmrpt.sql, o qual pode ser usado para executar uma análise ADDM em um intervalo de snapshots do AWR.

Usando o dicionário de dados Oracle para verificar os laudos do ADDM

As principais views do dicionário de dados que serão usadas para recuperar os laudos do ADDM são:

·         DBA_ADVISOR_TASKS: contém informações específicas sobre cada tarefa. A execução de uma análise ADDM é uma tarefa isolada (outras tarefas incluem execução dos vários consultores que serão apresentados em artigos posteriores);

·         DBA_ADVISOR_RECOMMENDATIONS: esta view fornece as recomendações associadas com a execução específica de uma tarefa ADDM;

·         DBA_ADVISOR_FINDINGS: esta view fornece os laudos associadas com uma execução específica de uma tarefa ADDM;

·         DBA_ADVISOR_RATIONALE: esta view fornece as razões associadas com as recomendações específicas do ADDM.

 

Se quisermos observar os laudos específicos e as recomendações da última execução do ADDM, podemos executar a consulta da Listagem 3.

Set pages 1000
Set lines 75
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status='COMPLETED'
Order by b.impact, d.rank;

Listagem 3. Laudo e recomendações da última execução do ADDM.

 

A Listagem 4 apresenta a saída da execução da consulta apresentada na Listagem 3.

EXECUTION TYPE            IMPACT       RANK                       TYPE
--------- ----------- ---------- ---------- --------------------------
MESSAGE
----------------------------------------------------------------------
COMMAND
----------------------------------------------------------------------
ACTION_MESSAGE
----------------------------------------------------------------------
14-DEC-04 PROBLEM       41889449          2         Host Configuration
Message           : Waits on event "log file sync" while performing CO
MMIT and ROLLBACK operations were consuming significant database time.
Command To correct: UNDEFINED
Action Message    : Investigate the possibility of improving the
performance of I/O to the online redo log files.


14-DEC-04 PROBLEM       53727085          1                 SQL Tuning
Message           : SQL statements consuming significant database time
 were found.
Command To correct: RUN SQL TUNING ADVISOR
Action Message    : Run SQL Tuning Advisor on the SQL statement with
SQL_ID "8hk7xvhua40va".


14-DEC-04 PROBLEM      177916166          0             DBConfiguration
Message           : The buffer cache was undersized causing significant additional read I/O.
Command To correct: ALTER PARAMETER
Action Message    : Increase SGA target size by increasing the value of parameter "sga_target" by 68 M.


14-DEC-04 PROBLEM     6.4314E+10          1        Application Analysis
Message           : Read and write contention on database blocks was
consuming significant database time.
Command To correct: UNDEFINED
Action Message    : Trace the cause of object contention due to SELECT
 statements in the application using the information provided.

Listagem 4. Saída da consulta apresentada na Listagem 3.

 

Nesta saída (Listagem 4) vemos a data da análise, o tipo de laudo (neste caso, tudo o que achamos foram problemas), uma mensagem que indica o que está causando o problema, o comando necessário para corrigir o problema e informações adicionais na forma de uma mensagem de ação. Observamos que o problema maior parece ser o arquivo de registro de sync de espera (log file sync waits), seguido por um problema de declaração de SQL (note que é informado o SQL_ADDRESS para aquela declaração, permitindo a fácil referência cruzada que o conduz à área de SQL).

Uma coisa que deveríamos notar é que as análises do ADDM ainda são muito incipientes. Como resultado, algumas análises e avaliações são bem elementares, e sugerem ações que nem sempre serão úteis. Mesmo assim, vale a pena dar uma olhada no ADDM. Para o DBA iniciante, pode ser útil para resolver os problemas mais corriqueiros. Contrastando com o estado elementar atual do ADDM, alguns dos consultores (como os consultores de tuning do SQL e os consultores de views materializadas) são bastante úteis.

Usando o OEM para observar os laudos do ADDM

Uma das novas características incorporadas ao OEM no Oracle 10g é a habilidade para elaborar os relatórios da análise ADDM do banco de dados. É muito mais fácil que escrever em SQL, posso assegurar. Através do OEM, podemos observar os resultados mais recentes da execução do ADDM, ou executar uma análise para um intervalo diferente de snapshots e verificar os resultados. Vejamos cada uma destas diferentes opções com mais detalhe.

Usando o OEM para observar os mais recentes laudos do ADDM

Para localizar os laudos do ADDM mais recentes, clique em GO, localizado na parte inferior da home page do OEM e clique no link Advisor Central (Central do Consultor). Este link nos conduz para a página do Advisor Central. Na parte inferior da página, acharemos a seção Results (Resultados). Se o ADDM estiver trabalhando corretamente, veremos uma linha na seção de Results onde acharemos o ADDM na coluna tipo de consultor. Esta deverá ser a mais recente execução do ADDM e podemos ver isto verificando a data e a hora que aparecem nas colunas Start Time e End Time (Hora Inicial e Hora Final) (Figura 1).

 

 SQL_ORA_01.JPG

Figura 1. O Advisor Central, no OEM.

 

Agora basta clicar no link da coluna NAME e observar os resultados da última execução do ADDM. Será exibida a tela do ADDM mostrada na Figura 2.

 

 SQL_ORA_02.JPG

Figura 2. Visualização dos resultados da última execução do ADDM.

 

A tela da Figura 2 também lhe dá a oportunidade, caso deseje, de verificar as informações completas do ADDM. Simplesmente clique no botão View Report, e o mesmo será exibido.

Podemos também observar nesta tela os resultados das execuções anteriores do ADDM. Na parte superior da tela do ADDM, sob a seção Database Activity (Atividade do Banco de Dados) (Figura 3), notaremos um quadro chamado Database Activity. Neste quadro, logo acima do cabeçalho da seção Performance Analysis (Análise de Desempenho), há uma lista de seleção que lhe permite escolher a execução do ADDM na qual estivermos interessados.

 

 SQL_ORA_03.JPG

Figura 3. Visualização as atividades de banco de dados.

 

Na tela da Figura 3 observamos que a atividade inicia no dia 18 de dezembro, continua sendo executado até o dia 19 de dezembro, e temos um pequeno pico de atividade entre as 07:00 horas e as 09:00 horas da tarde no dia 18.

Gerando um relatório ADDM personalizado (custom reports) com o OEM

O OEM suporta a geração de relatórios ADDM personalizados para um intervalo específico de snapshots. Para gerar um relatório ADDM personalizado a partir da página OEM ADDM, clique no botão Create ADDM Task (Criar Tarefa ADDM). Isto nos conduzirá à página Create ADDM Task como visto na Figura 4.

 

SQL_ORA_04.JPG 

Figura 4. Criação de um relatório ADDM personalizado.

 

Selecione os valores de hora inicial e hora final do período que correspondem aos snapshots para os quais desejamos o relatório e, a seguir, clique em OK para gerar o relatório ADDM para este período. O relatório resultante tem o mesmo aspecto daquele previamente mostrado.

Usando o Script addmrpt.sql para analisar um intervalo de snapshots

O relatório extraído através do script addmrpt.sql tem o mesmo aspecto dos relatórios extraídos através do statspack disponível em versões anteriores do Oracle. Rodamos o script addmrpt.sql a partir do SQL plus (o script está localizado no diretório $ORACLEHOME/rdbms/admin). O script retorna uma lista de snapshots a partir dos quais podemos gerar o relatório. Selecionamos um snapshot inicial e final, e finalmente, definimos o nome do relatório que desejamos que seja criado pelo addmrpt.sql. O addmrpt.sql fará então a análise ADDM do par de snapshots e dará saída da mesma no relatório.

O relatório resultante contém um cabeçalho (Listagem 5) seguido das informações de laudo detalhadas.

   DETAILED ADDM REPORT FOR TASK 'TASK_3455' WITH ID 3455
          ------------------------------------------------------                 


           Analysis Period: 10-JAN-2005 from 22:00:54 to 23:00:19
     Database ID/Instance: 2151721087/1
  Database/Instance Names: ROB10G/rob10g     
                Host Name: RFMOBILE
         Database Version: 10.1.0.3.0
            Database Time: 609 seconds
           Snapshot Range: from 1570 to 1574
    Average Database Load: 1.2 active sessions

Listagem 5. Cabeçalho do relatório extraído através do script addmrpt.sql.

 

Após o cabeçalho, são listados os laudos individuais (Listagem 6).

FINDING 1: 51% impact (309 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
         "db78fxqxwxt7r".
         RELEVANT OBJECT: SQL statement with SQL_ID db78fxqxwxt7r and
         PLAN_HASH 3879501264         
         SELECT a.emp, b.dname
         FROM EMP a, DEPT b
         WHERE a.deptno=b.deptno;

Listagem 6. Informações de laudos individuais extraídas através do script addmrpt.sql.

 

Notamos algumas coisas interessantes neste relatório. Em primeiro lugar, nosso primeiro laudo indica que o problema identificado teve um impacto global de 51 por cento no tempo de DB. Em outras palavras, o relatório do ADDM ordenou os laudos com base nos processos que estão consumindo a maior fatia de tempo do banco de dados. Observando mais adiante estes laudos, vemos que há uma declaração SQL que está causando problemas, e o ADDM sugere que façamos o tuning da mesma. O Oracle nos fornece o endereço do SQL e o valor hash, os quais nos permitem achar a declaração SQL na área de SQL.

Notamos que a ACTION sugere que rodemos o SQL Tuning Advisor (Consultor de Ajuste Fino de SQL) para gerar algumas ações de tuning sugeridas na declaração SQL em questão.

Conclusão

O ADDM representa um bom começo. Entretanto, como em qualquer novo utilitário, ainda há espaço para melhorias e crescimento em futuras liberações do Oracle. Por fim, vale informar que existem outros aspectos relacionados ao ADDM que não foram abordados neste artigo.

 

Robert Freeman é consultor Oracle, possuindo uma experiência neste SGBD de mais de 15 anos.

Veja a primeira parte deste artigo em //www.devmedia.com.br/visualizacomponente.aspx?comp=1448&site=2.