Firebird Gstat Database Statistics Reporting Tool | Gstat Examples And Interpretation

Selecting Tables To Analyse

To limit the analysis to a specific list of tables, rather than all user tables, you can use the [gstat-cmdline-table] switch to specify the ones you wish to analyse.Note that specifying table names in this manner also analyses all indices associated with those tables, unless only [gstat-cmdline-data] or [gstat-cmdline-index] is specified.

gstat employee -t EMPLOYEE JOB COUNTRY >employee.gst

The resulting output is interpreted as described above.

If you have a table name that has been created by a user wishing to preserve the letter case of the table name, rather than having it converted to uppercase, for example:

tux> isql myMusic
Database:  mymusic

SQL> CREATE TABLE "MyMusic_Artists" (
CON> art_id integer,
CON> art_name ....);

SQL> COMMIT;

... then you must supply the table names in exactly the same letter case as the name of the table within the database:

gstat mymusic -t MyMusic_Titles MyMusic_Artists > MyMusic.gst

If you supply a non-existing table name, or get the name in the wrong case, etc., gstat will output an error and not analyse any table.

Including The System Tables & Indices

Normal use of gstat doesn’t include the system tables and indices in the output.Calling gstat with the [gstat-cmdline-system] switch causes these tables to be included in the analysis.

gstat employee -system >employee.gst

The interpretation of the results for the various system tables and indices is exactly as described above for user tables and indices.

Record & Version Details

When you run gstat with either the default switches, or [gstat-cmdline-data] or [gstat-cmdline-table] and add the [gstat-cmdline-record] switch, you get additional information in the report that shows the average record length and average version details for the table(s) in question:

Average record length: 96.55, total records: 62372
    Average version length: 0.00, total versions: 0, max versions: 0

Average record length

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.

Total records

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

At this point, we can see that there are 15 records in the NORMAN table and that the average length of these 15 records is 9.00 bytes.Next, we start another isql session and delete all the records from the NORMAN table.

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

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

Still in the second session, we execute gstat to fetch statistics for the NORMAN table, the results are shown below.

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

Comparing the report above with the one taken before we deleted the records, we can see straight away that:

The average record length indicates that there are no records in the table, but the total record count shows that there are (still) 15.This is a good indicator that a session has deleted all the records but garbage collection has yet to run.
The versioning details have all changed, there are now statistics for average version length, total versions and max versions.
The average fill for the page(s) in this table has risen from 10% to 16% even though everything has been deleted.The extra space is being used by the back versions of the deleted records.

Continuing in the second session, if we execute a full table scan of the NORMAN table we will not see any results, but we will garbage collect the back versions.

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%

Everything has now returned to zero.There are no back versions, no current versions and the page is no longer filled.

Average version length

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.

Total versions

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.

Max versions

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.

If You Have Database Corruption

In the unlikely event of a database corruption, your gstat output may have the following within the report:

Database file sequence:
File /opt/firebird/examples/empbuild/corrupt.fdb is the only file

Analyzing database pages ...
    Expected b-tree bucket on page 337334 from 146314

If you ever see a message like the above, displayed just after the header information, you are advised to immediately shut down all connections to the database, make an operating system level copy of the database file(s) and attempt to run gbak against the database to take a full backup.Using nbackup may copy the database happily, but not report any errors.Gbak, on the other hand, will flag up errors.

Gstat Caveats

The following is a brief list of gotchas and funnies that I have detected in my own use of gstat.Some of these are mentioned above, others may not be.By collecting them all here in one place, you should be able to find out what’s happening if you have problems.

The `-⁠t[able]` Switch Can Cause Problems

The [gstat-cmdline-table] switch expects a list of table names to be supplied.

In older versions, if you supply the database name after a table name, it is assumed to be a table name, and you are prompted for a database name.

tux> gstat -t EMPLOYEE JOB employee
please retry, giving a database name

In Firebird 2.5 and higher, using -⁠t before the database name accepts only one table name, and a second table name is interpreted as the database name, resulting in an error when the actual database name is encountered.

> gstat -u sysdba -t EMPLOYEE JOB employee
database name was already specified

For this reason, call gstat with the database name as the very first parameter, or at least put the -⁠t[able] option after the database name:

tux> gstat employee -t EMPLOYEE JOB

Database "/opt/firebird/examples/empbuild/employee.fdb"
Database header page information:
...

Database file sequence:
File /opt/firebird/examples/empbuild/employee.fdb is the only file

Analyzing database pages ...
...

In Firebird 2.1 and earlier, you can supply an additional switch after the last table name and before the database name.This trick no longer works in Firebird 2.5 and higher, as it will only accept one table name when before the database name.

tux> gstat -t EMPLOYEE JOB -z employee
gstat version LI-V2.1.3.18185 Firebird 2.1

Database "/opt/firebird/examples/empbuild/employee.fdb"
Database header page information:
...

Database file sequence:
File /opt/firebird/examples/empbuild/employee.fdb is the only file
        Firebird/linux Intel (access method), version
"LI-V2.1.3.18185 Firebird 2.1"
        Firebird/linux Intel (remote server), version
"LI-V2.1.3.18185 Firebird 2.1/tcp (greenbird)/P11"
        Firebird/linux Intel (remote interface), version
"LI-V2.1.3.18185 Firebird 2.1/tcp (greenbird)/P11"
        on disk structure version 11.1

Analyzing database pages ...

The Shadow Count Seems Wrong

It appears that adding and/or dropping shadow files from a database is not always reported by gstat when it produces a database report.

tux> # Use gstat to display shadow details
tux> gstat employee -h|grep -i sh[a]dow
        Shadow count            0

tux> isql employee
Database: employee

SQL> SHOW DATABASE;
Database: employee
        Owner: SYSDBA
 Shadow 1: "/u00/firebird/databases/employee.shd1" auto
...

Straight away, it is obvious that the report from gstat is incorrect as the employee database has one shadow file.If we use isql to add a new shadow file to this database, as shown below, gstat still insists that there are no shadows.

SQL> CREATE SHADOW 7 AUTO '/u00/firebird/databases/employee.shd7';

SQL> SHOW DATABASE;
Database: employee
        Owner: SYSDBA
 Shadow 1: "/u00/firebird/databases/employee.shd1" auto
 Shadow 7: "/u00/firebird/databases/employee.shd7" auto
...

SQL> shell;

tux> gstat employee -h | grep -i sh[a]dow
        Shadow count            0

Document history

The exact file history is recorded in the firebird-documentation git repository; see https://github.com/FirebirdSQL/firebird-documentation

Revision History

Revision History
1.9	24 Feb 2024	MR	Fixed broken cross-references caused by using prefix `gbak` instead of `gstat`
1.8	24 Feb 2024	MR	Misc. copy editing Add links from usage instruction to relevant sections Include `gstat` name in the document title, making it the same as on https://firebirdsql.org/en/reference-manuals/
1.7	23 Feb 2024	MR	Reordered document history so most recent changes are on the top Convert commandline options from definition list to sections Add switches: `-⁠?`, `-⁠encryption`, `-⁠nocreation`, `-⁠role`, and `-⁠trusted` Misc. copy editing, and updating information for newer version Add some links to gfix and gbak documentation Cross-links between sections Added word-joiner in commandline switches between minus (`-`) and first character to ensure they aren’t broken up on word wrap
1.6	19 Jun 2020	MR	Conversion to AsciiDoc, minor copy-editing
1.5	11 Oct 2011	ND	Updated for Firebird 2.5. Spelling errors corrected.
1.4	23 Mar 2011	ND	Added ODS 9.1 for Interbase 5.0 to the list of known ODS values. Added reference to Managing Limbo Transactions in the gfix manual. Corrected explanation of when an automatic database sweep is carried out, based on OIT and OST as opposed to OIT and OAT.As advised by Vlad Khorsun.
1.3	17 Feb 2010	ND	Formatting errors in the command line switches corrected.
1.2	14 Dec 2009	ND	A couple more minor corrections and spelling mistakes corrected.
1.1	30 Nov 2009	ND	Many corrections suggested by Paul Vinkenoog plus a general tidy up and a few more examples added.
1.0	29 Oct 2009	ND	Created a new gstat manual.

1.9

24 Feb 2024

MR

Fixed broken cross-references caused by using prefix gbak instead of gstat

1.8

24 Feb 2024

MR

Misc. copy editing
Add links from usage instruction to relevant sections
Include gstat name in the document title, making it the same as on https://firebirdsql.org/en/reference-manuals/

1.7

23 Feb 2024

MR

Reordered document history so most recent changes are on the top
Convert commandline options from definition list to sections
Add switches: -⁠?, -⁠encryption, -⁠nocreation, -⁠role, and -⁠trusted
Misc. copy editing, and updating information for newer version
Add some links to gfix and gbak documentation
Cross-links between sections
Added word-joiner in commandline switches between minus (-) and first character to ensure they aren’t broken up on word wrap

1.6

19 Jun 2020

MR

Conversion to AsciiDoc, minor copy-editing

1.5

11 Oct 2011

ND

Updated for Firebird 2.5.
Spelling errors corrected.

1.4

23 Mar 2011

ND

Added ODS 9.1 for Interbase 5.0 to the list of known ODS values.
Added reference to Managing Limbo Transactions in the gfix manual.
Corrected explanation of when an automatic database sweep is carried out, based on OIT and OST as opposed to OIT and OAT.As advised by Vlad Khorsun.

1.3

17 Feb 2010

ND

Formatting errors in the command line switches corrected.

1.2

14 Dec 2009

ND

A couple more minor corrections and spelling mistakes corrected.

1.1

30 Nov 2009

ND

Many corrections suggested by Paul Vinkenoog plus a general tidy up and a few more examples added.

1.0

29 Oct 2009

ND

Created a new gstat manual.

License notice

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the “License”); you may only use this Documentation if you comply with the terms of this License.Copies of the License are available at https://www.firebirdsql.org/pdfmanual/pdl.pdf (PDF) and https://www.firebirdsql.org/manual/pdl.html (HTML).

The Original Documentation is titled Firebird Database Statistics Reporting Tool.

The Initial Writer of the Original Documentation is: Norman Dunbar.

Contributor(s): Mark Rotteveel.

Selecting Tables To Analyse

Selecting Tables To Analyse

Including The System Tables & Indices

docnext count = 7

Record & Version Details

If You Have Database Corruption

Gstat Caveats

The -⁠t[able] Switch Can Cause Problems

The Shadow Count Seems Wrong

Document history

License notice

The `-⁠t[able]` Switch Can Cause Problems