Vejo-me escrevendo cada vez mais consultas SQL no trabalho (principalmente Oracle 11g, mas algumas SQL Server 2005-2008) e comecei a criar algumas visualizações bastante complexas para o restante da equipe de analistas.
Quase todos eles funcionam muito bem, mas alguns deles não tão bem. Então...
- Como aprendo a sintonizar minhas consultas?
- Preciso aprender a ler/agir sobre Planos de Execução?
E...
- Quais livros/sites você pode recomendar para aprender sobre ajuste de consulta SQL 1) em geral 2) especificamente para Oracle 11g?
Temos alguns bons DBAs aqui, mas eles estão sobrecarregados demais para nos ajudar a ajustar cada consulta que escrevemos.
A maioria dos livros que encontrei na Amazon para Oracle parecem ser voltados para a otimização geral do banco de dados e/ou foram escritos de 8 a 10 anos atrás.
Obrigado gentilmente por seu conselho :)
Eu diria que aprender a entender os planos de explicação é uma habilidade vital para ajudá-lo a otimizar as instruções SQL. Achei o livro de Christian Antognini, Troubleshooting Oracle Performance , muito útil para detalhar como eles funcionam, além de explicar como abordar a otimização do banco de dados. Com alguns anos de idade, você ainda aprenderá muito que ainda é relevante com ele.
Se você for mais avançado, poderá consultar os livros de Jonathan Lewis, mas eles são mais aprofundados, portanto provavelmente não são um bom ponto de partida. O Oracle Fundamentals baseado em custo é bastante antigo agora, mas muito ainda é relevante. Ainda não li o Oracle Core: Essential Internals for Troubleshooting , mas recebi boas críticas da comunidade Oracle.
Como você está no 11g, se tiver consultas que demoram mais do que alguns segundos, eu definitivamente recomendo olhar para o monitor SQL em tempo real (supondo que você esteja devidamente licenciado). Como o nome sugere, ele mostra o progresso de uma instrução SQL em tempo real, detalhando quanto tempo cada operação levou com detalhes das linhas buscadas até o momento. Ele também mantém os detalhes das consultas executadas recentemente por um curto período, para que você possa ver como suas alterações afetam uma instrução.
Documentação do Oracle SQL Monitoring: http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94543
Aprender a ajustar as consultas é algo que requer tempo e prática. Algumas coisas que aprendi:
Principalmente, você aprenderá escrevendo consultas, observando os planos de explicação (esperados) e comparando-os com os planos de execução reais (por meio do rastreamento da consulta ou usando o monitor SQL). Em seguida, reescreva a consulta, adicione/remova índices etc. e veja como isso afeta os planos e os tempos de execução
Como você está procurando informações específicas da Oracle, recomendo o blog Ask Tom na Oracle. Em geral, acho que você descobrirá que o conselho é não ajustar a consulta. Você receberá bons conselhos sobre como escrever uma consulta que o otimizador pode otimizar. A documentação do Oracle também está online , e geralmente procuro informações atualizadas sobre o Oracle. Eu não trabalhei com SQLServer, então não tenho nenhuma recomendação para isso.
Não tenho visto muitas novidades no campo de otimização de consultas nos últimos anos. A grande mudança é a depreciação do otimizador baseado em regras, com o qual mal me lembro de ter trabalhado. No entanto, entendo que o SQLServer ainda usa um otimizador baseado em regras, portanto, entender suas regras pode ajudar.
Uma ferramenta na qual você pode editar uma consulta, executá-la e gerar um plano de explicação ajuda a entender quais alterações levam a uma consulta com bom desempenho. Tive bons resultados com o AquaData Studio e gosto muito de sua visualização em árvore. O SQL Developer também deve fazer isso.
Como em qualquer otimização, você precisa ter dados quantitativos sobre seu desempenho. Então você pode determinar se realmente o otimizou.
Como otimizar uma consulta depende em parte de como o analisador constrói e otimiza a consulta. Em grande medida, depende da distribuição dos dados que você está consultando. Em um banco de dados Oracle, se o conjunto de resultados representar quatro por cento ou mais de uma tabela e for distribuído aleatoriamente, uma varredura de tabela geralmente é mais rápida que um índice.
Trabalhei para otimizar consultas para uma equipe de desenvolvedores. Apenas duas ou três consultas por ano exigiam uma otimização séria. A maioria das consultas é simples o suficiente para não precisar de otimização. O restante geralmente pode ser tratado adicionando caminhos de junção ausentes.
Para Oracle, há três configurações ajustáveis que podem afetar significativamente o desempenho. O custo para pesquisas de índice e dados interagem para alterar as condições sob as quais um índice in será ou não usado. Esses dois podem ser ajustados por sessão. Os padrões geralmente não são ideais. O outro valor controla quantas alternativas o otimizador tentará. Aumentar esse valor geralmente ajuda.
A otimização é significativamente afetada pela distribuição e volume de dados. Ao otimizá-lo, é melhor usar uma cópia do banco de dados de produção ou, pelo menos, um banco de dados com a mesma distribuição e volumes de dados. Eu quebrei severamente o ambiente de teste, otimizando uma consulta ao banco de dados de ordens de produção. Os bancos de dados de teste e desenvolvimento tinham uma distribuição de dados significativamente diferente, o que fazia com que a consulta falhasse mesmo com menos dados.