Subject | Re: [firebird-support] Performance Degrade - HELP |
---|---|

Author | Rajesh Punjabi |

Post date | 2003-10-10T12:02:56Z |

Hi Martijn,

here is the output :

------------------------------------------------------------------------------------------------

Database "/home/information/hr.gdb"

Database header page information:

Flags 0

Checksum 12345

Generation 4890

Page size 8192

ODS version 10.0

Oldest transaction 4725

Oldest active 4726

Oldest snapshot 4726

Next transaction 4882

Bumped transaction 1

Sequence number 0

Next attachment ID 0

Implementation ID 19

Shadow count 0

Page buffers 0

Next header page 0

Database dialect 1

Creation date Oct 7, 2003 20:48:55

Attributes force write

Variable header data:

Sweep interval: 20000

*END*

Database file sequence:

File /home/information/hr.gdb is the only file

Database log page information:

Creation date

Log flags: 2

No write ahead log

Next log page: 0

Variable log data:

Control Point 1:

File name:

Partition offset: 0 Seqno: 0 Offset: 0

Control Point 2:

File name:

Partition offset: 0 Seqno: 0 Offset: 0

Current File:

File name:

Partition offset: 0 Seqno: 0 Offset: 0

*END*

Analyzing database pages ...

ACTIVELIST (138)

Primary pointer page: 159, Index root page: 160

Data pages: 1, data page slots: 1, average fill: 57%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY11 (0)

Depth: 1, leaf buckets: 1, nodes: 75

Average data length: 10.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

APPLOG (145)

Primary pointer page: 173, Index root page: 174

Data pages: 748, data page slots: 748, average fill: 80%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 260

80 - 99% = 487

BDFLOW (136)

Primary pointer page: 155, Index root page: 156

Data pages: 2170, data page slots: 2170, average fill: 84%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 2169

Index NEXACTX (1)

Depth: 2, leaf buckets: 87, nodes: 115157

Average data length: 0.00, total dup: 115149, max dup: 32182

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 2

60 - 79% = 1

80 - 99% = 83

Index NEXTDT_IDX (2)

Depth: 2, leaf buckets: 86, nodes: 115157

Average data length: 0.00, total dup: 114021, max dup: 20787

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 85

Index OFFERX (3)

Depth: 2, leaf buckets: 88, nodes: 115157

Average data length: 0.00, total dup: 114943, max dup: 32168

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 4

60 - 79% = 1

80 - 99% = 82

Index PRFX (4)

Depth: 2, leaf buckets: 135, nodes: 115157

Average data length: 0.00, total dup: 82653, max dup: 40

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 92

60 - 79% = 1

80 - 99% = 42

Index RESPX (0)

Depth: 2, leaf buckets: 90, nodes: 115157

Average data length: 0.00, total dup: 115061, max dup: 32168

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 8

60 - 79% = 1

80 - 99% = 80

Index SPDT_IDX (5)

Depth: 2, leaf buckets: 112, nodes: 115157

Average data length: 1.00, total dup: 43683, max dup: 20782

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 2

60 - 79% = 1

80 - 99% = 109

COMPANY (130)

Primary pointer page: 143, Index root page: 144

Data pages: 195, data page slots: 195, average fill: 87%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 5

60 - 79% = 35

80 - 99% = 155

Index CMPNM_IDX (1)

Depth: 2, leaf buckets: 10, nodes: 3051

Average data length: 18.00, total dup: 16, max dup: 7

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 9

Index CMP_CAT_IDX (0)

Depth: 2, leaf buckets: 3, nodes: 3051

Average data length: 0.00, total dup: 3025, max dup: 2792

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 2

Index CMP_CD_IDX (2)

Depth: 2, leaf buckets: 13, nodes: 3051

Average data length: 28.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 13

Index CMP_IDX (3)

Depth: 2, leaf buckets: 3, nodes: 3051

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 1

80 - 99% = 2

COMPDEPT (141)

Primary pointer page: 165, Index root page: 166

Data pages: 3, data page slots: 3, average fill: 76%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 2

Index RDB$PRIMARY27 (0)

Depth: 1, leaf buckets: 1, nodes: 109

Average data length: 15.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

CONTACTS (132)

Primary pointer page: 147, Index root page: 148

Data pages: 3, data page slots: 3, average fill: 67%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 2

Index CNT_CD_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 93

Average data length: 1.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 CNT_NAME_IDX (1)

Depth: 1, leaf buckets: 1, nodes: 93

Average data length: 15.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

EMPLOYEE (129)

Primary pointer page: 141, Index root page: 142

Data pages: 1, data page slots: 1, average fill: 48%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 0

Index EMP_CD_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 16

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

LOCATION (143)

Primary pointer page: 169, Index root page: 170

Data pages: 8, data page slots: 8, average fill: 69%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 7

80 - 99% = 0

Index RDB$PRIMARY5 (0)

Depth: 2, leaf buckets: 4, nodes: 770

Average data length: 29.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 3

NEXACT (131)

Primary pointer page: 145, Index root page: 146

Data pages: 1, data page slots: 1, average fill: 10%

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY2 (0)

Depth: 1, leaf buckets: 1, nodes: 10

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

OFFER (133)

Primary pointer page: 149, Index root page: 150

Data pages: 58, data page slots: 58, average fill: 83%

Fill distribution:

0 - 19% = 1

20 - 39% = 1

40 - 59% = 2

60 - 79% = 16

80 - 99% = 38

Index OFFERSKL_IDX (1)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 25.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 1

Index OFR_CD_IDX (2)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index OFR_CMP_IDX (3)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 0.00, total dup: 147, max dup: 20

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index OFR_CON_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 0.00, total dup: 237, max dup: 174

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

PROFESSIONAL (134)

Primary pointer page: 151, Index root page: 152

Data pages: 17431, data page slots: 17431, average fill: 78%

Fill distribution:

0 - 19% = 199

20 - 39% = 513

40 - 59% = 2190

60 - 79% = 4974

80 - 99% = 9555

Index CITY_X (22)

Depth: 2, leaf buckets: 38, nodes: 39053

Average data length: 0.00, total dup: 38617, max dup: 8905

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 16

60 - 79% = 0

80 - 99% = 21

Index CTRY_X (21)

Depth: 2, leaf buckets: 30, nodes: 39053

Average data length: 0.00, total dup: 38995, max dup: 28013

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 1

80 - 99% = 28

Index DESIG_IDX (20)

Depth: 2, leaf buckets: 32, nodes: 39053

Average data length: 0.00, total dup: 38929, max dup: 26379

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 5

60 - 79% = 2

80 - 99% = 25

Index NAME_X (4)

Depth: 2, leaf buckets: 83, nodes: 39053

Average data length: 7.00, total dup: 2763, max dup: 401

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 36

60 - 79% = 1

80 - 99% = 46

Index PRF_CD_IDX (19)

Depth: 2, leaf buckets: 154, nodes: 39053

Average data length: 25.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 6

60 - 79% = 0

80 - 99% = 147

Index PRF_EMAIL1_IDX (18)

Depth: 2, leaf buckets: 118, nodes: 39053

Average data length: 15.00, total dup: 3001, max dup: 1565

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 24

60 - 79% = 0

80 - 99% = 93

Index PRF_EMAIL2_IDX (17)

Depth: 2, leaf buckets: 61, nodes: 39049

Average data length: 4.00, total dup: 30706, max dup: 29458

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 24

60 - 79% = 0

80 - 99% = 36

Index PRF_EMAIL3_IDX (16)

Depth: 2, leaf buckets: 35, nodes: 39047

Average data length: 0.00, total dup: 37651, max dup: 32206

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 4

60 - 79% = 0

80 - 99% = 31

Index PRF_EMAIL4_IDX (15)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39035, max dup: 31712

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_KEYWD1_IDX (0)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39046, max dup: 31780

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_KEYWD2_IDX (1)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39046, max dup: 31780

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_KEYWD3_IDX (2)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39046, max dup: 31780

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_TEL_CELL_IDX (14)

Depth: 2, leaf buckets: 48, nodes: 39051

Average data length: 1.00, total dup: 30189, max dup: 29431

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 27

60 - 79% = 0

80 - 99% = 21

Index PRF_TEL_FAX_IDX (13)

Depth: 2, leaf buckets: 32, nodes: 39047

Average data length: 0.00, total dup: 38004, max dup: 32728

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 4

60 - 79% = 1

80 - 99% = 27

Index PRF_TEL_OFF_IDX (12)

Depth: 2, leaf buckets: 48, nodes: 39048

Average data length: 2.00, total dup: 26393, max dup: 24782

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 15

60 - 79% = 0

80 - 99% = 33

Index PRF_TEL_PAGER_IDX (11)

Depth: 2, leaf buckets: 30, nodes: 39047

Average data length: 0.00, total dup: 38910, max dup: 31929

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 2

60 - 79% = 0

80 - 99% = 28

Index PRF_TEL_RES_IDX (10)

Depth: 2, leaf buckets: 72, nodes: 39051

Average data length: 4.00, total dup: 12218, max dup: 10588

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 46

60 - 79% = 0

80 - 99% = 25

Index PROF_CMP_IDX (9)

Depth: 2, leaf buckets: 34, nodes: 39047

Average data length: 0.00, total dup: 36716, max dup: 30872

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 8

60 - 79% = 0

80 - 99% = 25

Index PROF_REF_IDX (8)

Depth: 2, leaf buckets: 30, nodes: 39053

Average data length: 0.00, total dup: 38997, max dup: 10271

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 2

80 - 99% = 27

Index QUAL_X (3)

Depth: 2, leaf buckets: 39, nodes: 39053

Average data length: 0.00, total dup: 38984, max dup: 11065

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 20

60 - 79% = 0

80 - 99% = 19

Index SPECL_X (5)

Depth: 2, leaf buckets: 38, nodes: 39052

Average data length: 0.00, total dup: 38862, max dup: 11116

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 16

60 - 79% = 0

80 - 99% = 21

Index STATE_X (6)

Depth: 2, leaf buckets: 36, nodes: 39053

Average data length: 0.00, total dup: 38916, max dup: 13988

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 14

60 - 79% = 0

80 - 99% = 22

Index UNIV_IDX (7)

Depth: 2, leaf buckets: 41, nodes: 39050

Average data length: 0.00, total dup: 38810, max dup: 18191

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 24

60 - 79% = 0

80 - 99% = 17

QNA (135)

Primary pointer page: 153, Index root page: 154

Data pages: 1, data page slots: 1, average fill: 45%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY10 (0)

Depth: 1, leaf buckets: 1, nodes: 13

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

RESPMAST (128)

Primary pointer page: 139, Index root page: 140

Data pages: 2, data page slots: 2, average fill: 61%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 1

80 - 99% = 0

Index RDB$PRIMARY1 (1)

Depth: 1, leaf buckets: 1, nodes: 119

Average data length: 1.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 RESP_TYPE_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 119

Average data length: 0.00, total dup: 114, max dup: 52

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

SKILLS (142)

Primary pointer page: 167, Index root page: 168

Data pages: 2, data page slots: 2, average fill: 70%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 2

80 - 99% = 0

Index SKILL_CD_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 144

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

SYSDEFN (137)

Primary pointer page: 157, Index root page: 158

Data pages: 2, data page slots: 2, average fill: 56%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 1

80 - 99% = 0

Index RDB$PRIMARY9 (0)

Depth: 1, leaf buckets: 1, nodes: 163

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

TASKORGANIZER (144)

Primary pointer page: 171, Index root page: 172

Data pages: 1, data page slots: 1, average fill: 4%

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY6 (0)

Depth: 1, leaf buckets: 1, nodes: 2

Average data length: 37.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

UNIVDEPT (140)

Primary pointer page: 163, Index root page: 164

Data pages: 4, data page slots: 4, average fill: 71%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 2

80 - 99% = 1

Index RDB$PRIMARY26 (0)

Depth: 1, leaf buckets: 1, nodes: 233

Average data length: 24.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 1

UNIVERSITY (139)

Primary pointer page: 161, Index root page: 162

Data pages: 13, data page slots: 13, average fill: 86%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 12

Index RDB$PRIMARY12 (0)

Depth: 1, leaf buckets: 1, nodes: 278

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

------------------------------------------------------------------------------------------------

Database was backed up and restarted recently as you can see from db

creation date. Still problem persists.

Warm regards,

RP

Martijn Tonies wrote:

here is the output :

------------------------------------------------------------------------------------------------

Database "/home/information/hr.gdb"

Database header page information:

Flags 0

Checksum 12345

Generation 4890

Page size 8192

ODS version 10.0

Oldest transaction 4725

Oldest active 4726

Oldest snapshot 4726

Next transaction 4882

Bumped transaction 1

Sequence number 0

Next attachment ID 0

Implementation ID 19

Shadow count 0

Page buffers 0

Next header page 0

Database dialect 1

Creation date Oct 7, 2003 20:48:55

Attributes force write

Variable header data:

Sweep interval: 20000

*END*

Database file sequence:

File /home/information/hr.gdb is the only file

Database log page information:

Creation date

Log flags: 2

No write ahead log

Next log page: 0

Variable log data:

Control Point 1:

File name:

Partition offset: 0 Seqno: 0 Offset: 0

Control Point 2:

File name:

Partition offset: 0 Seqno: 0 Offset: 0

Current File:

File name:

Partition offset: 0 Seqno: 0 Offset: 0

*END*

Analyzing database pages ...

ACTIVELIST (138)

Primary pointer page: 159, Index root page: 160

Data pages: 1, data page slots: 1, average fill: 57%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY11 (0)

Depth: 1, leaf buckets: 1, nodes: 75

Average data length: 10.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

APPLOG (145)

Primary pointer page: 173, Index root page: 174

Data pages: 748, data page slots: 748, average fill: 80%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 260

80 - 99% = 487

BDFLOW (136)

Primary pointer page: 155, Index root page: 156

Data pages: 2170, data page slots: 2170, average fill: 84%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 2169

Index NEXACTX (1)

Depth: 2, leaf buckets: 87, nodes: 115157

Average data length: 0.00, total dup: 115149, max dup: 32182

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 2

60 - 79% = 1

80 - 99% = 83

Index NEXTDT_IDX (2)

Depth: 2, leaf buckets: 86, nodes: 115157

Average data length: 0.00, total dup: 114021, max dup: 20787

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 85

Index OFFERX (3)

Depth: 2, leaf buckets: 88, nodes: 115157

Average data length: 0.00, total dup: 114943, max dup: 32168

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 4

60 - 79% = 1

80 - 99% = 82

Index PRFX (4)

Depth: 2, leaf buckets: 135, nodes: 115157

Average data length: 0.00, total dup: 82653, max dup: 40

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 92

60 - 79% = 1

80 - 99% = 42

Index RESPX (0)

Depth: 2, leaf buckets: 90, nodes: 115157

Average data length: 0.00, total dup: 115061, max dup: 32168

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 8

60 - 79% = 1

80 - 99% = 80

Index SPDT_IDX (5)

Depth: 2, leaf buckets: 112, nodes: 115157

Average data length: 1.00, total dup: 43683, max dup: 20782

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 2

60 - 79% = 1

80 - 99% = 109

COMPANY (130)

Primary pointer page: 143, Index root page: 144

Data pages: 195, data page slots: 195, average fill: 87%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 5

60 - 79% = 35

80 - 99% = 155

Index CMPNM_IDX (1)

Depth: 2, leaf buckets: 10, nodes: 3051

Average data length: 18.00, total dup: 16, max dup: 7

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 9

Index CMP_CAT_IDX (0)

Depth: 2, leaf buckets: 3, nodes: 3051

Average data length: 0.00, total dup: 3025, max dup: 2792

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 2

Index CMP_CD_IDX (2)

Depth: 2, leaf buckets: 13, nodes: 3051

Average data length: 28.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 13

Index CMP_IDX (3)

Depth: 2, leaf buckets: 3, nodes: 3051

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 1

80 - 99% = 2

COMPDEPT (141)

Primary pointer page: 165, Index root page: 166

Data pages: 3, data page slots: 3, average fill: 76%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 2

Index RDB$PRIMARY27 (0)

Depth: 1, leaf buckets: 1, nodes: 109

Average data length: 15.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

CONTACTS (132)

Primary pointer page: 147, Index root page: 148

Data pages: 3, data page slots: 3, average fill: 67%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 2

Index CNT_CD_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 93

Average data length: 1.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 CNT_NAME_IDX (1)

Depth: 1, leaf buckets: 1, nodes: 93

Average data length: 15.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

EMPLOYEE (129)

Primary pointer page: 141, Index root page: 142

Data pages: 1, data page slots: 1, average fill: 48%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 0

Index EMP_CD_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 16

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

LOCATION (143)

Primary pointer page: 169, Index root page: 170

Data pages: 8, data page slots: 8, average fill: 69%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 7

80 - 99% = 0

Index RDB$PRIMARY5 (0)

Depth: 2, leaf buckets: 4, nodes: 770

Average data length: 29.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 3

NEXACT (131)

Primary pointer page: 145, Index root page: 146

Data pages: 1, data page slots: 1, average fill: 10%

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY2 (0)

Depth: 1, leaf buckets: 1, nodes: 10

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

OFFER (133)

Primary pointer page: 149, Index root page: 150

Data pages: 58, data page slots: 58, average fill: 83%

Fill distribution:

0 - 19% = 1

20 - 39% = 1

40 - 59% = 2

60 - 79% = 16

80 - 99% = 38

Index OFFERSKL_IDX (1)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 25.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 1

Index OFR_CD_IDX (2)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index OFR_CMP_IDX (3)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 0.00, total dup: 147, max dup: 20

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index OFR_CON_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 245

Average data length: 0.00, total dup: 237, max dup: 174

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

PROFESSIONAL (134)

Primary pointer page: 151, Index root page: 152

Data pages: 17431, data page slots: 17431, average fill: 78%

Fill distribution:

0 - 19% = 199

20 - 39% = 513

40 - 59% = 2190

60 - 79% = 4974

80 - 99% = 9555

Index CITY_X (22)

Depth: 2, leaf buckets: 38, nodes: 39053

Average data length: 0.00, total dup: 38617, max dup: 8905

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 16

60 - 79% = 0

80 - 99% = 21

Index CTRY_X (21)

Depth: 2, leaf buckets: 30, nodes: 39053

Average data length: 0.00, total dup: 38995, max dup: 28013

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 1

80 - 99% = 28

Index DESIG_IDX (20)

Depth: 2, leaf buckets: 32, nodes: 39053

Average data length: 0.00, total dup: 38929, max dup: 26379

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 5

60 - 79% = 2

80 - 99% = 25

Index NAME_X (4)

Depth: 2, leaf buckets: 83, nodes: 39053

Average data length: 7.00, total dup: 2763, max dup: 401

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 36

60 - 79% = 1

80 - 99% = 46

Index PRF_CD_IDX (19)

Depth: 2, leaf buckets: 154, nodes: 39053

Average data length: 25.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 6

60 - 79% = 0

80 - 99% = 147

Index PRF_EMAIL1_IDX (18)

Depth: 2, leaf buckets: 118, nodes: 39053

Average data length: 15.00, total dup: 3001, max dup: 1565

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 24

60 - 79% = 0

80 - 99% = 93

Index PRF_EMAIL2_IDX (17)

Depth: 2, leaf buckets: 61, nodes: 39049

Average data length: 4.00, total dup: 30706, max dup: 29458

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 24

60 - 79% = 0

80 - 99% = 36

Index PRF_EMAIL3_IDX (16)

Depth: 2, leaf buckets: 35, nodes: 39047

Average data length: 0.00, total dup: 37651, max dup: 32206

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 4

60 - 79% = 0

80 - 99% = 31

Index PRF_EMAIL4_IDX (15)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39035, max dup: 31712

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_KEYWD1_IDX (0)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39046, max dup: 31780

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_KEYWD2_IDX (1)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39046, max dup: 31780

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_KEYWD3_IDX (2)

Depth: 2, leaf buckets: 31, nodes: 39047

Average data length: 0.00, total dup: 39046, max dup: 31780

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 3

60 - 79% = 0

80 - 99% = 28

Index PRF_TEL_CELL_IDX (14)

Depth: 2, leaf buckets: 48, nodes: 39051

Average data length: 1.00, total dup: 30189, max dup: 29431

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 27

60 - 79% = 0

80 - 99% = 21

Index PRF_TEL_FAX_IDX (13)

Depth: 2, leaf buckets: 32, nodes: 39047

Average data length: 0.00, total dup: 38004, max dup: 32728

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 4

60 - 79% = 1

80 - 99% = 27

Index PRF_TEL_OFF_IDX (12)

Depth: 2, leaf buckets: 48, nodes: 39048

Average data length: 2.00, total dup: 26393, max dup: 24782

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 15

60 - 79% = 0

80 - 99% = 33

Index PRF_TEL_PAGER_IDX (11)

Depth: 2, leaf buckets: 30, nodes: 39047

Average data length: 0.00, total dup: 38910, max dup: 31929

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 2

60 - 79% = 0

80 - 99% = 28

Index PRF_TEL_RES_IDX (10)

Depth: 2, leaf buckets: 72, nodes: 39051

Average data length: 4.00, total dup: 12218, max dup: 10588

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 46

60 - 79% = 0

80 - 99% = 25

Index PROF_CMP_IDX (9)

Depth: 2, leaf buckets: 34, nodes: 39047

Average data length: 0.00, total dup: 36716, max dup: 30872

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 8

60 - 79% = 0

80 - 99% = 25

Index PROF_REF_IDX (8)

Depth: 2, leaf buckets: 30, nodes: 39053

Average data length: 0.00, total dup: 38997, max dup: 10271

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 2

80 - 99% = 27

Index QUAL_X (3)

Depth: 2, leaf buckets: 39, nodes: 39053

Average data length: 0.00, total dup: 38984, max dup: 11065

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 20

60 - 79% = 0

80 - 99% = 19

Index SPECL_X (5)

Depth: 2, leaf buckets: 38, nodes: 39052

Average data length: 0.00, total dup: 38862, max dup: 11116

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 16

60 - 79% = 0

80 - 99% = 21

Index STATE_X (6)

Depth: 2, leaf buckets: 36, nodes: 39053

Average data length: 0.00, total dup: 38916, max dup: 13988

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 14

60 - 79% = 0

80 - 99% = 22

Index UNIV_IDX (7)

Depth: 2, leaf buckets: 41, nodes: 39050

Average data length: 0.00, total dup: 38810, max dup: 18191

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 24

60 - 79% = 0

80 - 99% = 17

QNA (135)

Primary pointer page: 153, Index root page: 154

Data pages: 1, data page slots: 1, average fill: 45%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY10 (0)

Depth: 1, leaf buckets: 1, nodes: 13

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

RESPMAST (128)

Primary pointer page: 139, Index root page: 140

Data pages: 2, data page slots: 2, average fill: 61%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 1

80 - 99% = 0

Index RDB$PRIMARY1 (1)

Depth: 1, leaf buckets: 1, nodes: 119

Average data length: 1.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 RESP_TYPE_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 119

Average data length: 0.00, total dup: 114, max dup: 52

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

SKILLS (142)

Primary pointer page: 167, Index root page: 168

Data pages: 2, data page slots: 2, average fill: 70%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 2

80 - 99% = 0

Index SKILL_CD_IDX (0)

Depth: 1, leaf buckets: 1, nodes: 144

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

SYSDEFN (137)

Primary pointer page: 157, Index root page: 158

Data pages: 2, data page slots: 2, average fill: 56%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 1

80 - 99% = 0

Index RDB$PRIMARY9 (0)

Depth: 1, leaf buckets: 1, nodes: 163

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

TASKORGANIZER (144)

Primary pointer page: 171, Index root page: 172

Data pages: 1, data page slots: 1, average fill: 4%

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

Index RDB$PRIMARY6 (0)

Depth: 1, leaf buckets: 1, nodes: 2

Average data length: 37.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

UNIVDEPT (140)

Primary pointer page: 163, Index root page: 164

Data pages: 4, data page slots: 4, average fill: 71%

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 1

60 - 79% = 2

80 - 99% = 1

Index RDB$PRIMARY26 (0)

Depth: 1, leaf buckets: 1, nodes: 233

Average data length: 24.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 1

UNIVERSITY (139)

Primary pointer page: 161, Index root page: 162

Data pages: 13, data page slots: 13, average fill: 86%

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 12

Index RDB$PRIMARY12 (0)

Depth: 1, leaf buckets: 1, nodes: 278

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 0

60 - 79% = 0

80 - 99% = 0

------------------------------------------------------------------------------------------------

Database was backed up and restarted recently as you can see from db

creation date. Still problem persists.

Warm regards,

RP

Martijn Tonies wrote:

>Hi,

>

>

>

>>I have a production DB thats about 502 MB (single file) running on FB

>>(ver 1.0.3) (SS 32 bit) on Redhat Linux 8.0 & I am using a Athlon 2GHz

>>server with 36 GB SCSI disk and 1 GB physical RAM. This is a resume

>>database with about 40000 resumes (stored as blobs on the server)

>>

>>There is something very peculiar happening as the database is being

>>queried, the server free physical memory count keeps going down as users

>>keep querying. In fact the database takes up the entire available free

>>physical memory and moves on to the swap space and slows down users

>>considerably (1200+ % degrade is common). I am aware of the memory usage

>>going up coz I am monitoring the same using Linux commands (top d 10)

>>and there is no other app apart from FB on the said server.

>>

>>This problem did not exist till the database size was less than 350 MB.

>>Recently a large addition caused the db size to shoot up past 500 MB and

>>this seems to have caused the problem.

>>

>>The frontend is a thick client Delphi app using DBXpress drivers.

>>

>>What can be the problem ?

>>

>>

>

>Can you run "gstat" on your database and post the transaction counters?

>

>

>With regards,

>

>Martijn Tonies

>Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.

>Upscene Productions

>http://www.upscene.com

>

>