Firebird Gstat Database Statistics Reporting Tool

Introduction

Gstat is one of the database utilities supplied with Firebird.It is used to display statistical details about the contents of a database.Gstat is not transactionally aware, and some of the statistics it gathers may include data that has been deleted, for example, by normal database transactions.

In this manual, we will discuss:

Command line options for gstat.
gstat commands and their parameters.
Running gstat and interpreting the results.
Some caveats, gotchas and foibles of gstat.

Note

For the header information, gstat reads the database file directly.For other statistics, gstat may connect to the database using the embedded engine or to a local Firebird server.If the database is currently opened by a Firebird server running in SuperServer (or ThreadedDedicated) mode, gstat may fall back to connecting using XNET or TCP/IP to the local server, but this may not always work when using a non-default configuration for the IPC name or TCP/IP port.

In that case, to use gstat, you may need to terminate any open connections, or use the equivalent commands via the service manager (e.g. through fbsvcmgr).

Command-line Switches

Gstat should be run as either root or the Firebird user.This is because the default operating system permissions when a new database is created, are such that only the owner — firebird — has access to the database file(s).Even members of the firebird group have no read access by default.

Gstat is normally called as follows:

gstat database_name [switches]

The usage instructions shows that gstat can also be called as follows:

gstat [switches] database_name

In practice, switches can occur both before and after the database name.However, the [gstat-cmdline-table] switch can only be used with multiple table names if it is placed after the database name.

The database name cannot be a remote database, it must be local, but it can be an alias for a local database.The reason that it must be local is that obtaining the header information of gstat is done at the physical file level as opposed to making a database connection to the server — it reads the database file directly.Depending on the Firebird version and OS platform, other operations may connect to the local server or use the embedded engine to gather information.

If gstat is called with an invalid switch, or with [gstat-cmdline-help], the following is displayed to remind you of the valid switches.Only the short form of the switches is displayed.

gstat Usage information for Firebird 5.0, with links to relevant sections

./gstat -?
usage:   gstat [options] <database> or gstat <database> [options]
Available switches:
    -a      analyze data and index pages
    -d      analyze data pages
    -e      analyze database encryption
    -h      analyze header page ONLY
    -i      analyze index leaf pages
    -s      analyze system relations in addition to user tables
    -u      username
    -p      password
    -fetch  fetch password from file
    -r      analyze average record and version length
    -t      tablename <tablename2...> (case sensitive)
    -role   SQL role name
    -tr     use trusted authentication
    -z      display version number
option -t accepts several table names only if used after <database>

Note	The links are not present in the actual `gstat` output. Firebird 1.0 and 1.5 also had a `-⁠l[og]` switch, which would display information about log pages.Given Firebird does not use log pages, that switch was removed in Firebird 2.0.

The gstat usage instruction only displays abbreviated forms of switches.In the following sections, we will show the full switch names, with the optional part in square brackets (‘[’ and ‘]’).For example, for [gstat-cmdline-all], this means that you can enter -⁠A, -⁠AL and -⁠ALL.Switch names are case-insensitive.

`-⁠A[LL]`

This is the default switch and is equivalent to -⁠d[ata] -⁠i[ndex].In the absence of [gstat-cmdline-data] and/or [gstat-cmdline-index], or [gstat-cmdline-header], gstat will run as if -⁠a[ll] was specified.

`-⁠T[ABLE]`

This switch allows you to analyse a table, or list of tables, and any indices belonging to the specified tables.

Syntax when placed before the database name

-T[ABLE] table_name

Syntax when placed after the database name

-T[ABLE] table_name [table_name ...]

If you want to specify multiple tables, the -⁠t[able] switch must be specified after the database name.See [gstat-caveats] for some potential problems with this switch — especially before Firebird 2.5 — and an example of how it should be used.

The -⁠t[able] switch should be followed by a list of the table names you wish to analyse.The list must match the table name exactly as stored in the metadata.In other words, case-insensitive table names (i.e. referenced with unquoted identifiers) must be entered in uppercase, while case-sensitive table names (referenced with quoted identifiers) must be entered exactly as-is.If a table name contains spaces or certain special symbols, you may need to enclose the table name in double or single quotes, and/or escape certain symbols, following the requirements of the shell/command prompt you use.

When combined with the [gstat-cmdline-data] switch, only the data pages of the specified tables will be analysed.When combined with the [gstat-cmdline-index] switch, only the indices of the specified tables will be analysed.When neither -⁠d[ata] or -⁠i[ndex] is specified, both data pages and indices will be analysed, as if [gstat-cmdline-all] is specified.To obtain information on system tables, the [gstat-cmdline-system] switch must be specified explicitly.

The database header information is also displayed.