[firebase-br] Estatistica do BD
Josauro S.J.
josauro em casasoft.inf.br
Ter Maio 4 10:27:36 -03 2010
Prezados
Nunca analizei nesse nível e gostaria de uma breve dica, sobre a estatistica
abaixo tirada de um banco de dados de um cliente, o qual tem +- 150 a 200
terminais.
É possível identificar algo que precise ser melhorado, temos sentido uma
queda de performan-se semanalmente, o sweep estava com 20000 setei para 0
hoje e tirei o relatorio, entao o mesmo não foi feito ainda, sera feito
manualmente.
Cortei o log de tabelas para não estender o email.
Database header page information:
Flags 0
Checksum 12345
Generation 11655165
Page size 4096
ODS version 11.0
Oldest transaction 11573097
Oldest active 11573098
Oldest snapshot 11573096
Next transaction 11638337
Bumped transaction 1
Sequence number 0
Next attachment ID 40124
Implementation ID 19
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Mar 3, 2010 10:25:46
Attributes force write
Variable header data:
Sweep interval: 0
*END*
Database file sequence:
File /dados/firebird/data/casasoft.fdb is the only file
Analyzing database pages ...
FICHA_IMOVEL (302)
Primary pointer page: 1225, Index root page: 1226
Data pages: 1286, data page slots: 1286, average fill: 93%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 72
80 - 99% = 1213
Index IDX_FIC_IMOVEL (0)
Depth: 2, leaf buckets: 35, nodes: 10894
Average data length: 6.72, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 4
60 - 79% = 3
80 - 99% = 28
Index PK_FICHA_IMOVEL (1)
Depth: 2, leaf buckets: 35, nodes: 10894
Average data length: 6.71, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 4
60 - 79% = 4
80 - 99% = 27
FILIAL_CONEXAO (142)
Primary pointer page: 901, Index root page: 902
Data pages: 8486, data page slots: 8535, average fill: 56%
Fill distribution:
0 - 19% = 43
20 - 39% = 265
40 - 59% = 8097
60 - 79% = 79
80 - 99% = 2
Index PK_FILIAL_CONEXAO (0)
Depth: 3, leaf buckets: 3303, nodes: 572436
Average data length: 9.84, total dup: 2505, max dup: 22
Fill distribution:
0 - 19% = 7
20 - 39% = 736
40 - 59% = 944
60 - 79% = 149
80 - 99% = 1467
CLIENTE (150)
Primary pointer page: 917, Index root page: 918
Data pages: 4127, data page slots: 4127, average fill: 84%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 394
80 - 99% = 3732
Index IDX_CLI_CNPJ_CPF (8)
Depth: 3, leaf buckets: 360, nodes: 108147
Average data length: 2.85, total dup: 64290, max dup: 46734
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 301
60 - 79% = 3
80 - 99% = 55
Index IDX_CLI_ESTADO_CIVIL (0)
Depth: 2, leaf buckets: 132, nodes: 108146
Average data length: 0.01, total dup: 108139, max dup: 50924
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 45
60 - 79% = 2
80 - 99% = 85
Index IDX_CLI_FILIAL (7)
Depth: 2, leaf buckets: 138, nodes: 108143
Average data length: 0.01, total dup: 108139, max dup: 60904
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 30
60 - 79% = 0
80 - 99% = 107
Index IDX_CLI_NACIONALIDADE (1)
Depth: 2, leaf buckets: 144, nodes: 108143
Average data length: 0.02, total dup: 108039, max dup: 65649
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 61
60 - 79% = 1
80 - 99% = 82
Index IDX_CLI_PROFISSAO (2)
Depth: 2, leaf buckets: 168, nodes: 108146
Average data length: 0.10, total dup: 106681, max dup: 38091
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 98
60 - 79% = 8
80 - 99% = 62
Index IDX_CLI_REFERENCIA (3)
Depth: 3, leaf buckets: 287, nodes: 108143
Average data length: 2.37, total dup: 3410, max dup: 17
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 174
60 - 79% = 5
80 - 99% = 107
Index IDX_SOUNDEX_NOME (5)
Depth: 2, leaf buckets: 122, nodes: 108143
Average data length: 0.12, total dup: 106766, max dup: 106656
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 45
60 - 79% = 0
80 - 99% = 76
Index IDX_SOUNDEX_NOME_ALTERNATIVO (6)
Depth: 2, leaf buckets: 121, nodes: 108143
Average data length: 0.11, total dup: 106833, max dup: 106656
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 45
60 - 79% = 0
80 - 99% = 76
Index PK_CLIENTE (4)
Depth: 3, leaf buckets: 319, nodes: 108143
Average data length: 6.12, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 7
60 - 79% = 2
80 - 99% = 310
FOTO_CLIENTE (249)
Primary pointer page: 1118, Index root page: 1119
Data pages: 0, data page slots: 0, average fill: 0%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index IDX_FTC_CLIENTE (0)
Depth: 1, leaf buckets: 1, nodes: 0
Average data length: 0.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index PK_FOTO_CLIENTE (1)
Depth: 1, leaf buckets: 1, nodes: 0
Average data length: 0.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
FOTO_IMOVEL (282)
Primary pointer page: 1185, Index root page: 1186
Data pages: 1207, data page slots: 1207, average fill: 79%
Fill distribution:
0 - 19% = 1
20 - 39% = 3
40 - 59% = 10
60 - 79% = 877
80 - 99% = 316
Index IDX_FTO_IMOVEL (0)
Depth: 2, leaf buckets: 87, nodes: 41174
Average data length: 0.64, total dup: 37440, max dup: 51
Fill distribution:
0 - 19% = 1
20 - 39% = 7
40 - 59% = 54
60 - 79% = 14
80 - 99% = 11
Index PK_FOTO_IMOVEL (1)
Depth: 2, leaf buckets: 131, nodes: 41174
Average data length: 6.30, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 7
60 - 79% = 15
80 - 99% = 109
ACESSO_REGISTRO (330)
Primary pointer page: 1282, Index root page: 1283
Data pages: 1, data page slots: 1, average fill: 15%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index IDX_ACR_REGISTRO (0)
Depth: 1, leaf buckets: 1, nodes: 10
Average data length: 35.90, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index PK_ACESSO_REGISTRO (1)
Depth: 1, leaf buckets: 1, nodes: 10
Average data length: 7.80, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
AGENCIA (137)
Primary pointer page: 891, Index root page: 892
Data pages: 397, data page slots: 397, average fill: 71%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 397
80 - 99% = 0
Index IDX_AGE_BANCO (0)
Depth: 2, leaf buckets: 25, nodes: 19170
Average data length: 0.03, total dup: 19117, max dup: 5200
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 10
60 - 79% = 0
80 - 99% = 14
Index PK_AGENCIA (1)
Depth: 2, leaf buckets: 57, nodes: 19170
Average data length: 6.67, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 2
60 - 79% = 1
80 - 99% = 54
AGENDA (217)
Primary pointer page: 1053, Index root page: 1054
Data pages: 23, data page slots: 23, average fill: 72%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 23
80 - 99% = 0
Index IDX_AGE_AGENDADO_PARA (0)
Depth: 1, leaf buckets: 1, nodes: 1072
Average data length: 0.00, total dup: 1071, max dup: 1071
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 0
Index IDX_AGE_CONCLUIDO (4)
Depth: 1, leaf buckets: 1, nodes: 1072
Average data length: 0.01, total dup: 1068, max dup: 745
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 1
Index IDX_AGE_RESPONSAVEL (1)
Depth: 1, leaf buckets: 1, nodes: 1072
Average data length: 0.00, total dup: 1071, max dup: 1071
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 0
Index IDX_AGE_USUARIO (2)
Depth: 1, leaf buckets: 1, nodes: 1072
Average data length: 0.00, total dup: 1071, max dup: 1071
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 0
Index PK_AGENDA (3)
Depth: 2, leaf buckets: 4, nodes: 1072
Average data length: 7.11, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 3
AGENDA_RESPONSAVEL (218)
Primary pointer page: 1055, Index root page: 1056
Data pages: 23, data page slots: 23, average fill: 68%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 22
80 - 99% = 0
Index IDX_AGR_AGENDA (0)
Depth: 2, leaf buckets: 4, nodes: 1122
Average data length: 6.79, total dup: 50, max dup: 3
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 3
Index PK_AGENDA_RESPONSAVEL (1)
Depth: 2, leaf buckets: 4, nodes: 1122
Average data length: 7.10, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 3
Mais detalhes sobre a lista de discussão lista