SQL statistics

Job details

Name:	SQL statistics
Platform:	Postgres
Category:	Performance
Premium package:	SQL Performance package
Description:	Collects performance statistics for the SQL statements.
Long description:	Collects performance statistics for SQL statements from pg_stat_statements view. During installation it checks if the pg_stat_statements extension is created, and create it if it is not. For tracking which queries get executed in your database you need to add the following entries to your postgresql.conf file:
Version:	1.2
Default schedule:	0,5,10,15,20,25,30,35,40,45,50,55 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & newer_than_ninefour = 1 & maj_version < 13]

Name	Default value	Description
history threshold	7	The maximum number of day to kept statistics for in the historic tables.
dbwatch database max size	5000	Maximum size (in MB) of dbwatch database.
collect execution plan	YES	Collects execution plan into history table if set to “YES“.
collect internal statistics	30	How often (in minutes) size of internal objects should be checked. “SQL statistics” job collects a large amount of data in the dbWatch schema tables, so it is important to keep track of space usage in the dbWatch database.
SQL repository max size	80	Maximum space consumption (in percentage) of maximum size of the dbWatch database for internal/repository objects that contain statistics for SQL statements.
min logical reads	100	Minimum logical reads value (per execution) for a SQL statement to be registered into history tables (repository tables).
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when reached maximum space consumption (in percentage) of maximum size of the dbWatch database (parameter “SQL repository max size”)

Purpose: The SQL Statistics job collects and analyzes performance statistics for SQL statements executed on a PostgreSQL database.
Why: This job is important because it allows database administrators to monitor and optimize the performance of SQL queries by providing insights into query execution and resource usage.
Manual checking: To manually check SQL statistics, you can query the PostgreSQL’s `pg_stat_statements` view using the following command:

SELECT * FROM pg_stat_statements;

The job collects data such as total execution time, number of executions, and rows returned for each SQL statement.
It utilizes the `pg_stat_statements` module, which must be enabled in the PostgreSQL configuration.
Additional settings such as `track_activity_query_size` and `shared_preload_libraries` need to be configured to fully utilize the capabilities of the monitoring job.

Parameter Name	Description
history threshold	Defines the number of days to retain SQL statistics in historical tables.
dbwatch database max size	Sets the maximum size in MB for the dbWatch database to prevent excessive space usage.
collect execution plan	Determines whether to collect the execution plan for SQL queries and store them in the history table.
collect internal statistics	Indicates how often to check the size of internal objects within the dbWatch database.
SQL repository max size	Establishes the maximum space usage for the SQL repository as a percentage of the dbWatch database’s maximum size.
min logical reads	Sets the minimum logical reads required for a SQL statement to be recorded.
return status	Specifies the return status such as ALARM, WARNING, or OK based on certain conditions being met regarding space usage.

The pg_stat_statements extension must be installed and properly configured, which includes modifying settings in the `postgresql.conf` file.
Provides automated setup scripts to ensure the extension is created if it doesn’t exist.

dbw_sql_stat_current: Tracks the current SQL execution statistics.
dbw_sql_stat_histr: Handles the historical execution statistics storage.
dbw_sql_text_histr: Maintains historical records for query texts.
dbw_internal_sql_stat_histr: Used for storing internal SQL statistics records.
dbw_match_similarity_tab: Tracks similarities between SQL queries for optimizing performance handles.

Monitors SQL statements and their impact on the database.
Proposes creation of various helper tables and functions to manage statistics.
Determines the new and historical SQL registrations.
Implements monitoring of not just SQL statements but also internal database sizes and resource statistics.

Includes detailed templates for reporting the SQL performance statistics.
Offers optional clean-up functions to maintain database hygiene by removing outdated data sets.