Ajuste de instruções SQL (Select, Insert, Update e Delete) pela técnica de Reescrita de Consultas
Com a freqüente implementação de novas aplicações que fazem acesso ao banco de dados, uma preocupação constante da equipe de suporte é como manter o acesso aos dados com desempenho. Esse artigo trata de uma das melhores técnicas existentes para alcançar desempenho, chamada Reescrita de Consultas.
A justificativa
O principal motivo para que seja aplicada essa técnica é que as consultas SQL representam uma das principais origens de perda de desempenho. De acordo com uma pesquisa conduzida pela Oracle Corporation, tipicamente um DBA (Database Administrator) consome cerca de 55% de seu tempo realizando atividades de tuning. A figura 1 ilustra a distribuição de tempo por atividade de um típico DBA.
O monitoramento e ajustes de consultas SQL é a atividade que consome maior tempo do DBA por sua complexidade e por representar a maior parte dos acessos realizados no SGBDR, sendo que muitas não alcançam o desempenho esperado, devido a terem sido escritas pensando-se no resultado a serem obtidos, e não no melhor caminho para obtê-los. Fatores como falta de experiência em desenvolvimento, baixo nível de conhecimento técnico, prazos de entregas subdimensionados e falta de monitoramento individual contribuem para que as consultas sejam ineficientes, o que determina a análise freqüente das consultas.
O otimizador constrói o plano de execução a partir da consulta SQL, tendo uma margem limitada de opções sobre os operadores utilizados. Uma consulta SQL mal escrita leva o otimizador a utilizar um caminho que nem sempre é o mais adequado, o que gera um plano de execução que normalmente compromete o desempenho. Em ambientes nos quais existem inúmeras consultas construídas dessa forma e que são executadas freqüentemente, a conseqüência acaba sendo drástica, dificultando o uso eficiente da maior parte dos recursos disponíveis e deixando de atender em tempo outros processos críticos cujas execuções são rápidas e necessitam de prioridade.
Assim sendo, existem inúmeras técnicas utilizadas pelos grandes mestres para se alcançar desempenho. A justificativa para se usar a técnica de reescrita de consultas, que na opinião de muitos deles é a 1ª em termos de menor impacto, é simples : fazer com que o ajuste apenas afete a consulta específica, não se propagando para outras aplicações que acessam as tabelas envolvidas na instrução SQL. Um bom exemplo de propagação global de ajustes é a famosa “criação de índices”, que após ser criado, está disponível para ser usado por todas as aplicações que fazem uso da tabela ajustada. O impacto nesse caso pode ser positivo como também negativo (o que ocorre em muitas situações).
Como fazer?
De forma bem simplificada, os principais critérios adotados são:
- Monitorar as sessões ativas que estão sendo executadas no banco de dados;
- Separar as consultas que estão “agarrando”, ou seja, com execuções demoradas;
- Dividi-las em grupos, como : criticidade, freqüência de execução e fraco desempenho;
- Implementar os ajustes – reescrevendo a consulta que está sem desempenho por outra, porém com desempenho.
Cada banco de dados fornece suas ferramentas específicas para capturar as consultas citadas nos itens acima, porém a maneira mais eficiente e explícita é o próprio uso feito pelo usuário final. Quando existem muitos chamados de “lentidão” no ambiente, grandes indícios de que algo não está 100%.
- Uso de função sobre coluna indexada;
- Subconsulta mal formulada;
- Uso errôneo de agrupamento e predicado;
- Uso indevido de operadores de conjunto;
- Incompatibilidade de tipos de dados.
Como exemplo do item 1, temos a seguinte consulta solicitada pelo usuário: Exiba as filiais, o nome do produto, seu preço unitário, a conta de despesa, a conta de venda, a unidade de medida e o status do produto (Ativo e Inativo) cuja descrição é "GLP ENVASADO 13 KGS". A consulta original é descrita abaixo:
E seu respectivo plano de execução:
O principal problema que ocorre é que a coluna description da tabela MTL_SYSTEM_ITEMS_B é argumento da função UPPER, que converte todos os conteúdos do tipo caracter em letras maiúsculas. Este tipo de conversão faz com que o otimizador não utilize o índice DBA_MTL_SYSTEM_B_011 composto apenas pela coluna description, que é bem mais seletivo que o índice MTL_SYSTEM_ITENS_B_N2.
Devido ao desvio na utilização do índice mais seletivo, o otimizador utiliza o método de acesso Loop aninhado em ao invés de Hash Join. Os valores das colunas cost (5362), disk (3021), cpu (2,33) e elapsed (4,68) indicam indicam que este método de acesso pode comprometer o desempenho da consulta. (e que está ocorrendo na verdade)
Como a coluna description do produto pode conter letras maiúsculas e minúsculas e a restrição solicitada na cláusula WHERE é sempre maiúscula, a solução é evitar o uso da função UPPER na coluna indexada, substituindo-a pelo operador OR, aceitando os valores de comparação tanto para minúsculas como em maiúsculas. Assim não há mais a necessidade de se realizar a conversão sobre a coluna indexada, e o índice DBA_MTL_SYSTEM_B_011 é utilizado pelo otimizador, fornecendo o mesmo resultado de dados da consulta original, porém diferente em termos de plano e tempo de execução.
Veja a consulta reescrita:
E o seu novo plano de execução:
A melhora no tempo foi significativa na execução da consulta reescrita, obtendo-se um ganho de desempenho sobre a consulta original a partir da métrica elapsed.
Este artigo apresentou uma técnica de reescrita de consultas que melhora a eficiência em termos de tempo de execução: O uso de função sobre coluna indexada. No próximo artigo as demais técnicas mencionadas serão detalhadas.
