Identificando Problemas – Queries lentas – Parte 04

Publicado em por

Introdução

Continuando o assunto de identificação de problemas, vamos ver agora o que e como lidar com queries que não apresentam um bom desempenho. Antes de chamar um DBA, existem alguns procedimentos investigativos e algumas ações que ajudam a resolver uma boa parte destas ocorrências.

Queries, de onde vêm?

Quando utilizamos um Banco de Dados relacional homologado com o ERP Microsiga / Protheus, todas as requisições de acesso a dados passam pela aplicação DBAccess. Como eu havia mencionado em um post anterior, o DBAccess serve de “gateway” de acesso ao Banco de Dados, e serve para emular as instruções ISAM do AdvPL em um banco relacional.

Logo, as queries submetidas pelo DBAccess ao Banco de Dados em uso podem ser de de dois tipos:

  • Queries emitidas (geradas) pelo DBAccess para atender a uma requisição ISAM — DBGoTop(), DBGoBottom(), DbSkip(), DBSeek().
  • Queries abertas pela aplicação AdvPL para recuperar dados diretamente do Banco de Dados.

Quando utilizamos o DBAccess Monitor, para realizar um Trace de uma conexão do Protheus com o DBAccess, podemos visualizar as operações solicitadas do Protheus ao DBAccess, e as respectivas queries submetidas ao Banco de Dados.

Como identificar uma query “lenta”?

Normalmente encontramos uma ou mais queries com baixo desempenho quando estamos procurando a causa da demora de um determinado processo. A descoberta acaba sendo realizada durante a análise de um Log Profiler obtido durante a execução da rotina, ou também através de um trace da conexão do DBAccess usando o DBMonitor.

Agora, podemos também usar o próprio DBAccess para gerar um log das queries que demoraram mais para serem abertas pelo Banco de Dados. Basta utilizar a configuração MaxOperationTimer, onde podemos especificar um número de segundos limite, a partir do qual um log de advertência deve ser gerado pelo DBAccess, caso o retorno da abertura de uma Query ultrapasse o tempo definido.

O que é uma query lenta?

Definir lentidão normalmente não está ligado apenas a medida absoluta de tempo, mas sim é relativa a urgência ou necessidade de obter a informação rapidamente, versus a quantidade de informações a serem avaliadas e retornadas.

Por exemplo, quando a aplicação AdvPL executa uma instrução como DBGoto(N), onde N é o número de um registro da base de dados, o DBAccess vai montar e submeter uma Query contra o banco de dados, para selecionar todas as colunas da tabela em questão, onde o campo R_E_C_N_O_ é igual ao número N informado.

SELECT CPO1,CPO2,CPO3,...N FROM TABELA WHERE R_E_C_N_O_ = N

Meu chapa, essa Query deve rodar normalmente em menos de 1 milissegundo no banco de dados, por duas razões: A primeira é que a coluna R_E_C_N_O_ é a chave primária (Primary Key) de todas as tabelas criadas pelo DBAccess, então naturalmente existe um índice no Banco de Dados usando internamente para achar em qual posição do Banco estão gravadas as colunas correspondentes a esta linha. E a segunda é que, apenas uma linha será retornada.

Se não existisse um índice para a coluna R_E_C_N_O_, o Banco de Dados teria que sair lento a tabela inteira, sequencialmente, até encontrar a linha da tabela que atendesse esta condição de busca. Esta busca na tabela de dados inteira sem uso de índice é conhecida pelo termo “FULL TABLE SCAN”.

Agora, imagine um SELECT com UNION de mais quatro selects, finalizado com um UNION ALL, onde cada Query faz SUB-SELECTS e JOINS no Banco de Dados, e o resultado disso não vai ser pequeno … Mesmo em condições ideais de configuração do Banco de Dados, não é plausível exigir que uma operação deste tamanho seja apenas alguns segundos.

Causas mais comuns de degradação de desempenho em Queries

Entre as mais comuns, podemos mencionar:

  1. Ausência de um ou mais índices — simples ou compostos — no Banco de Dados, que favoreçam um plano de execução otimizado do Banco de Dados para recuperar as informações desejadas.
  2. Estatísticas do Banco de Dados desatualizadas.
  3. Picos de CPU , Disco ou Rede, na máquina onde está o DBAccess e/ou na máquina onde está o Banco de Dados.
  4. Problemas de hardware na máquina do Banco de Dados ou em algum dos componentes da infra-estrutura.
  5. Problemas de configuração ou de comportamento do próprio Banco de Dados sob determinadas condições.
  6. Excesso de operações ou etapas do plano de execução, relacionadas a complexidade da Query ou da forma que a Query foi escrita para chegar ao resultado esperado.

Recomendo adicionalmente uma pesquisa sobre “Full Table Scan” e outras causas possíveis de baixo desempenho em Queries. Quanto mais infirmação, melhor. E, a propósito, mesmo que a tabela não tenha um índice adequado para a busca, se ela for  realmente pequena (poucas linhas) , o Banco de Dados internamente acaba fazendo CACHE da tabela inteira em, memória, então um Full Table Scan acaba sendo muito rápido, quando a tabela é pequena. Esta é mais uma razão por que muitas ocorrências de desempenho relacionados a este evento somente são descobertas após a massa de dados crescer representativamente no ambiente.

Troubleshooting e boas práticas

Normalmente as melhores ferramentas que podem dar pistas sobre as causas do baixo desempenho de uma Query são ferramentas nativas ou ligadas diretamente ao Banco de Dados, onde a ferramenta é capaz de desenhar e retornar — algumas inclusive em modo gráfico — o PLANO DE EXECUÇÃO da Query. Neste plano normalmente as ferramentas de diagnóstico informam quando está havendo FULL TABLE SCAN, e quais são as partes da Query que consomem mais recursos no  plano de execução. Algumas destas ferramentas inclusive são capazes de sugerir a criação de um ou mais índices para optimizar a busca dos dados desejados.

Mesmo sem ter uma ferramenta destas nas mãos, normalmente necessária para analisar queries grandes e mais complexas, podemos avaliar alguns pontos em queries menores “apenas olhando”, por exemplo:

  1. Ao estabelecer os critérios de busca — condições e comparações usadas na cláusula WHERE — procure usar diretamente os nomes dos campos, comparando com um conteúdo fixo,  evitando o uso de funções. É clado, vão existir exceções, mas via de regra procure estar atento neste ponto.
  2. Evite concatenações de campos nas expressões condicionais de busca. Imagine que você tem uma tabela com dois campos, e você tem nas mãos, para fazer a busca, uma string correspondendo a concatenação destes dois valores. Muto prático você fazer SELECT X FROM TABELA WHERE CPO1 || CPO2 = ‘0000010100’, certo ? Sim, mas mesmo que você tenha um índice com os campos CPO1 e CPO2, o Banco de Dados não vai conseguir usar o índice para ajudar nesta Query — e corre o risco de fazer FULLSCAN. Agora, se ao inves disso, você quebrar a informação para as duas colunas, e escrever SELECT X FROM TABELA WHERE CPO1 = ‘000001’ AND CPO2  = ‘01000’ , o Banco de Dados vai descobrir durante a montagem do plano de execução que ele pode usar um índice para estas buscas, e vai selecionar as linhas que atendem esta condição rapidinho.
  3. O Banco de Dados vai analisar a sua Query, e tentar criar um plano de acesso (ou plano de execução) para recuperar as informações desejadas o mais rápido possível. Se todas as condições usadas na cláusula WHERE forem atendidas por um mesmo índice, você ajuda o Banco de Dados a tomar a decisão mais rapidamente de qual índice internamente usar, se você fizer as comparações com os campos na ordem de declaração do índice. Por exemplo, para o índice CPO1, CPO2, CPO3, eu sugiro  uma Query com SELECT XYZ from TABELA WHERE CPO1 = ‘X’ AND CPO2 = ‘Y’ AND CPOC3 >=  ‘Z’

Queries emitidas pelo DBAccess

As queries emitidas pelo DBAccess no Banco de Dados para emular o comportamento de navegação ISAM são por natureza optimizadas. Para emular a navegação de dados em uma determinada ordem de índice, o DBAccess emite queries para preencher um cache de registros — não de dados — usando os dados dos campos como condições de busca, na mesma sequência da criação do índice. E, para recuperar o conteúdo (colunas) de um registro (linha), ele usa um statement preparado, onde o Banco faz o parser da requisição apenas na primeira chamada, e as demais usam o mesmo plano de execução.

Porém, isto não impede de uma ou mais queries emitidas pelo DBAccess acabem ficando lentas. Normalmente isso acontece quando é realizada uma condição de filtro na tabela em AdvPL, onde nem todos — ou nenhum —  os campos utilizados não possuem um índice que favoreça uma busca indexada, fazendo com que o Banco acabe varrendo a tabela inteira — FULL SCAN — para recuperar os dados desejados.

Este tipo de ocorrência também é solúvel, uma vez determinado qual seria a chave de índice que tornaria a navegação com esta condição de filtro optimizada, é possível de duas formas criar este índice.

Criando um índice auxiliar

A forma recomendada de se criar um índice auxiliar é acrescentá-lo via Configurador no arquivo de índices do ERP (SIX), para que ele seja criado e mantido pelas rotinas padrão do sistema. Porém, para isso este índice não pode conter campos de controle do DBAccess no meio das colunas do índice, e para se adequar ao padrão do ERP, seu primeiro campo deveria sempre ser o campo XX_FILIAL da tabela.

Quando esta alternativa não for possível, existe a possibilidade de criar este índice diretamente no Banco de Dados. Porém, a existência desse índice não deve interferir na identificação de índices de emulação ISAM que o DBAccess faz quando qualquer tabela é aberta. Para isso, a escolha do NOME DO ÍNDICE é fundamental.

Um índice criado diretamente no Banco de Dados para estes casos deve ter um nome que seja alfabeticamente POSTERIOR aos índices declarados no dicionário de dados (SIX). Por exemplo, existe uma tabela ABC990, que possui 8 índices. O ERP Microsiga nomeia os índices da tabela no padrão usando o nome da tabela e mais um número ou letra, em ordem alfabética. Logo, os oito primeiros indices da tabela chamam-se, respectivamente, ABC9901, ABC9902 … ABC9908.

Nomeie seu primeiro índice customizado com o nome de ABC990Z1. Caso seja necessário mais um índice, ABC990Z2, e assim sucessivamente. Chegou no 9, precisa de mais um índice, coloque a letra “A” — ABC990ZA. Dessa forma, estes índices ficarão por último na identificação do DBAccess, e por eles provavelmente não se encaixarem no padrão do ERP, você não vai — e não deve — usá-los dentro de fontes customizados AdvPL — Estes índices vão existir apenas no Banco de Dados, para favorecer a execução de queries especificas ou filtros específicos de navegação.

Precauções

Uma vez que um ou mais índices sejam criados dentro do Banco de Dados, sem usar o dicionário ativo de dados (SIX), qualquer alteração estrutural na tabela feita por uma atualização de versão ou outro programa pode apagar estes índices a qualquer momento, caso seja necessário, e isso vai novamente impactar o desempenho da aplicação. Para evitar isto, é possível escrever uma rotina em AdvPL — customização — para verificar usando Queries no banco de dados se os índices auxiliares criados por fora ainda existem, e até recriá-los se for o caso. Pode ser usado para isso o Ponto de Entrada CHKFILE() , junto das funções TCCanOpen() — para testar a existência do índice — e TcSqlExec() — para criar o índice customizado usando uma instrução SQL diretamente no banco de dados.

Outras causas de degradação de desempenho

Existem ocorrências específicas, que podem estar relacionadas desde a configuração do Banco de Dados, até mesmo problemas no mecanismo de montagem ou cache dos planos de acesso criados pelo Banco para resolver as Queries. Outas ocorrências podem estar relacionadas a execução de rotinas automáticas ou agendadas no próprio servidor de Banco de Dados. Normalmente o efeito destas interferências são percebidos como uma lentidão momentânea porém generalizada, por um período determinado de tempo.

Conclusão

Use a tecnologia ao seu favor, e em caso de pânico, chame um DBA! Muitas vezes existe uma forma mais simples ou com menos etapas para trazer as informações desejadas. Outras vezes é mais elegante, rápido e prático escrever mais de uma query menor, do que uma Super-Query-Megazord.

Desejo a todos um ótimo final de semana, e muitos TERABYTES DE SUCESSO 😀

Referências

 

2 respostas em “Identificando Problemas – Queries lentas – Parte 04”

Deixe um comentário