firebird gstat

Firebird Database Statistics Reporting Tool Norman Dunbar Version 1.6, 19 June 2020 Table of Contents 1. Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 2. Command-line Switches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 3. Gstat Examples And Interpretation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 3.1. Database Header. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 3.2. Analyse Entire Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 3.3. Analyse Data Pages Only. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 3.4. Analyse Index Pages Only. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.5. Selecting Tables To Analyse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 3.6. Including The System Tables & Indices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 3.7. Record & Version Details. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.8. If You Have Database Corruption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 4. Gstat Caveats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 4.1. The -t[able] Switch Can Cause Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 4.2. The Shadow Count Seems Wrong. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Appendix A: Document history. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Appendix B: License notice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Table of Contents 1 Chapter 1. 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 does not connect to the database as other utilities do, instead it opens the database file(s) directly and reads through the raw data. Because of this, gstat is not transactionally aware and some of the statistics it gathers may include data that have 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. Chapter 1. Introduction 2 Chapter 2. 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] Some documentation advises that gstat can be called as follows: gstat [switches] database_name However, although it does work this way, problems arise when the -t[able] switch is used. 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 because gstat works at the physical file level as opposed to making a database connection to the server — it reads the database file directly. If gstat is called with an invalid switch, or with the new -? switch from Firebird 2.5 onwards, the following is displayed to remind you of the valid ones. Only the short form of the switches is displayed, unfortunately. ./gstat -? usage: gstat [options] <database> or gstat <database> [options] Available switches: -a analyze data and index pages -d analyze data pages -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) -z display version number option -t accepts several table names only if used after <database>  In Firebird versions prior to 2.0, the -l[og] switch could also be used. This reported on the details of the logging page(s) within the database. The logging pages have not been used for some time and the switch has now been removed from gstat. Chapter 2. Command-line Switches 3  The -fetch switch is only available from Firebird 2.5 onwards. These switches are described below. -a[ll] This is the default switch and is equivalent to -h[eader] -d[ata] -i[ndex]. In the absence of both -d[ata] and -i[ndex], gstat will run as if both had been specified alongside -h[eader]. -d[ata] Specifying this switch causes gstat to analyse every user table within the specified database. User indices, system tables and system indices are not analysed. -h[eader] This switch displays statistics about the database itself, then exits. The header information is also displayed when any other switch is used — so you always get database header details in your output. -i[ndex] Specifying this switch causes gstat to analyse every user index within the specified database. User tables, system indices and system tables are not analysed. -s[ystem] This switch is a modifier and alters the output from the -d[ata] or -i[ndex] switches by including the system tables (or indices) in addition to the user defined tables (or indices). Using this switch on its own is equivalent to calling gstat with -a[ll] -s[ystem] specified. When run, this switch lists statistics for the various RDB$ tables and indices, and if running against Firebird 2, for the various MON$ tables and indices too. -r[ecord] The -r[ecord] switch is a modifier for the -d[ata] and -s[ystem] switches. It adds data about the average record and version lengths for any data tables (user and/or system) analysed. This switch has no effect on the -i[ndex] switch. -t[able] This switch allows you to analyse a table, or list of tables, and any indices belonging to the specified tables. See the caveats section below for some potential problems with this switch 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 be all in upper case and each table is separated by a space. It is also possible to use double quotes to cause gstat to analyse a table that doesn’t have its name in upper case. It is not necessary to specify the -i[ndex] switch as any indices on the specified tables will be analysed. The database header information is also displayed. -u[sername] Allows the user name of the SYSDBA or database owner user to be specified. This need not be Chapter 2. Command-line Switches 4 supplied if the ISC_USER environment variable exists and has a correct value for the user name, or if you are logged on to the server as a privileged account.  A privileged account is one of the following: • root • firebird • interbase • interbas (without the final 'e') If you log in to the server with one of these accounts, you will automatically receive SYSDBA privileges. If you use a different account, you may be required to supply a