Usando Advisors de Banco de Dados

Por Sushma Jagannath
Postado en Junho 2008

Obtenha orientações sobre os principais desafios do gerenciamento e melhore a performance no Banco de Dados Oracle 11g.

Os advisors são ferramentas poderosas que oferecem orientações específicas sobre como abordar os principais desafios do gerenciamento de banco de dados, cobrindo uma ampla variedade de áreas, incluindo gerenciamento de undo, performance e espaço. Os advisors são desenvolvidos com base em dois componentes de infra-estrutura.

Automatic Workload Repository (AWR). Este repositório fornece serviços para coletar, manter e utilizar estatísticas para fins de detecção de problemas e auto-ajuste. As informações estatísticas são armazenadas no AWR na forma de snapshots.

Automatic Database Diagnostic Monitor (ADDM). Este monitor faz análise, detecta gargalos e recomenda soluções. As recomendações podem incluir o tipo de advisor que precisa ser usado para resolver o problema.

Esta coluna se concentra em alguns dos advisors de banco de dados que são chamados pelo ADDM para ajudá-lo a melhorar a performance do banco de dados. Ela apresenta amostras de perguntas do tipo que você pode encontrar ao prestar o exame Oracle Database 11g Administration Workshop I, que o qualifica para obter o nível de certificação Oracle Certified Associate.

SQL Tuning Advisor

O SQL Tuning Advisor analisa problemas com instruções SQL individuais, como um plano otimizador de baixa performance ou o uso equivocado de algumas instruções SQL, além de fazer recomendações para melhorar a performance. Você pode executar o SQL Tuning Advisor em instruções SQL de uso intensivo de recursos, em um conjunto de instruções SQL durante um período ou a partir de uma carga de trabalho SQL. Geralmente, esse advisor é executado em resposta a uma constatação de performance do ADDM que recomenda seu uso.

O Banco de Dados Oracle 11g introduz o SQL Tuning Advisor automático, que pode ser configurado para ser executado automaticamente durante intervalos de manutenção do sistema como uma tarefa de manutenção. Durante cada execução automática, o advisor seleciona consultas SQL de alta carga no sistema e gera recomendações sobre como ajustá-las.

John começa a criar uma nova tabela com base nos dados da tabela do cliente. Os critérios a seguir precisam ser aplicados nos dados:

  • Todas as colunas da tabela do cliente precisam estar disponíveis na nova tabela.
  • A nova tabela precisa ter dados somente para os clientes cujo pedido médio é de US$ 1 milhão ou mais por trimestre, que não efetuaram pagamentos nos últimos dois pedidos e cujo período de pagamento ultrapassou o período de crédito.

John observa que o processo de criação de tabelas está demorando bastante para ser concluído. O DBA habilitou o SQL Tuning Advisor automático com implementação automática, mas ao executar o advisor, observa que essa instrução SQL estava malformada e não ajustada automaticamente. Por que o servidor não ajustou automaticamente essa instrução?

  1. O SQL Tuning Advisor automático ignora instruções CREATE TABLE AS SELECT.
  2. O SQL Tuning Advisor automático ignora instruções CREATE TABLE.
  3. O SQL Tuning Advisor automático ajusta somente consultas SQL.
  4. O SQL Tuning Advisor automático não ajusta instruções DML.

A resposta correta é A. Mesmo se o SQL Tuning Advisor automático for habilitado, ele não resolverá todos os problemas de performance de SQL. Ele não resolve automaticamente problemas com os seguintes tipos de instruções SQL: CREATE TABLE AS SELECT e INSERT SELECT, SQL específico ou raramente repetido, consultas paralelas e SQL recursivo.

Você recebeu reclamações sobre a queda na performance de consultas SQL e identificou as consultas SQL com uso mais intensivo de recursos. Qual é o próximo passo para obter recomendações sobre como reestruturar instruções SQL para melhorar a performance das consultas?

  1. Executar o Segment Advisor
  2. Executar o SQL Tuning Advisor nas instruções SQL de uso mais intensivo de recursos
  3. Executar o relatório AWR
  4. Executar o ADDM nas instruções SQL de uso mais intensivo de recursos

A resposta correta é B. Depois de identificar as instruções SQL de uso mais intensivo de recursos, você usa o SQL Tuning Advisor para obter recomendações sobre como ajustá-los. A resposta A está incorreta porque o Segment Advisor gera relatórios sobre a tendência de crescimento de segmentos e faz recomendações sobre se um segmento precisa ser diminuído. A resposta C está incorreta porque o AWR é um repositório que armazena informações relativas à performance na forma de snapshots. A resposta D está incorreta porque o ADDM usa essas estatísticas para fazer análise e detectar gargalos e depois recomenda soluções.

SQL Access Advisor

O SQL Access Advisor faz recomendações para melhorar a performance de uma carga de trabalho. Além de analisar índices e visualizações materializadas como no Banco de Dados Oracle 10g, o SQL Access Advisor no Banco de Dados Oracle 11g analisa tabelas e consultas e faz recomendações sobre como otimizar estruturas de armazenamento.

O SQL Access Advisor ajusta um esquema a uma carga de trabalho específica. Normalmente, ao usar o SQL Access Advisor para ajuste de performance, você executa as seguintes etapas: criar uma tarefa, definir a carga de trabalho, gerar recomendações e implementá-las.

Você pode usar o SQL Access Advisor para receber recomendações sobre:

  1. Modificações de esquema
  2. Ajuste de instruções SQL de uso intensivo de recursos
  3. Melhoria do plano de execução de instruções SQL
  4. Carga de trabalho SQL

As respostas corretas são a A e a D. O SQL Access Advisor analisa uma carga de trabalho inteira e recomenda alterações em índices, visualizações materializadas e tabelas para melhorar a performance. As respostas B e C estão incorretas porque o SQL Access Advisor faz recomendações sobre como ajustar instruções SQL de uso intensivo de recursos e melhorar o plano de execução das instruções SQL.

Memory Advisor

O Memory Advisor é um conjunto de várias funções de advisor que ajudam a determinar as melhores configurações da memória total usada pela instância do banco de dados. Elas oferecem análises gráficas das configurações de destino da memória total (conforme mostra a Figura 1), configurações de destino de SGA e PGA ou configurações de tamanho de componente SGA. As análises são usadas para ajustar a performance do banco de dados e para planejamento hipotético. Vários Memory Advisors estão disponíveis para ajuste de memória (observe que a disponibilidade desses advisors depende de os recursos de Automatic Memory Management [AMM] e Automatic Shared Memory Management [ASMM] estarem habilitados ou não): O SGA Advisor fornece informações sobre melhoria percentual no tempo do banco de dados para vários tamanhos de SGA, o advisor de pool compartilhado fornece informações sobre o tempo de análise estimado no pool compartilhado para diferentes tamanhos de pool, o advisor de cache de buffer fornece informações sobre leituras físicas e o tempo para o tamanho do cache e o PGA Advisor fornece informações sobre a porcentagem de acertos de cache em comparação com o tamanho da memória de destino de PGA.

Figura 1
Figura 1: Configurações de destino da memória total


Você habilitou os recursos AMM e ASMM no banco de dados e usa o Oracle Enterprise Manager para gerenciá-lo. Quais Memory Advisors – de tamanho de memória, de pool compartilhado, de cache de buffer ou de pool Java – você conseguirá usar?

  1. Somente o advisor de tamanho de memória
  2. Somente o advisor de pool compartilhado
  3. Todos os quatro Memory Advisors
  4. Advisor de pool compartilhado, de cache de buffer e de pool Java

A resposta correta é A. Quando AMM e ASMM estão habilitados, o sistema se adapta às alterações na carga de trabalho dimensionando automaticamente os componentes de SGA e PGA. Como você não receberá orientação sobre esses componentes individuais do SGA, os advisors correspondentes serão desabilitados.

Undo Advisor

O Undo Advisor ajuda a determinar o tamanho do tablespace de undo. Você pode calcular o tamanho mínimo do tablespace de undo, com base nas estatísticas coletadas ou durante um período de tempo designado ou durante um período de retenção de undo. Usando as estatísticas de tempo de execução coletadas no AWR, você pode usar o Undo Advisor para extrapolar como os futuros requisitos podem prejudicar o tamanho do tablespace de undo. Daí, pode usar a página Undo Management no Oracle Enterprise Manager para fazer alterações recomendadas pelo Undo Advisor.

Você é um DBA de um sistema de processamento de transações online (OLTP) que suporta milhares de usuários e milhões de transações diariamente. Como parte da atividade de ajuste periódico, você pretende usar o Undo Advisor para garantir que o tamanho do tablespace de undo atenda aos requisitos da transação de maior duração da instância. Quais informações o advisor usará para determinar o tamanho do tablespace de undo?

  1. O período de tempo de análise
  2. O período de retenção de undo
  3. O índice de geração de undo
  4. A quantidade de tablespaces de undo no banco de dados

As respostas corretas são A, B e C. O Undo Advisor usa o período de tempo de análise, o período de retenção de undo e o índice de geração de undo para recomendar o tamanho mínimo do tablespace de undo que pode atender aos requisitos da transação de maior duração. A resposta D está incorreta porque somente um tablespace de undo está ativo em qualquer determinado momento, por isso não importa quantos tablespaces de undo um banco de dados tenha.

Conclusão

Esta coluna se concentrou em alguns advisors que ajudam a gerenciar e ajustar seu banco de dados:

  • O SQL Tuning Advisor faz recomendações sobre ações como reescrever a instrução, alterar a configuração da instância e adicionar índices.
  • O SQL Tuning Advisor pega uma carga de trabalho SQL como entrada e recomenda quais índices, visualizações materializadas e logs criar, ignorar ou reter para garantir maior performance.
  • O Memory Advisor oferece análises gráficas de configurações de destino da memória total, configurações de destino de SGA e PGA ou configurações de tamanho de componente SGA.
  • O Undo Advisor determina o tamanho do tablespace de undo necessário para suportar um determinado período de retenção.


Sushma Jagannath ( ocpexam_ww@oracle.com) é gerente de desenvolvimento de exames de certificação da Oracle. Ela está na empresa desde 2000.