On 14/08/2013 14:02, Ann Harrison wrote:

>
>
>
> On Aug 12, 2013, at 10:56 AM, Tim Ward <tdw@...
> <mailto:tdw%40telensa.com>> wrote:
>
> > I believe the relevant part of the gstat output for the table in
> > question to be as below, please let me know if I've got that wrong.
> > (Names changed but otherwise unaltered.) My interpretation, which
> may be
> > faulty, of
> >
> > Average version length: 0.00, total versions: 0, max versions: 0
> >
> > is that there are no versions of records lying around, so none of the
> > explanations of the performance so far, all of which I think involve
> > multiple versions in some way, is correct.
>
> What does gstat show about the table before updates?
>
> Best,
>
> Ann
>

I don't have a copy of the table brand new from a new installation
before any updates have been run.

I do however have a copy of the database following backup/restore, at
which point the gstat output is:

Database "mydb.fdb"
Database header page information:
Flags 0
Checksum 12345
Generation 14209
Page size 16384
ODS version 11.1
Oldest transaction 11896
Oldest active 11897
Oldest snapshot 11897
Next transaction 11900
Bumped transaction 1
Sequence number 0
Next attachment ID 2303
Implementation ID 19
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jul 24, 2013 12:30:01
Attributes

Variable header data:
Sweep interval: 0
*END*


Database file sequence:
File mydb.fdb is the only file

Analyzing database pages ...
MYTABLE(224)
Primary pointer page: 546, Index root page: 547
Average record length: 218.81, total records: 110003
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 2007, data page slots: 3214, average fill: 79%
Fill distribution:
0 - 19% = 252
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 1755

and the stats output is

SQL> select count(*) from mytable;

COUNT
============
110003

Current memory = 3076308
Delta memory = 105488
Max memory = 3195572
Elapsed time= 0.30 sec
Buffers = 150
Reads = 2315
Writes 0
Fetches = 226592

which gives the 2,315 reads rather than the 81,000.

--
Tim Ward



[Non-text portions of this message have been removed]