Firebird Database Statistics Reporting Tool

Flags are not used on a database header page.

All checksums are 12345. Checksums on the various database pages are no longer used.

The generation number is incremented each and every time this page is rewritten in the database.

The page size of the entire database. As the database file has to be split into various pages, the SYSDBA can, at creation time, specify how big a page size he or she desires. Every page in the database will be the same size.

The On-Disc Structure of a database defines, possibly along with the SQL dialect, which features of the Firebird database system are available to users of that database. These features may be present in the version of Firebird that you are running, but if the database ODS is older, some of the new features will not be available.

There are a number of different transaction details in the report; these are:

Always zero. This was the sequence number of the database header page, but is no longer used.

The ID number of the next attachment to this database. Every time an application connects to the database, this number goes up by one. Starting up and shutting down the database increases this number too. Gstat connections do not alter the id as they do not connect in a normal manner.

When the database was created, it may have been created on a different system — hardware, operating system etc — to the one on which it is now running. The implementation ID shows you which hardware architecture the database was originally created on.

Displays the number of shadow files attached to this database, or available for use by this database. Sometimes this value is incorrect even when shadow files have been created and/or deleted recently.

If this value shows as zero, the database is using the server’s default value for the number of pages that can be cached in memory when the database is operating. The setting may be defined in the firebird.conf file. On Firebird Superserver 2.1, this setting is the DefaultDbCachePages in the configuration file and is set to 2048 pages. You may use gfix to change this without editing the configuration file.

The database’s SQL dialect number. Normally 1 or 3. This setting can be changed using gfix and, alongside the ODS value, helps determine what features of Firebird are available for use when applications use the database.

The date that this database was created originally. It may show the date that the database was last restored by gbak.

This part of the report displays information about various attributes of the database. Examples of what you may see are:

This part of the report covers information that is not in the fixed part of the database header. For example, the sweep interval is displayed here and information applicable to secondary files, if any, that are attached. If you have backed up the database using the nbackup tool, for example, details of the backup GUID will be displayed here — but only for the most recent backup.

This is the page number, within the database, of the first page with pointers to the data pages of this table. The structure of the database is such that each table has exclusive data pages and a list of those pages is required to be kept somewhere. This statistic gives you the page number for that location.

This is the page number where the first page of pointers to the table’s indices can be found within the database. Every table in the database has one page, the index root page, that holds pointers to the apex pages for each individual index.

The total number of pages allocated to this table. Because gstat doesn’t connect to the database in a transaction-aware manner, it cannot determine whether any of these pages are old record versions (garbage) or deleted records in currently uncommitted transactions, so the number may be higher than it needs to be as these additional pages are included in the total.

This value should be the same as the number of data pages. It reports on the number of pointers to pages in this table, that are stored in various pointer pages internal to the database. If the numbers differ, it may be down to the garbage that remains uncollected.

The calculated space used in each page of the table, on average. The figure includes space utilised by back versions of records in the table. The fill distribution (below) gives more details.

This section of the report displays a 5-band histogram where each band represents 20% of the space filled in each page. In the example above, we see that this table has a single page that is filled less than 20%, 79 pages are filled to between 60% and 79% while the vast majority, 2042, are filled to between 80% and 99%.

This statistic displays the number of pages that have to be accessed in order to get at an index entry. In this example we have to read three separate pages into the buffer cache before we can use the index details to access the row we want in the table. This is often referred to as index indirection.

Depth: 3

tux> # Shutdown the database
tux> gfix -shut -tran 60 employee

tux> # Backup the database
tux> gbak -backup employee /backups/employee.fbk

tux> # Find current page size
tux> gstat employee -header | grep -i "page size"
     page size             4096

tux> # Restore database with a bigger page size
tux> gbak -replace overwrite -page 8192 /backups/employee.fbk employee

tux> # Check new page size
tux gstat employee -header | grep -i "page size"
     page size             8192

tux> #Open the database
tux> gfix -online normal employee

This statistic informs us of the number of leaf pages that this particular index uses. A page and a bucket are synonymous but page tends to be the more modern term in wide use.

leaf buckets: 174

This is the total number of records in the table that have been indexed. However, it is possible — because gstat doesn’t work in a transaction-aware manner — that this figure will possibly include rows that have been deleted (and not garbage-collected) and/or it may count records more than once if they have been modified in such a way that the indexed column(s) have been changed.

nodes: 62372

This statistic indicates the average length of the key column(s) in bytes.

Duplicates are not permitted in a primary key or unique index. Other indexes do permit duplicates and these statistics report on the number of duplicates the index holds. The following isql query shows the details of duplicates for an indexed column in a different table to the one being used so far — which has no duplicates.

SQL> SELECT IDX, COUNT(*)
CON> FROM NORMAN_TEST
CON> GROUP BY IDX;

         IDX        COUNT
============ ============
           1           10
           2            4
           3            1

Index NORMANX (0)
        Depth: 1, leaf buckets: 1, nodes: 15
        Average data length: 0.27, total dup: 12, max dup: 9

The remainder of the report for our original example index shows how the pages are used within the index.

Fill distribution:
             0 - 19% = 15
            20 - 39% = 0
            40 - 59% = 55
            60 - 79% = 68
            80 - 99% = 36

Simply the average record length, in bytes, of all the records in the table. If this figure is 0.00 then you can be reasonably sure that all your records have been deleted, or that you have no records in the table.

The total number of records in the table. The value may include records in currently active transactions and may include records which have been deleted.

tux> # In session 1.
tux> gstat test -r -t NORMAN

...
Analyzing database pages ...
NORMAN (142)
    Primary pointer page: 268, Index root page: 269
    Average record length: 9.00, total records: 15
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 1, data page slots: 1, average fill: 10%

tux> isql tset -user norman -password secret
Database:  employee

SQL> SELECT COUNT(*) FROM NORMAN;

       COUNT
============
          15

tux> # In session 2.
tux> isql test -user norman -password secret
Database:  employee

SQL> DELETE FROM NORMAN;
SQL> COMMIT;
SQL> shell;

tux> gstat test -r -t NORMAN

...
Analyzing database pages ...
NORMAN (142)
    Primary pointer page: 268, Index root page: 269
    Average record length: 0.00, total records: 15
    Average version length: 9.00, total versions: 15, max versions: 1
    Data pages: 1, data page slots: 1, average fill: 16%
...

tux> # Return to isql.
tux> exit

SQL> SELECT * FROM NORMAN;

SQL> shell;

tux> gstat test -r -t NORMAN

...
Analyzing database pages ...
NORMAN (142)
    Primary pointer page: 268, Index root page: 269
    Average record length: 0.00, total records: 0
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 0, data page slots: 0, average fill: 0%

This is similar to the average record length, but for the back versions of the record. For example, if you have deleted a number of records and updated others, the old — back — versions of these records will be reported here. If the figure is 0.00 then garbage collection has taken place and removed the back versions — see above for an example.

The same as total records above, but includes only the back versions. If the figure is 0 then garbage collection has taken place and removed the back versions — see above for an example.

If a record has been updated many times, the max versions statistic shows you the number of back versions of the record (or records) in question. In a table where all the rows have been updated 7 times, but one has been updated 20 times, this statistic will report a value of 20. If the figure is 0.00 then garbage collection has taken place and removed the back versions — see above for an example.