Oracle SQL Tuning Advisor: análise, diagnóstico e otimização de SQL

O Oracle SQL Tuning Advisor é um recurso do Oracle Database voltado à análise de instruções SQL que apresentam problemas de desempenho. A ferramenta analisa uma ou mais instruções SQL e apresenta recomendações para melhorar sua execução, podendo indicar coleta de estatísticas, criação de índices, reestruturação de SQL, SQL Profiles e SQL Plan Baselines.

Em ambientes corporativos, o Oracle SQL Tuning Advisor deve ser utilizado dentro de uma metodologia de performance que considere o comportamento histórico do banco, os SQLs de maior impacto, os planos de execução e os efeitos das alterações sobre o workload.

O SQL Tuning Advisor faz parte dos recursos de diagnóstico e otimização do Oracle Database e pode ser utilizado tanto em análises sob demanda quanto por mecanismos automáticos de SQL Tuning.

Documentação oficial da Oracle: Analyzing SQL with SQL Tuning Advisor.

O que é Oracle SQL Tuning Advisor?

O Oracle SQL Tuning Advisor é um mecanismo especializado para analisar SQLs que apresentam comportamento inadequado ou elevado consumo de recursos.

O administrador pode submeter uma instrução SQL ou um conjunto de instruções para análise. O Advisor avalia o SQL e apresenta findings, recomendações, justificativas e benefícios estimados.

Entre as principais recomendações que podem ser produzidas estão:

Coleta de estatísticas de objetos;
Criação de índices;
Reestruturação de instruções SQL;
Criação de SQL Profiles;
Criação ou utilização de SQL Plan Baselines;
Alternativas de plano de execução;
Recomendações relacionadas ao uso de paralelismo em determinados cenários.

A documentação oficial da Oracle apresenta o SQL Tuning Advisor como um recurso para obter recomendações de melhoria de desempenho de SQLs de alta carga. Oracle Database — Analyzing SQL with SQL Tuning Advisor

Por que utilizar o Oracle SQL Tuning Advisor?

Problemas de desempenho em Oracle Database frequentemente estão relacionados a SQLs específicos que consomem uma parcela significativa dos recursos disponíveis.

Uma consulta pode apresentar elevado consumo de CPU, grande quantidade de leituras lógicas, I/O físico, tempo de execução excessivo ou comportamento diferente daquele esperado pelo otimizador.

O SQL Tuning Advisor ajuda o DBA a transformar esses problemas em uma análise estruturada.

O objetivo não é simplesmente executar uma consulta mais rapidamente. O objetivo é encontrar uma solução tecnicamente adequada e sustentável para o workload.

Oracle SQL Tuning Advisor e SQLs de alto consumo

Um dos cenários mais importantes para utilização do SQL Tuning Advisor é a investigação de SQLs de alto consumo.

Esses SQLs podem ser identificados por ferramentas e recursos do próprio Oracle Database, incluindo:

Oracle AWR;
Oracle ASH;
Oracle ADDM;
Oracle Enterprise Manager;
Consultas na memória compartilhada;
SQL Tuning Sets;
Análises de incidentes de performance.

Depois de identificado o SQL_ID relevante, o DBA pode aprofundar a investigação e utilizar o SQL Tuning Advisor para avaliar oportunidades de otimização.

Veja também:

Oracle SQL Tuning Advisor e Oracle AWR

O Oracle AWR fornece informações históricas de desempenho que ajudam a identificar SQLs responsáveis por grande consumo de recursos.

Por meio das informações coletadas pelo AWR, o DBA pode analisar períodos específicos, identificar SQLs relevantes e comparar o comportamento do banco antes e depois de uma alteração.

Essa integração é importante porque o SQL Tuning Advisor não deve ser utilizado isoladamente. O contexto histórico do banco ajuda a determinar se o SQL realmente representa um problema e qual é o impacto para o ambiente.

Oracle SQL Tuning Advisor e Oracle ASH

O Oracle ASH permite aprofundar a investigação de sessões e SQLs ativos em determinados períodos.

Durante um incidente, o ASH pode ajudar a relacionar SQL_IDs, sessões, eventos de espera, consumo de CPU e outras informações importantes para determinar a origem de um problema de desempenho.

O SQL Tuning Advisor pode então ser utilizado como uma etapa adicional da investigação para analisar as instruções SQL consideradas críticas.

Oracle SQL Tuning Advisor e Oracle ADDM

O Oracle ADDM realiza análises automáticas do desempenho do banco e pode identificar SQLs de alta carga.

Quando um SQL relevante é identificado, o DBA pode utilizar o SQL Tuning Advisor para realizar uma análise específica da instrução.

A documentação oficial da Oracle descreve o processo de execução manual do SQL Tuning Advisor a partir de recomendações relacionadas a SQLs de alta carga.

Oracle — Tuning SQL Manually Using SQL Tuning Advisor

Como funciona o Oracle SQL Tuning Advisor?

O processo de tuning pode ser dividido em algumas etapas principais:

Identificação do SQL de alto impacto;
Obtenção do SQL_ID e contexto de execução;
Análise do plano de execução;
Criação de uma tarefa de tuning;
Execução do SQL Tuning Advisor;
Análise dos findings;
Avaliação das recomendações;
Teste da solução;
Implementação controlada;
Monitoramento posterior.

Essa metodologia reduz o risco de aplicar alterações de forma indiscriminada em ambientes de produção.

Oracle SQL Tuning Advisor: escopo Limited e Comprehensive

Na execução do SQL Tuning Advisor, o escopo da análise pode influenciar a profundidade e o tempo necessário para o diagnóstico.

Limited

O escopo limitado realiza uma análise mais rápida e não recomenda SQL Profile.

Comprehensive

O escopo Comprehensive realiza uma análise mais aprofundada e pode utilizar técnicas adicionais, incluindo amostragem e execução parcial, para melhorar a qualidade das estimativas.

Essa análise mais abrangente pode resultar em recomendações de SQL Profile quando forem identificadas diferenças significativas entre as estimativas do otimizador e os resultados observados.

Consulte a documentação oficial sobre os escopos de tuning do Oracle SQL Tuning Advisor.

Ambiente corporativo da Dominus Tech com análise de SQL em Oracle Database, plano de execução, SQL Tuning Advisor, indicadores de desempenho e recomendações de otimização. — Equipe técnica da Dominus Tech analisando consultas SQL, planos de execução, desempenho do banco de dados e recomendações de otimização.

Oracle SQL Tuning Advisor e SQL Profiles

Uma das recomendações mais importantes que o SQL Tuning Advisor pode apresentar é a criação de um SQL Profile.

Um SQL Profile contém informações auxiliares específicas de uma instrução SQL. Essas informações podem ajudar o otimizador a corrigir estimativas de cardinalidade, seletividade e custo que estejam levando a planos inadequados.

Um SQL Profile não é simplesmente um plano de execução armazenado. Ele fornece informações adicionais ao otimizador para que este possa escolher um plano mais adequado.

A Oracle explica que o SQL Profile pode corrigir estimativas do otimizador sem necessariamente prendê-lo a um único plano.

Oracle — Managing SQL Profiles

Quando o SQL Tuning Advisor recomenda um SQL Profile?

Durante uma análise abrangente, o SQL Tuning Advisor pode comparar estimativas do otimizador com informações obtidas durante a análise e identificar diferenças significativas.

Quando isso ocorre, o Advisor pode recomendar um SQL Profile para fornecer informações auxiliares ao otimizador.

O DBA deve avaliar a recomendação antes da implementação, principalmente em sistemas críticos.

Um SQL Profile pode ser particularmente útil quando o problema está relacionado a estimativas inadequadas que levam o otimizador a escolher um plano de execução menos eficiente.

Como aceitar um SQL Profile

A implementação de um SQL Profile recomendado pelo SQL Tuning Advisor pode ser realizada utilizando o pacote DBMS_SQLTUNE.

DECLARE
  l_sql_profile VARCHAR2(128);
BEGIN
  l_sql_profile := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
      task_name => 'SQL_TUNING_TASK',
      name      => 'SQL_PROFILE_EXEMPLO'
  );
END;
/

O nome da tarefa e os demais parâmetros devem ser substituídos pelos valores correspondentes ao ambiente.

A documentação oficial da Oracle apresenta o procedimento DBMS_SQLTUNE.ACCEPT_SQL_PROFILE para implementação de SQL Profiles.

Oracle — Implementing a SQL Profile

Oracle SQL Tuning Advisor e índices

O SQL Tuning Advisor pode recomendar a criação de índices quando identificar que um índice adicional pode melhorar a execução de determinada instrução.

Entretanto, uma recomendação de índice não deve ser aplicada automaticamente.

O DBA deve analisar:

Quantidade de dados;
Frequência de acesso;
Espaço utilizado;
Impacto em INSERT;
Impacto em UPDATE;
Impacto em DELETE;
Índices existentes;
SQLs que podem ser beneficiados;
Impacto sobre o workload geral.

Em ambientes corporativos, o melhor índice para uma consulta pode não ser necessariamente a melhor decisão para todo o banco.

Oracle SQL Tuning Advisor e reestruturação de SQL

O Advisor também pode identificar oportunidades de reestruturação da instrução SQL.

A análise pode apontar alternativas relacionadas à forma como a instrução é construída e como suas operações são processadas pelo otimizador.

Uma alteração de SQL deve ser validada funcionalmente e comparada em termos de desempenho antes de ser aplicada em produção.

Oracle SQL Tuning Advisor e SQL Plan Baselines

SQL Profiles e SQL Plan Baselines são mecanismos diferentes, embora ambos estejam relacionados ao controle e à melhoria do desempenho de SQL.

Um SQL Plan Baseline trabalha com planos aceitos e busca evitar que o otimizador utilize planos considerados inadequados.

Um SQL Profile, por sua vez, fornece informações auxiliares para melhorar as estimativas do otimizador.

A Oracle descreve SQL Plan Baselines como normalmente proativos e SQL Profiles como predominantemente reativos.

Oracle — Overview of SQL Plan Management

Oracle SQL Tuning Advisor e DBMS_SQLTUNE

O pacote DBMS_SQLTUNE é a interface utilizada para tuning SQL sob demanda.

Entre as funcionalidades relacionadas ao SQL Tuning Advisor estão:

Criação de tuning tasks;
Execução de tuning tasks;
Geração de relatórios;
Gerenciamento de SQL Profiles;
Gerenciamento de SQL Tuning Sets;
Operações de análise de SQL.

Um fluxo típico pode utilizar:

DBMS_SQLTUNE.CREATE_TUNING_TASK
DBMS_SQLTUNE.EXECUTE_TUNING_TASK
DBMS_SQLTUNE.REPORT_TUNING_TASK

Oracle — DBMS_SQLTUNE

Oracle SQL Tuning Advisor e SQL Tuning Sets

Um SQL Tuning Set permite trabalhar com um conjunto de instruções SQL e informações relacionadas ao contexto de execução.

Essa funcionalidade é especialmente útil quando o tuning envolve várias instruções SQL e não apenas uma consulta isolada.

Um SQL Tuning Set pode armazenar informações como:

Texto SQL;
Contexto de execução;
Estatísticas de execução;
Planos de execução;
Informações de processamento.

O SQL Tuning Advisor pode utilizar SQL Tuning Sets como entrada para análises de workloads.

Oracle — DBMS_SQLTUNE e SQL Tuning Sets

Oracle SQL Tuning Advisor automático

Além do tuning executado manualmente, o Oracle Database possui mecanismos de SQL Tuning automático.

O pacote DBMS_AUTO_SQLTUNE fornece a interface relacionada ao SQL Tuning Advisor quando executado pelo framework de tarefas automáticas.

A tarefa automática pode selecionar SQLs de alta carga a partir das informações do AWR e executar uma análise abrangente.

Oracle — DBMS_AUTO_SQLTUNE

Oracle SQL Tuning Advisor e Oracle Enterprise Manager

O Oracle Enterprise Manager fornece recursos gráficos para monitoramento e administração do Oracle Database.

O DBA pode utilizar os recursos de performance para identificar SQLs relevantes e iniciar análises de tuning.

A documentação oficial da Oracle apresenta o processo para executar o SQL Tuning Advisor manualmente por meio das ferramentas de gerenciamento.

Oracle — Tuning SQL Manually Using SQL Tuning Advisor

Oracle SQL Tuning Advisor e plano de execução

O plano de execução é uma das principais informações utilizadas pelo DBA para entender como o Oracle está processando uma instrução SQL.

Durante o processo de tuning, é importante comparar o plano atual com alternativas identificadas pelo Advisor.

Entre os aspectos que podem ser avaliados estão:

Acesso por índice;
Full Table Scan;
Join methods;
Cardinalidade estimada;
Cardinalidade real;
Operações de sort;
Leituras lógicas;
Leituras físicas;
Tempo de CPU;
Tempo decorrido.

SQL Tuning Advisor e regressão de performance

Uma alteração que melhora uma consulta específica pode eventualmente produzir efeitos negativos sobre outras partes do workload.

Por isso, o tuning deve ser acompanhado de testes e validação.

Em ambientes críticos, o DBA deve considerar mecanismos de gerenciamento de planos para aumentar a previsibilidade do comportamento do otimizador.

Oracle — SQL Plan Management

Metodologia de Oracle SQL Tuning para ambientes corporativos

Uma metodologia profissional de tuning pode seguir o seguinte fluxo:

Identificar o problema de desempenho;
Determinar quando o problema ocorre;
Analisar Oracle AWR;
Analisar Oracle ASH;
Verificar Oracle ADDM;
Identificar SQL_IDs críticos;
Verificar o plano de execução;
Executar o SQL Tuning Advisor;
Analisar os findings;
Avaliar as recomendações;
Testar a solução;
Implementar a alteração;
Validar os resultados;
Monitorar possíveis regressões.

Essa abordagem permite que o SQL Tuning Advisor seja utilizado como parte de uma estratégia maior de administração e performance do Oracle Database.

Fluxo de performance Oracle com AWR, ASH e ADDM identificando SQLs críticos, análise do SQL Tuning Advisor, implementação controlada e validação de resultados da Dominus Tech. — Fluxo técnico da Dominus Tech para diagnóstico e otimização de performance Oracle, desde AWR, ASH e ADDM até SQL Tuning Advisor, implementação controlada e validação dos resultados.

Oracle SQL Tuning Advisor em ambientes críticos

Em bancos de dados corporativos, o tuning deve considerar não somente o ganho de uma instrução SQL, mas também o impacto sobre a aplicação, o workload e a infraestrutura.

Antes de implementar uma recomendação, é importante verificar:

Benefício esperado;
Impacto sobre CPU;
Impacto sobre I/O;
Impacto sobre memória;
Impacto sobre outras consultas;
Janela de mudança;
Possibilidade de rollback;
Comportamento após a alteração.

Essa abordagem é especialmente importante em ambientes Oracle RAC, Oracle Exadata e bancos de dados que suportam aplicações críticas.

Veja também:

Oracle SQL Tuning Advisor e serviços de DBA

O SQL Tuning Advisor pode fazer parte de uma estratégia profissional de serviços de DBA Oracle, principalmente em ambientes que precisam de acompanhamento contínuo de performance.

Entre os serviços relacionados estão:

Oracle Performance Tuning;
SQL Tuning;
Diagnóstico de problemas de performance;
Monitoramento de banco de dados;
Análise de AWR;
Análise de ASH;
Análise de ADDM;
Otimização de SQL;
Revisão de planos de execução;
Consultoria Oracle.

Conheça também:

Conteúdos relacionados ao Oracle SQL Tuning Advisor

Performance Oracle

Administração Oracle

Arquitetura Oracle

Conclusão

O Oracle SQL Tuning Advisor é uma ferramenta importante para análise e otimização de instruções SQL no Oracle Database.

Sua utilização permite que o DBA investigue SQLs de alto impacto e avalie recomendações relacionadas a estatísticas, índices, reestruturação de SQL, SQL Profiles e SQL Plan Baselines.

Quando integrado ao Oracle AWR, Oracle ASH, Oracle ADDM e Oracle Enterprise Manager, o SQL Tuning Advisor passa a fazer parte de uma metodologia mais completa de diagnóstico e otimização.

Em ambientes corporativos, a recomendação não deve ser aplicada de forma automática. O DBA deve avaliar o contexto, testar a alteração, medir os resultados e acompanhar o ambiente após a implementação.

Para organizações que dependem do Oracle Database em aplicações críticas, o SQL Tuning Advisor pode contribuir para melhorar desempenho, reduzir consumo de recursos e aumentar a previsibilidade do ambiente.

Documentação oficial Oracle

A Dominus Tech ajuda empresas a reduzir custos de licenciamento Oracle Database por meio de Performance Tuning, análise de utilização, otimização de recursos, observabilidade Full Stack e monitoramento inteligente da infraestrutura de TI.

A proteção dos dados corporativos exige planejamento, experiência e conhecimento das melhores práticas da Oracle.

A Dominus Tech oferece serviços especializados para implantação, revisão e evolução da segurança em ambientes Oracle Database, incluindo assessment, hardening, criptografia, auditoria, monitoramento, alta disponibilidade e recuperação de desastres.

👉Entre em contato com nossa equipe para descobrir como fortalecer a segurança do seu ambiente Oracle Database e reduzir os riscos para o seu negócio.