[firebase-br] Queries (isql) úteis para verificação de métricas de banco de dados Firebird.

[Alexandre Benson Smith]

Em 1/12/2016 11:51, Alexandre Vinhaes - DBA - TIC escreveu:
> Bom dia senhores,
> alguém teria como disponibilizar um conjunto de queries básicas que eu
> possa executar via isql para verificação de problemas no banco Firebird ?
> Locks, contenções, utilização de memória, queries lentas, hit ratios etc ?
> Sou especialista em Oracle e SQL Server, porém estou começando com Firebird
> agora.
>
> Muito obrigado.
> Atenciosamente.
> Alexandre Vinhaes

Xará, não tenho as queries para tirar da manga...

Mas a principal causa de problema de performance no Firebird é o 
controle transacional.

Abre-se a transação no inicio da aplicação e não a fecha (ou faz commit 
retaining, que mantémo contexto transacional aberto). Por conta disso, 
pelo fato do Firebird ser um banco que usa a arquitetura MGA (como o 
Postgres e acho que o SQL Server tb se tornou em certo grau), vão 
acumulando versões de registros, uma para cada visão do ambiente 
transacional. em cada acesso o servidor precisa descobrir qual a versão 
que aquela transação deve ver e com isso varre-se uma lista de versões, 
algumas vezes bem grande. Segundo ponto é que como se acumula muito 
"lixo", durante o processo de garbage collection, tem que se limpar 
muito lixo.. Junta as duas coisas e a performance vai pro espaço.

Segundo ponto que mais identifico problemas é a falta de índices 
adequados, mas aí não tem receita mágica.. tem que monitorar as queries 
e ver qual é lenta e a partir daí otimiza-la (ou re-escrevendo ou o mais 
comum, criando índices que faltam).. não tem receita mágica aqui.. cada 
caso é um caso.

Um problema bem comum em queries lentas é algo neste sentido:

SELECT * FROM A LEFT JOIN B ON (A.A = B.A) WHERE B.B = 'XYZ'

O otimizador do firebird faz a análise combinacional dos "rivers" (para 
simplificar, tabelas) até quando encontra um OUTER JOIN (seja ele LEFT 
ou RIGHT). No exemplo acima, como só temos uma tabela antes do OUTER 
JOIN, o otimizador é obrigado a começar por ela, e como não tem filtro 
na clausula WHERE para a tabela A, não tem índice a ser usado apra 
filtra-la, restando fazer uma leitura NATURAL desta tabela, e se ela for 
grande, a performance vai pro espaço. Será usado indice no campo B.A e 
B.B se tiver disponível, mas a tabela A será a primeira a ser lida e 
será lida em sua totalidade.

Mas como otimizar está query então ?

Como o campo B.B está sendo testado com uma igualdade (e não faz 
verificação de NULL), este LEFT JOIN é inútil, pois os registros não 
encontrados na tabela B conterão obrigatoriamente NULL no campo B.B e 
portanto serão excluídos do resultado final, basta trocar para um JOIN 
comum e a performance mudará muito.

Um outro exemplo, similar, mas mais interessante para o mundo real.

SELECT
     *
FROM
     A LEFT JOIN
     B ON (B.A = A.A) JOIN
     C ON (C.A = A.A)
WHERE
     C.C = 'XYZ'

O problema pro otimizador é o mesmo... ele para de fazer as permutações 
de otimização entre as tabelas que estão até o primeiro OUTER JOIN, 
neste caso, novamente só sobrou a tabela A, o filtro da cláusula WHERE 
está na tabela C, supondo que em um campo com índice, mas pelo mesmo 
motivo explicado acima, não será usado pelo filtro principal. Como 
resolver este caso ? Simples.... Coloque todos os INNER's JOIN antes dos 
OUTER's JOIN

SELECT
     *
FROM
     A JOIN
     C ON (C.A = A.A) LEFT JOIN
     B ON (B.A = A.A)
WHERE
     C.C = 'XYZ'

Desta forma o otimizador poderá escolher entre começar a leitura pela 
tabela A ou pela tabela C, como a tabela C tem um campo de filtro na 
cláusula WHERE que tem um índice com boa seletividade, ele começará pela 
tabela C e depois ira buscar a tabela A com base no índice do campo A.A, 
provavelmente uma PK ou FK.

Uma mudança simples que melhora drasticamente a performance. Resumindo.. 
coloque sempre os OUTER JOIN's no fim (a direita se preferir) da query.

Fazer a otimização acaba por ser um problema simples de resolver (na 
maioria dos casos), o grande problema é identificar o gargalo (que foi a 
sua pergunta original). Embora eu não tenha as queries para tirar da 
manga e te fornecer... Posso dar o caminho das pedras.

Estude as monitoring tables, lá tem uma série de informações úteis.
O utilitário GSTAT é seu amigo para dar informações sobre as 
estatísticas do banco de dados, extremamente útil em muitos casos, mas 
precisa aprender a ler seu resultado.
O fb_lock_print traz informações importantes sobre a tabela de lock do 
Firebird, mas também, vai precisar saber o que procurar para poder 
identificar e resolver o problema.

É difícil resumir em uma mensagem, que não seja um livro, os 3 temas 
acima...

E fora o caminho das pedras.. tem o caminho da ponte.. que é ainda mais 
simples... nem precisa molhar o pé e nem correr o risco de escorregar 
nas pedras e cair no rio. Só que a ponte tem pedágio... :)

Olhe as ferramentas da IBSurgeon (IBAnalyst, Firebird Scanner, Firebird 
MonLogger) elas fazem isso de forma automática, dão os resultados 
mastigados e facilitam sua vida enormemente...
Veja:
https://www.youtube.com/watch?v=82cfMCkajU0&list=PL6tMVU1gu_R8aDsi9OBzo_YXRlydtXgcI&index=4
https://www.youtube.com/watch?v=C2L6YejILVc&list=PL6tMVU1gu_R8aDsi9OBzo_YXRlydtXgcI&index=3
https://www.youtube.com/watch?v=L5g3RmgjkiY&list=PL6tMVU1gu_R8aDsi9OBzo_YXRlydtXgcI&index=2

Ou o próprio HQBird, que é uma versão do Firebird com todas as 
ferramentas incluídas e também outras funcionalidades.


até mais...