[firebase-br] Estatistica do BD

Carlos H. Cantu listas em warmboot.com.br
Ter Maio 4 13:31:19 -03 2010


Vc poderia consultar as tabelas de monitoramento, mas soh existem a
partir do FB 2.1

Outra opção é usar o fbscanner, da IBSurgeon, que funciona com qq
versão do FB, ou talvez o Sinatica Monitor.

[]s
Carlos H. Cantu
www.FireBase.com.br - www.firebirdnews.org
www.warmboot.com.br - blog.firebase.com.br

JSJ> Obrigado Cantu,

JSJ> Existe uma forma de identificar hoje qual a transação mais antiga o que
JSJ> estava fazendo, talvez pelas tabelas do sistema, ou que registros foi usada
JSJ> assim ficaria mais facil localizar em que parte do sistema pode ter ficado
JSJ> pendurado. (FB 2.0.5)

JSJ> Respondendo outra pergunta: Foi tirado vai IBExpert, Service, Data Base
JSJ> Properties, mas pode ser tirado pelo IBConsole -> Maintenace / DataBase
JSJ> Statistics
JSJ> Grato
JSJ> Josauro S.J.
JSJ> ----------------------------------------------------------------------

JSJ> Message: 1
JSJ> Date: Tue, 4 May 2010 10:50:08 -0300
JSJ> From: "Carlos H. Cantu" <listas em warmboot.com.br>
JSJ> To: FireBase <lista em firebase.com.br>
JSJ> Subject: Re: [firebase-br] Estatistica do BD
JSJ> Message-ID: <942858906.20100504105008 em warmboot.com.br>
JSJ> Content-Type: text/plain; charset=iso-8859-1

JSJ>         Oldest active           11573098
JSJ>         Next transaction        11638337

JSJ> Pelos números, existe uma transação que está ativa há 65.239
JSJ> transações "atrás". Reveja seu controle transacional, pois isso impede
JSJ> a coleta de lixo e geralmente prejudica a performance do servidor.

JSJ> []s
JSJ> Carlos H. Cantu
JSJ> www.FireBase.com.br - www.firebirdnews.org
JSJ> www.warmboot.com.br - blog.firebase.com.br

JSJ>> Prezados

JSJ>> Nunca analizei nesse nível e gostaria de uma breve dica, sobre a 
JSJ> estatistica
JSJ>> abaixo tirada de um banco de dados de um cliente, o qual tem +- 150 a 
JSJ> 200
JSJ>> terminais.
JSJ>> É possível identificar algo que precise ser melhorado, temos sentido 
JSJ> uma
JSJ>> queda de performan-se semanalmente, o sweep estava com 20000 setei para 
JSJ> 0
JSJ>> hoje e tirei o relatorio, entao o mesmo não foi feito ainda, sera feito
JSJ>> manualmente.
JSJ>> Cortei o log de tabelas para não estender o email.


JSJ>> Database header page information:
JSJ>>         Flags                   0
JSJ>>         Checksum                12345
JSJ>>         Generation              11655165
JSJ>>         Page size               4096
JSJ>>         ODS version             11.0
JSJ>>         Oldest transaction      11573097
JSJ>>         Oldest active           11573098
JSJ>>         Oldest snapshot         11573096
JSJ>>         Next transaction        11638337
JSJ>>         Bumped transaction      1
JSJ>>         Sequence number         0
JSJ>>         Next attachment ID      40124
JSJ>>         Implementation ID       19
JSJ>>         Shadow count            0
JSJ>>         Page buffers            0
JSJ>>         Next header page        0
JSJ>>         Database dialect        3
JSJ>>         Creation date           Mar 3, 2010 10:25:46
JSJ>>         Attributes              force write

JSJ>>     Variable header data:
JSJ>>         Sweep interval:         0
JSJ>>         *END*


JSJ>> Database file sequence:
JSJ>> File /dados/firebird/data/casasoft.fdb is the only file

JSJ>> Analyzing database pages ...
JSJ>> FICHA_IMOVEL (302)
JSJ>>     Primary pointer page: 1225, Index root page: 1226
JSJ>>     Data pages: 1286, data page slots: 1286, average fill: 93%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 0
JSJ>>         20 - 39% = 1
JSJ>>         40 - 59% = 0
JSJ>>         60 - 79% = 72
JSJ>>         80 - 99% = 1213

JSJ>>     Index IDX_FIC_IMOVEL (0)
JSJ>>         Depth: 2, leaf buckets: 35, nodes: 10894
JSJ>>         Average data length: 6.72, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 4
JSJ>>             60 - 79% = 3
JSJ>>             80 - 99% = 28

JSJ>>     Index PK_FICHA_IMOVEL (1)
JSJ>>         Depth: 2, leaf buckets: 35, nodes: 10894
JSJ>>         Average data length: 6.71, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 4
JSJ>>             60 - 79% = 4
JSJ>>             80 - 99% = 27

JSJ>> FILIAL_CONEXAO (142)
JSJ>>     Primary pointer page: 901, Index root page: 902
JSJ>>     Data pages: 8486, data page slots: 8535, average fill: 56%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 43
JSJ>>         20 - 39% = 265
JSJ>>         40 - 59% = 8097
JSJ>>         60 - 79% = 79
JSJ>>         80 - 99% = 2

JSJ>>     Index PK_FILIAL_CONEXAO (0)
JSJ>>         Depth: 3, leaf buckets: 3303, nodes: 572436
JSJ>>         Average data length: 9.84, total dup: 2505, max dup: 22
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 7
JSJ>>             20 - 39% = 736
JSJ>>             40 - 59% = 944
JSJ>>             60 - 79% = 149
JSJ>>             80 - 99% = 1467

JSJ>> CLIENTE (150)
JSJ>>     Primary pointer page: 917, Index root page: 918
JSJ>>     Data pages: 4127, data page slots: 4127, average fill: 84%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 0
JSJ>>         20 - 39% = 1
JSJ>>         40 - 59% = 0
JSJ>>         60 - 79% = 394
JSJ>>         80 - 99% = 3732

JSJ>>     Index IDX_CLI_CNPJ_CPF (8)
JSJ>>         Depth: 3, leaf buckets: 360, nodes: 108147
JSJ>>         Average data length: 2.85, total dup: 64290, max dup: 46734
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 301
JSJ>>             60 - 79% = 3
JSJ>>             80 - 99% = 55

JSJ>>     Index IDX_CLI_ESTADO_CIVIL (0)
JSJ>>         Depth: 2, leaf buckets: 132, nodes: 108146
JSJ>>         Average data length: 0.01, total dup: 108139, max dup: 50924
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 45
JSJ>>             60 - 79% = 2
JSJ>>             80 - 99% = 85

JSJ>>     Index IDX_CLI_FILIAL (7)
JSJ>>         Depth: 2, leaf buckets: 138, nodes: 108143
JSJ>>         Average data length: 0.01, total dup: 108139, max dup: 60904
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 30
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 107

JSJ>>     Index IDX_CLI_NACIONALIDADE (1)
JSJ>>         Depth: 2, leaf buckets: 144, nodes: 108143
JSJ>>         Average data length: 0.02, total dup: 108039, max dup: 65649
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 61
JSJ>>             60 - 79% = 1
JSJ>>             80 - 99% = 82

JSJ>>     Index IDX_CLI_PROFISSAO (2)
JSJ>>         Depth: 2, leaf buckets: 168, nodes: 108146
JSJ>>         Average data length: 0.10, total dup: 106681, max dup: 38091
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 98
JSJ>>             60 - 79% = 8
JSJ>>             80 - 99% = 62

JSJ>>     Index IDX_CLI_REFERENCIA (3)
JSJ>>         Depth: 3, leaf buckets: 287, nodes: 108143
JSJ>>         Average data length: 2.37, total dup: 3410, max dup: 17
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 174
JSJ>>             60 - 79% = 5
JSJ>>             80 - 99% = 107

JSJ>>     Index IDX_SOUNDEX_NOME (5)
JSJ>>         Depth: 2, leaf buckets: 122, nodes: 108143
JSJ>>         Average data length: 0.12, total dup: 106766, max dup: 106656
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 45
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 76

JSJ>>     Index IDX_SOUNDEX_NOME_ALTERNATIVO (6)
JSJ>>         Depth: 2, leaf buckets: 121, nodes: 108143
JSJ>>         Average data length: 0.11, total dup: 106833, max dup: 106656
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 45
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 76

JSJ>>     Index PK_CLIENTE (4)
JSJ>>         Depth: 3, leaf buckets: 319, nodes: 108143
JSJ>>         Average data length: 6.12, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 7
JSJ>>             60 - 79% = 2
JSJ>>             80 - 99% = 310

JSJ>> FOTO_CLIENTE (249)
JSJ>>     Primary pointer page: 1118, Index root page: 1119
JSJ>>     Data pages: 0, data page slots: 0, average fill: 0%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 0
JSJ>>         20 - 39% = 0
JSJ>>         40 - 59% = 0
JSJ>>         60 - 79% = 0
JSJ>>         80 - 99% = 0

JSJ>>     Index IDX_FTC_CLIENTE (0)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 0
JSJ>>         Average data length: 0.00, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 0

JSJ>>     Index PK_FOTO_CLIENTE (1)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 0
JSJ>>         Average data length: 0.00, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 0

JSJ>> FOTO_IMOVEL (282)
JSJ>>     Primary pointer page: 1185, Index root page: 1186
JSJ>>     Data pages: 1207, data page slots: 1207, average fill: 79%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 1
JSJ>>         20 - 39% = 3
JSJ>>         40 - 59% = 10
JSJ>>         60 - 79% = 877
JSJ>>         80 - 99% = 316

JSJ>>     Index IDX_FTO_IMOVEL (0)
JSJ>>         Depth: 2, leaf buckets: 87, nodes: 41174
JSJ>>         Average data length: 0.64, total dup: 37440, max dup: 51
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 7
JSJ>>             40 - 59% = 54
JSJ>>             60 - 79% = 14
JSJ>>             80 - 99% = 11

JSJ>>     Index PK_FOTO_IMOVEL (1)
JSJ>>         Depth: 2, leaf buckets: 131, nodes: 41174
JSJ>>         Average data length: 6.30, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 7
JSJ>>             60 - 79% = 15
JSJ>>             80 - 99% = 109

JSJ>> ACESSO_REGISTRO (330)
JSJ>>     Primary pointer page: 1282, Index root page: 1283
JSJ>>     Data pages: 1, data page slots: 1, average fill: 15%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 1
JSJ>>         20 - 39% = 0
JSJ>>         40 - 59% = 0
JSJ>>         60 - 79% = 0
JSJ>>         80 - 99% = 0

JSJ>>     Index IDX_ACR_REGISTRO (0)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 10
JSJ>>         Average data length: 35.90, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 0

JSJ>>     Index PK_ACESSO_REGISTRO (1)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 10
JSJ>>         Average data length: 7.80, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 0

JSJ>> AGENCIA (137)
JSJ>>     Primary pointer page: 891, Index root page: 892
JSJ>>     Data pages: 397, data page slots: 397, average fill: 71%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 0
JSJ>>         20 - 39% = 0
JSJ>>         40 - 59% = 0
JSJ>>         60 - 79% = 397
JSJ>>         80 - 99% = 0

JSJ>>     Index IDX_AGE_BANCO (0)
JSJ>>         Depth: 2, leaf buckets: 25, nodes: 19170
JSJ>>         Average data length: 0.03, total dup: 19117, max dup: 5200
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 10
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 14

JSJ>>     Index PK_AGENCIA (1)
JSJ>>         Depth: 2, leaf buckets: 57, nodes: 19170
JSJ>>         Average data length: 6.67, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 2
JSJ>>             60 - 79% = 1
JSJ>>             80 - 99% = 54

JSJ>> AGENDA (217)
JSJ>>     Primary pointer page: 1053, Index root page: 1054
JSJ>>     Data pages: 23, data page slots: 23, average fill: 72%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 0
JSJ>>         20 - 39% = 0
JSJ>>         40 - 59% = 0
JSJ>>         60 - 79% = 23
JSJ>>         80 - 99% = 0

JSJ>>     Index IDX_AGE_AGENDADO_PARA (0)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 1072
JSJ>>         Average data length: 0.00, total dup: 1071, max dup: 1071
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 1
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 0

JSJ>>     Index IDX_AGE_CONCLUIDO (4)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 1072
JSJ>>         Average data length: 0.01, total dup: 1068, max dup: 745
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 1

JSJ>>     Index IDX_AGE_RESPONSAVEL (1)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 1072
JSJ>>         Average data length: 0.00, total dup: 1071, max dup: 1071
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 1
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 0

JSJ>>     Index IDX_AGE_USUARIO (2)
JSJ>>         Depth: 1, leaf buckets: 1, nodes: 1072
JSJ>>         Average data length: 0.00, total dup: 1071, max dup: 1071
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 0
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 1
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 0

JSJ>>     Index PK_AGENDA (3)
JSJ>>         Depth: 2, leaf buckets: 4, nodes: 1072
JSJ>>         Average data length: 7.11, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 3

JSJ>> AGENDA_RESPONSAVEL (218)
JSJ>>     Primary pointer page: 1055, Index root page: 1056
JSJ>>     Data pages: 23, data page slots: 23, average fill: 68%
JSJ>>     Fill distribution:
JSJ>>          0 - 19% = 0
JSJ>>         20 - 39% = 1
JSJ>>         40 - 59% = 0
JSJ>>         60 - 79% = 22
JSJ>>         80 - 99% = 0

JSJ>>     Index IDX_AGR_AGENDA (0)
JSJ>>         Depth: 2, leaf buckets: 4, nodes: 1122
JSJ>>         Average data length: 6.79, total dup: 50, max dup: 3
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 3

JSJ>>     Index PK_AGENDA_RESPONSAVEL (1)
JSJ>>         Depth: 2, leaf buckets: 4, nodes: 1122
JSJ>>         Average data length: 7.10, total dup: 0, max dup: 0
JSJ>>         Fill distribution:
JSJ>>              0 - 19% = 1
JSJ>>             20 - 39% = 0
JSJ>>             40 - 59% = 0
JSJ>>             60 - 79% = 0
JSJ>>             80 - 99% = 3





JSJ> ______________________________________________
JSJ> FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
JSJ> Para saber como gerenciar/excluir seu cadastro na lista, use:
JSJ> http://www.firebase.com.br/fb/artigo.php?id=1107
JSJ> Para consultar mensagens antigas: http://firebase.com.br/pesquisa





Mais detalhes sobre a lista de discussão lista