Questões de Concurso sobre Técnicas para otimização de consultas SQL

SQL Tuning

Power Tuning.

Distance Tuning

Performance Planning

Otimizadores baseados em Regras (Rule-Based Optimizer) são superiores aos baseados em Custo (Cost-Based Optimizer) porque ignoram o número de linhas da tabela, focando apenas na ordem alfabética das colunas nas Chaves Primárias.

Uma consulta é considerada SARGable (Capaz de ser um Argumento de Busca) quando o otimizador consegue utilizar índices para filtrar os dados, evitando o uso de funções ou transformações sobre a coluna no lado esquerdo do predicado.

O uso do operador LIKE (Como) com o caractere curinga no início do termo de busca (ex: '%termo') é uma prática recomendada para garantir a utilização de Índices B-Tree (Árvores B) em tabelas com milhões de registros.

A Intersecção de Índices (Index Intersection) ocorre exclusivamente quando o SGBD (Sistema Gerenciador de Banco de Dados) identifica que dois usuários tentam atualizar a mesma linha simultaneamente, causando uma falha de Segmentação.

2.800

3.000

3.200

6.000

1.500

aumentar a quantidade de memória RAM alocada para o buffer pool, o que melhorará o cache hit ratio, mas não o plano de execução da consulta.

criar um índice Non-Clustered na coluna DATA_OCORRENCIA, o que permitirá ao otimizador utilizar a busca por índice em vez da varredura completa.

alterar o tipo de dado da coluna DATA_OCORRENCIA para VARCHAR, o que facilita as operações de string.

forçar o uso de um Merge Join em vez de um Nested Loop Join, pois o Merge Join é sempre mais rápido.

desativar todas as Stored Procedures relacionadas ao relatório, pois elas introduzem latência no processamento.

A utilização de SELECT * em todas as consultas para simplificar o código.

A remoção de todas as chaves primárias e estrangeiras para reduzir a sobrecarga.

A desnormalização completa do banco de dados para evitar operações de JOIN.

A execução de consultas complexas durante o horário de pico de utilização do sistema.

A criação e manutenção de índices adequados nas colunas utilizadas em cláusulas WHERE e JOIN.

Utilizar as visões dinâmicas de desempenho (V$ views) para coletar estatísticas do sistema em tempo real, como V$SESSION WAIT e V$SQLAREA, analisar os planos de execução de consultas com o EXPLAIN PLAN ou o AUTOTRACE, e verificar a ocorrência de bloqueios em sessões com o DBA LOCKS.

Focar a análise nas tabelas e no espaço em disco livre (tablespaces), pois a degradação de desempenho é causada, na maioria dos casos, pela falta de espaço. A otimização seria feita simplesmente adicionando mais arquivos de dados (datafiles) para garantir que não ocorram erros de out of space.

Consultar o Oracle Enterprise Manager (OEM) para uma visão genérica do ambiente, identificar o SQL mais lento com base em elapsed time e, em seguida, forçar o otimizador a usar um índice específico, sem necessidade de avaliar a distribuição dos dados ou a validade do plano de execução.

Desabilitar as estatísticas do otimizador de consultas, pois elas consomem recursos do sistema, e confiar na indexação manual de todas as colunas de todas as tabelas para garantir um acesso mais rápido e eficiente aos dados.

Reiniciar o banco de dados para liberar a memória e resolver a degradação de desempenho, o que é a maneira mais simples e rápida de restaurar a performance do sistema. As ferramentas de monitoramento seriam usadas apenas após a reinicialização para identificar se a performance foi restaurada.

Certo

Errado

V$SESSION ou sys.dm_exec_sessions

V$SQL ou sys.dm_exec_query_stats

V$LOCK ou sys.dm_tran_locks

V$SYSSTAT ou sys.dm_os_performance_counters

V$SESSION_WAIT ou sys.dm_os_wait_stats

SELECT id_aluno, nome

FROM Alunos

WHERE num_reprovacoes < 5

AND aproveitamento_geral >= 6.0

AND tipo_bolsa IS NOT NULL

AND tipo_ingresso = 'ampla';

SELECT id_aluno, nome, num_reprovacoes,

aproveitamento_geral, tipo_bolsa, tipo_ingresso

FROM Alunos

WHERE num_reprovacoes > 5

AND aproveitamento_geral < 6.0

AND tipo_bolsa IS NULL

AND tipo_ingresso = 'cota';

SELECT id_aluno, nome

FROM Alunos

WHERE num_reprovacoes > 5

AND aproveitamento_geral > 6.0

AND tipo_bolsa IS NULL

AND tipo_ingresso = 'cota';

SELECT *

FROM Alunos

WHERE tipo_bolsa IS NOT NULL

AND tipo_ingresso = 'cota';

SELECT id_aluno, nome, num_reprovacoes,

aproveitamento_geral

FROM Alunos

WHERE num_reprovacoes > 5

AND aproveitamento_geral < 6.0;

Normalizar ainda mais as tabelas envolvidas.

Utilizar EXPLAIN para verificar o plano de execução.

Avaliar estatísticas e atualizá-las com ANALYZE.

Criação de índices nos campos usados em JOIN e WHERE.

Reescrever a consulta evitando subconsultas desnecessárias.

Criar índice composto alinhado à seletividade do predicado e à ordenação desejada, avaliar estatísticas atualizadas e revisar plano por custo real em execução.

Habilitar dicas para forçar nested-loop em qualquer cenário, priorizando paralelismo por quantidade de threads e buffers grandes.

Eliminar índices existentes e focar varredura completa por tabela, confiando em cache sempre residente e picos previsíveis de E/S.

Fixar hash join para cada junção, criar índices separados por coluna e aceitar operação de ordenação posterior como etapa natural.

Resolver ordens por criação de índice exclusivo para cada coluna de ordenação, manter estatísticas padrão e restringir reescrita de consultas.

SELECT PedidoID, SUM(Quantidade * PrecoUnit) AS Total FROM PedidoItem.

SELECT PedidoID, SUM(Quantidade) * SUM(PrecoUnit) AS Total FROM PedidoItem GROUP BY PedidoID.

SELECT PedidoID, SUM(Quantidade * PrecoUnit) AS Total FROM PedidoItem GROUP BY PedidoID.

SELECT PedidoID, Quantidade * PrecoUnit AS Total FROM PedidoItem GROUP BY PedidoID.

SELECT PedidoID, SUM(Quantidade) * AVG(PrecoUnit) AS Total FROM PedidoItem GROUP BY PedidoID.

F − V − F − V.

F − F − V − F.

V − V − F − F.

F − V − V − F.

V − F − V − V.

DEALLOCATE ...

SET ROLE ...

CLUSTER ...

SET enable_partition_pruning = on;

SET CONSTRAINTS ...

criar índices para todas as colunas que sejam usadas em qualquer tipo de consulta.

desconsiderar as formas normais e manter os dados em um único JSON, o que aumentaria a velocidade das operações.

normalizar todas as tabelas até 5FN.

permitir desnormalizações controladas, como colunas duplicadas, por exemplo, para obtenção de ganho de desempenho.

usar somente bancos de dados que sejam mais rápidos para leitura, como o NoSQL.

A cláusula DISTINCT é puramente lógica e não impacta desempenho.

O otimizador sempre usa índices quando eles existem.

O desempenho de uma consulta é independente da forma como ela é escrita, já que o otimizador sempre gera o mesmo plano de execução.

Indices não têm impacto no desempenho de consultas.

Examinar o plano de execução com os comandos do SGBD é uma prática recomendada para diagnosticar consultas com baixo desempenho sem alterar o resultado lógico.

É correta apenas a afirmativa 2.

São corretas apenas as afirmativas 1 e 2.

São corretas apenas as afirmativas 1 e 3.

São corretas apenas as afirmativas 2 e 3.

São corretas as afirmativas 1, 2 e 3.

Desativar o parameter sniffing globalmente para todo o banco de dados.

Adotar recompilação seletiva (OPTION (RECOMPILE)) apenas em procedimentos críticos.

Forçar a utilização de hints de paralelismo em todas as queries.

Converter todas as Stored Procedures em views indexadas para reduzir variabilidade.

Usar cursores estáticos para controlar o cache de execução.

Criar uma indexação no campo status para acelerar a filtragem das solicitações concluídas.

Substituir a consulta por uma visão materializada, armazenando os resultados pré-calculados para reduzir a carga computacional da consulta.

Utilizar o nível de isolamento SERIALIZABLE, garantindo que cada transação leia os dados sem interferência de outras, mesmo que isso reduza a concorrência.

Implementar um bloqueio exclusivo (LOCK) na tabela Solicitações durante a execução da consulta para evitar inconsistências, impedindo leituras concorrentes.