Dados Gerais | |
Módulo: | TOTVS Automação Fiscal (SIGATAF) |
Issue: | DSERTAF1-31444 |
Descrição: | Otimização query presente no TAFDINAMICJOB no Oracle |
Data |
|
Analista | Fabio Mendonça |
Cliente TOTVS Protheus que utiliza o Módulo 84 - Automação Fiscal para envio de informações trabalhistas, previdenciárias e tributárias ao Ambiente Nacional Virtual de apuração de tributos e da contribuição para o FGTS (eSocial) relata instabilidades no sistema TAF.
A instabilidade mencionada consistiu, segundo o cliente, de evento adverso de pico de utilização em uma das instâncias de banco de dados do SGBD do cliente no BQWS08 (banco de dados do TAF) que consumiu recursos do SGBD a ponto do servidor recusar novas conexões, sendo necessário o extermínio da thread dentro do SGBD e reinício dos serviços.
Em vista do evento adverso de instabilidade nos recursos do servidor devido ao alto processamento originado pela instância da base de dados do TAF, o cliente limitou o número de conexões simultâneas a 20 (vinte) usuários.
Cliente via ticket #15383589 nos envia análise e proposta de solução pelo seu DBA , com o uso de sistemas de índices de forma a reduzir o custo de esforço para o banco na query capturada durante o trace log no banco realizado pelo cliente.
Hints são otimizadores de desempenho que podem ser usados em comandos SQL para alterar planos de execução de tarefas, baseando-se na premissa de que, como projetista da aplicação, o desenvolvedor teria acesso a informações que lhe permitiriam instruir o otimizador do pré-processador do SGBD a uma melhor conduta a ser tomada em determinadas situações.
Os Hints fornecem mecanismos para direcionar o otimizador a escolher um plano de execução mais coerente para determinadas situações baseados em critérios externos específicos que de outra forma o otimizador não teria como saber.
Um exemplo clássico seria o desenvolvedor ter conhecimento de que certo índice é mais seletivo e efetivo para determinadas queries. Com base nessa informação, o desenvolvedor dispõe da informação de que um determinado índice levará a um plano de execução mais efetivo, instrui o otimizador através de Hints embutidos dentro da query que levará a um resultado mais performático.
Existem vários tipos de Hints, como por exemplo Hints para Operações com Joins, Hints para Ordenação de Joins, Hints de Execução de Instruções em Paralelo, entre outros.
O Hint que implementado neste artigo foi da família de Hints de Acesso a Tabelas.
A query construída atualmente pela rotina GETFIL4RET presente no objeto TAFDINAMICJOB gera como saída o script SQL abaixo:
Com a implementação do Hints de Index, o script SQL desejado será construído de forma a gerar o seguinte artefato:
Vale destacar que o ganho em performance consiste em instruir o otimizador do SGBD quanto ao índice mais eficaz para a pesquisa, porém não existe um padrão entre as tabelas do TAF para o índice que possuirá uma sequência de campos específicos, a saber, no caso de nossa demanda, os campos _FILIAL, _STATUS e _ATIVO . Desta feita, cada tabela possui seu índice desejado em uma posição e nomenclatura diferentes entre si, conforme demonstrado abaixo com a relação de índices das tabelas C1E, C92 e C8R .
Ao submeter para análise do SQL Tuning da OEM (Oracle Enterprise Manager) a query gerada pela rotina GETFIL4RET presente no objeto TAFDINAMICJOB, foi gerada uma sugestão pelo otimizador SQL Tuning de um relatório contendo 25 tabelas e seus respectivos campos os quais sua presença na composição dos índices traria otimização ao trabalho de pesquisa.
Os campos relacionados na proposta de otimização do Tuning foram os seguintes: _FILIAL, _ATIVO, _STATUS .
As tabelas relacionadas na proposta de otimização do Tuning foram as seguintes: C1E, C92, C8R, C99, C8V, C8X, C90, C1G, C91, T3P, T72, T1S, CUO, T3A, C9V, T1U, T1V, CM0, C8B, CM6, CM9, CMF, CMD, T92, CMJ.
Conforme demonstrado no print acima, as linhas em amarelo destacam as tabelas que não possuem entre nenhum de seus índices os campos _FILIAL, _STATUS e _ATIVO.
Foi necessário criar índices para as tabelas que não os possuíam, sendo estes nomeados com o nickname 'ORACLEJOB', informação essencial na localização do índice desejado para a implementação da otimização via Hints na query em bancos Oracle.
Para as tabelas que já possuíam o índice desejado, foi necessário atualizar seu nickname para 'ORACLEJOB'.
Foi criada uma rotina dentro do objeto TAFDINAMICJOB que verifica e armazena no padrão JSON de chave-valor a tabela e monta seu respectivo nome de índice no banco de dados, informação necessária para a implementação do Hint de Index nos bancos Oracle.
Conforme demonstrado abaixo, a rotina apenas instancia valor em objeto JSON caso seja de um banco oracle.
Sugestão no uso do parser ChangeQuery antes de instanciar uma área de trabalho pro script SQL. Além de realizar limpeza de espaços vazios desnecessários presentes na query, também realiza pequenas adequações para cada banco de dados buscando otimização de desempenho. Ex.: segundo documentação, remove expressões NOLOCK ou (NOLOCK) e pros antigos bancos DB2 acrescenta a expressão "FOR READ ONLY".
Configurações dos jobs presentes no arquivo de configuração appserver.ini influenciam diretamente o consumo dos serviços REST, principalmente o job TAF_CFGJOB.
Foram realizados testes de desempenho atentando para as propriedades Instances e PrepareIn da chave TAF_CFGJOB com sucesso.
Solicitamos via interação no ticket #15283606 e contato com o suporte a SYS_COMPANY (antiga SM0), a tabela C1E e o appserver.ini do cliente para verificar se estão dispondo da melhor configuração de threads e jobs segundo suas empresas e filiais cadastradas, contudo ainda não obtivemos retorno para prosseguir com análise.
No Objeto WSTAFST2, interno à rotina TAFLastRcN, existe uma chamada a função ChangeQuery, porém, comentada.
No changeset de subida do fonte WSTAFST2 no TFS a função já encontrava-se comentada.
Seria interessante futuramente avaliar a reutilização da função, uma vez que esta visa melhor adaptar ao script SQL informado de forma a escrever a query mais adequada de acordo com o banco utilizado pelo cliente.
TAFDINAMICJOB.PRW
Vide seção 4.
C91, T3P, T72, T1S, CUO, T3A, T1U, T1V, CM0, C8B, CM9, CMF, CMD, T92, CMJ
C91, T3P, T72, T1S, CUO, T3A, T1U, T1V, CM0, C8B, CM9, CMF, CMD, T92, CMJ, C8W, T62, CMR, T1M, T2A, T3V, T72, T1S, T3Z, V73, V75, V76, V77, V78
Depende.
Com a inclusão dos índices que contenham os campos _FILIAL, _STATUS e _ATIVO já foi suficiente pra otimizar o desempenho sem custo adicional.
Com a implementação do Hint de Index também gerou ganhou, porém ao custo de um pequeno processamento de coleta dos índices que otimizariam a query.
TCQUERY: https://tdn.totvs.com/display/tec/Comando+TCQUERY
CHANGEQUERY: https://tdn.totvs.com/display/public/framework/ChangeQuery
EMBEDDED SQL https://tdn.totvs.com/display/public/framework/Embedded+SQL
WEB SERVICE REST - INTEGRAÇÃO: Web Service REST - Integração
LOG PROFILER VIEW: https://arte.engpro.totvs.com.br/framework/logprofile/builds/3.0.1/win/
SQL DEVELOPER: https://www.oracle.com/database/sqldeveloper/technologies/download/
ORACLE DATABASE OPTIMIZER HINTS: https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm
ORACLE DATABASE SQL TUNING GUIDE: https://docs.oracle.com/database/121/TGSQL/tgsql_intro.htm#TGSQL113
ORACLE DATABASE PERFORMANCE TUNING GUIDE: https://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i8327