Firebird Gstat Database Statistics Reporting Tool | Command-Line Switches

`-⁠RO[LE]`

Specifies the role for privileges — for example RDB$ADMIN, or another role providing the system privilege USE_GSTAT_UTILITY (and IGNORE_DB_TRIGGERS).

Syntax

-RO[LE] role_name

Note	Introduced in Firebird 3.0.

Gstat Examples And Interpretation

This section contains frequently executed statistics gatherings and explains the output.

Database Header

This option produces the least amount of output — unless you specify a single nonexistent table name with the [gstat-cmdline-table] switch — and is included with all other switches, so it is discussed first.

tux> gstat employee -header

Database "/opt/firebird/examples/empbuild/employee.fdb"
Database header page information:
        Flags                   0
        Checksum                12345
        Generation              184
        Page size               4096
        ODS version             11.1
        Oldest transaction      166
        Oldest active           167
        Oldest snapshot         167
        Next transaction        170
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      68
        Implementation ID       19
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Sep 25, 2009 12:50:24
        Attributes              multi-user maintenance

    Variable header data:
        Sweep interval:         20000
        *END*

The first line of output displays the database filename(s) and path.This can be useful to resolve a database alias to find out exactly where the database is located.As the employee database is a single-file database, only one file is displayed.Had this been a multi-file database, the end of the listing above would look like the following:

...
    Variable header data:
        Continuation file:       /u00/firebird/databases/multi_employee.fdb1
        Last logical page:       162

The details of the various header fields are described below:

Flags

Flags are not used on a database header page.

Checksum

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

Generation

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

Page size

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

ODS version

The On-Disk 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 new features will not be available.

Values you may currently see here are:

5.0 for Interbase 3.3
8.0 for Interbase 4.0
9.0 for Interbase 4.5
9.1 for Interbase 5.0
10.0 for Firebird 1.0 and Interbase 6.0
10.1 for Firebird 1.5
11.0 for Firebird 2.0
11.1 for Firebird 2.1
11.2 for Firebird 2.5
12.0 for Firebird 3.0
13.0 for Firebird 4.0
13.1 for Firebird 5.0

Transaction details

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

Oldest transaction

The transaction ID of what is known as Oldest Interesting Transaction or OIT.This is simply the ID of the longest running transaction that has so far not been completed by way of a hard commit.It may have been rolled back, or be in limbo, but if it has been committed, it is no longer interesting.

This value, along with the Oldest Snapshot Transaction, is used to determine if an automatic sweep of the database is required.

Note

There are two types of commits — commit and commit retaining.Only the first of these is a hard commit, which, when executed, renders the transaction as no longer interesting.Commit retaining leaves the transaction as still interesting.Some database utilities and/or tools that commit actually perform a commit retaining which can leave your database with a lot of still interesting transactions.

Under normal circumstances, a rollback is converted to a commit by undoing all its changes and then marking the transaction as committed, but this is not always possible (e.g. too many changes, or the transaction was explicitly marked as “no auto-undo”).

Oldest active

The ID of the oldest active transaction, or OAT.This value shows the transaction ID (TID) of the oldest transaction that is still running.A transaction is considered active if it has not been hard committed, is not in a state of limbo, or has not been rolled back.

Oldest snapshot

The ID of the oldest transaction which is currently not eligible to be garbage-collected.Any transaction with this or a higher ID cannot, yet, have old record versions removed by a sweep, for example.Normally, this is the same as the OAT above.The difference between this value and the OIT, if greater than the database sweep interval — assuming that automatic sweeping is not disabled — determines if an automatic sweep takes place.

Note

Many websites, books, and manuals (previously including this one) explain that the automatic sweep is activated when OAT - OIT is greater than the sweep interval.This is not the case as explained by Vlad Khorsun, one of the Firebird developers, who explained that it is when OST - OIT is greater than the threshold that the sweep is activated.

Next transaction

The next transaction started on the database will have this ID number.

Bumped transaction

Always 1, no longer used, and removed from the output in Firebird 3.0

If you discover that the difference between the OAT and the Next Transaction ID is growing larger and larger, something in your database is not committing properly and as such, an increasing number of garbage records may be building up.Eventually, you will see that the database startup times take longer and longer and the performance becomes slower and slower.Check the figures and if a problem is detected, you may be wise to run gfix to manually run a database sweep to clear out the garbage and restore normal working to the database.

You may wish to consult with the section entitledLimbo Transaction Managementin the gfix manual for details on how to detect and treat transactions in limbo.These may well be affecting the ability of the database sweep process in clearing out old redundant data from older uninteresting transactions.Limbo transactions are caused when a two-phase commit across multiple databases, fails for some reason.Limbo transactions are still interesting to the database and need to be committed or rolled back using gfix as the sweep processing cannot tell whether it is safe to do so without human intervention.

Sequence number

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

Next attachment ID

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 also alters this ID, except for only the [gstat-cmdline-header] option as that does not connect in a normal manner.

Implementation ID

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.

The implementation ID is used to determine if the database can actually be used on the hardware it is currently running on, or if there is some feature of the original hardware, where the database was created, that makes it incompatible with the current host system.

Shadow count

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.

Warning

Because of the inconsistency between what gstat reports and reality, it is best to use isql and the SHOW DATABASE command to view correct details of the shadow files.

Page buffers

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.

Database dialect

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.

Creation date

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

Attributes

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

no reserve: All pages will be filled to 100% and will be most useful on read-only databases.No space is reserved in each page for updates and/or deletions.
force write: Disk writes are not cached.They are written out to the hardware at the time of the write request.This is used mainly on Windows databases where the cache management system can lead to lost writes and database corruption.
shutdown: The database has been closed and cannot be used.
read only: The database is running in read-only mode.
multi-user maintenance: The database is closed for maintenance.Multiple connections are allowed by SYSDBA or the database owner only.
single-user maintenance: The database is closed for maintenance.Only one SYSDBA or database owner connection is allowed.

Other values may appear here, depending on the version of Firebird in use.

Variable header data

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.

Analyse Entire Database

The analysis of the entire database is the default for gstat.When used, all user tables and indices will be analysed and the gathered statistics reported.As the output will most likely be very large, it is advisable to pipe the output to a file:

gstat employee >employee.gst

The output will consist of an analysis of all user tables and all associated user indices.Interpretation of these results is covered below in the sections on analysis of data and index pages.