FB TraceManager 3 - Documentation

Database Monitoring - Database Statistics Monitoring

Database Monitoring - Database Statistics Monitoring

Previous topic Next topic  

Database Monitoring - Database Statistics Monitoring

Previous topic Next topic  

The Database Statistics Monitoring facility fetches statistics about table/index pages and back-record versions. This is exactly the same output as when using Firebird's command-line utility gstat with its various command-line options (-a, -d, -i, -r). FB TraceManager fetches data without the use of gstat, parses the output and offers the usual after-parsing capabilities. Furthermore, you can recompute index selectivity or rebuild indexes directly from FB TraceManager.

 

New in V3.5: Firebird 3 support has been added.

 

To start continuous database statistics monitoring for the connected database, unselect the Paused option
If you want to manually fetch database statistics from the database, click the Refresh All button
You can also load a gstat output from a file instead of fetching the database statistics from a live database

 

 

Raw Output

 

The raw output is identical to running gstat on the command-line. For example:

 

fbtm_dsm_raw_output.zoom25

 

 

Parsed Output

 

Additionally, FB TraceManager parses the output and adds the result in the Tables and Indexes grid with capabilities to filter, sort data and apply aggregations in the grid footer per-column. You can export the result into a CSV file and you can persist snapshots of fetched data in the FBTM3.fdb database.

 

Tables

 

fbtm_dsm_parsed_output_tables.zoom25

 

Indexes

 

fbtm_dsm_parsed_output_indexes.zoom25

 

This makes it easy to read and furthermore, FB TraceManager also provides "derived" attributes, like Total Duplicates [%] and a lot more. A customizable color management facility, with meaningful default-settings, underlines suspicious database statistics.

 

New in V3.0: A new SEVERITY column for both Tables and Indexes has been added, which gets automatically filled with various levels, in case something suspicious has been detected. This new information also gets persisted in the database.

 

If fetched statistics shows, that index statistics (selectivity) is out-of-date (e.g. Index Statistics Calculated vs. Index Statistics Fetched differs too much) or if you are in need to entirely rebuild one or more indexes, you can do that directly from FB TraceManager with the Index Management facility. You can even drop indexes. All index management operations map to regular DDL statements.

 

Hint: Index Management is not available in the Trial Edition.

 

If you are fetching from a live database (not loading from a file) and you have selected the Include Index Metadata option, additional index meta data is available. This also includes information about the index segments, what table fields are included in the index. You can use the Index Values Distribution | Copy SQL To Clipboard context-menu item, to build an index values distribution SQL statement, to be executed in your favorite Firebird database development tool. For example, this will give you a hint on the distribution of the index key values. An example for the generated SQL statement for a single field foreign-key constraint index:

 

select ACCOMMODATIONTYPE_ID, count(*) as rec_count, count(*) - 1 as maximum_duplicates from ACCOMMODATION group by ACCOMMODATIONTYPE_ID order by count(*) desc;

 

An exemplary output:

 

ACCOMMODATIONTYPE_ID REC_COUNT  MAXIMUM_DUPLICATES
--------------------------------------------------
3                    36434      36433             
2                    36261      36260             
1                    36117      36116             

 

 

Parsed Output Storage

 

If you wish, the parsed output can be persisted in the FBTM3.fdb database in tables called DST_TABLE_PARSED and DST_INDEX_PARSED. This allows you to query/inspect database statistics   at a later point in time. To enable parsed output storage, you have to select the Log to database option in the Settings flyout tool panel.

 

 

Customization and Settings

 

The database statistics monitoring module is highly customizable. Simply click the Settings flyout tool panel to get in touch with various options.

 

Settings

 

fbtm_dsm_settings.zoom25

 

Use the Paused option to enable/disable automatic fetching of database statistics with the given Refresh interval. The user-definable colors on various database statistics data gives you a way to spot questionable conditions more easily. Use the Statistic Data Options to define what kind of data should be included when fetching database statistics. Use the Include Index Metadata option to fetch additional index meta data from the connected live database. This data is used to extend database statistics data which isn't available in the gstat output. This includes:

 

Is the index used in a primary/foreign key constraint
Is the index defined as unique
How many and what fields are part of the index
And more ...

 

Furthermore, fetching extended index meta data even allows you to compare index statistics/selectivity calculated from the gstat output with fetched values from RDB$INDICES.RDB$STATISTICS which can help you to spot indices with out-dated statistics. You can use Recompute Statistics on selected index rows to recompute the selectivity (= RDB$INDICES.RDB$STATISTICS)

 

Changed settings are stored and re-loaded every time you open the database statistics monitoring area when connecting to a database.

 

Filter Tables

 

fbtm_dsm_filter_tables.zoom25

 

In the Filter Tables area, you can define a table filter before fetching database statistics from the database. Click the Refresh button to load a list of the available tables. Select/Unselect table names as you like. If no tables are selected, then the table filter is omitted.

 

 

 

See also

Register Project

Database Monitoring

Database Monitoring - Header Page Monitoring

Database Monitoring - Monitoring (MON$) Tables