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.32
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’ & maj_version > 16 ]

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	NO	NOT IN USE. 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 job is designed to monitor and collect comprehensive performance statistics for SQL statements executed in PostgreSQL databases, specifically for versions later than 16.
Why: Monitoring SQL performance is critical for database health and optimization. It allows database administrators to pinpoint inefficient queries and understand the workload distribution, which in turn can help in tuning the database for better performance. Inadequate monitoring might lead to performance bottlenecks, leading to slower response times and potentially disruptive downtimes.
Manual checking: To manually check SQL statistics you can query the pg_stat_statements view and other related PostgreSQL system catalogues. Tracking and managing the extension can be done with the following commands:

CREATE EXTENSION pg_stat_statements;
DROP EXTENSION pg_stat_statements;
SELECT pg_stat_statements_reset();

The job involves several SQL and PL/pgSQL scripts which collectively work to gather, organize, and manage SQL execution statistics.
A large variety of statistics are collected, including the number of calls, total execution time, rows processed, and block activities (hits, reads, writes).
The job constructs several tables for handling the current, historical, and text data of SQL queries as well as their execution statistics.
It is equipped with mechanisms to periodically check the size of its own footprint within the database and to clean up old or outdated statistics, ensuring that the database does not become clogged with monitoring data.
Dependencies include several custom objects like `dbw_sql_stat_current` which holds current SQL statistics, and others like `dbw_sql_stat_histr` and `dbw_sql_text_histr` which store historical data and query texts respectively.
The job makes use of scheduled tasks (cron job notation) which indicates it automatically collects data every 5 minutes.

The job checks and, if necessary, automatically installs the `pg_stat_statements` extension during installation, which is essential for capturing SQL query statistics.
It requires modifications to the PostgreSQL configuration file to track activities and load necessary libraries:
bc.
track_activity_query_size = 2048
shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.track = all
pg_stat_statements.max = 10000

The dbWatch Control Center job provisions a reporting template which facilitates the visualization of internal data (like SQL handles and table sizes).
Administrators can view reports that provide insights into SQL statement activity and database internals, helping them make informed decisions about performance tuning and capacity planning.

The job includes a mechanism to manage the history and limit the storage used by monitoring scripts, thereby preventing any negative impact on the overall database performance due to the size of collected statistical data.
It has built-in error handling and checking mechanisms to ensure that database administrators are alerted in scenarios where the monitoring setup is incorrect or if certain parameters are not set as required.