SQL statistics

Job details

Name:	SQL statistics
Platform:	Oracle
Category:	Performance
Premium package:	SQL Performance package
Description:	Collects SQL statements statistics (for container database) from V$SQLAREA and V$SQL_PLAN dynamic performance views.
Long description:	Collects SQL statements statistics from V$SQLAREA and V$SQL_PLAN dynamic performance views. To calculate similarities between SQL statements, the Oracle internal procedure UTL_MATCH.EDIT_DISTANCE_similarity is used.
Version:	2
Default schedule:	0 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & is_rds=0 & eleven_and_above=‘1′ & is_containerdb=‘1′]

Parameters

Name	Default value	Description
history threshold	7	The maximum number of days to keep statistics for, in the repository tables. Remember that it can quickly take several GB of space for one week of statistics. It is important to monitor space consumption in the Management part of dbWatch Monitor GUI.
dbwatch tablespace max size	5000	Maximum size (in MB) of dbwatch tablespace.
collect execution plan	YES	Collects execution plan statistics into the 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 tablespace.
SQL repository max size	80	Maximum space consumption (in percentage) of maximum size of the dbWatch tablespace for internal/repository objects that contain statistics for SQL statements.
min buffer gets	100	Minimum buffer gets value (per execution) for a SQL statement to be registered into history tables (repository tables).
check similarities	YES	Check similarities between SQL statements while collecting statistics. This option can be switched off if the procedure consumes too much time and resources. To calculate similarities between SQL statements, the Oracle internal procedure UTL_MATCH.EDIT_DISTANCE_similarity is used
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 tablespace (parameter “SQL repository max size”)

Job Summary

Purpose: The purpose of this job is to monitor and manage SQL statistics for Oracle databases, especially for container databases, ensuring efficient performance and space management in the dbWatch environment. It includes collecting, analyzing, and historical tracking of SQL execution data.
Why: This job is essential for maintaining database performance and operability by providing insights into SQL executions, identifying resource-intensive statements, and helping optimize SQL statement execution and database configuration. It also manages space within dbWatch to prevent excessive data accumulation.
Manual checking: To manually check and troubleshoot the specifics monitored and manipulated by this job, direct queries to different views and tables can be used, like:

SELECT * FROM V$SQLAREA WHERE SQL_TEXT LIKE '%your_interest%';

SELECT * FROM user_segments WHERE segment_name = 'dbw_sql_stat_current_#instance~id#';

Tables Generated and Dependencies

Generated Tables: This job creates various tables including but not limited to dbw_sql_stat_current, dbw_sql_stat_histr, dbw_sql_text_histr, dbw_sql_plan_histr, and dbw_internal_sql_size_histr, which are used to store current SQL statistics, historical statistics, SQL text, SQL execution plans, and statistics on internal object sizes respectively.
Dependencies: The job depends on Oracle’s dynamic performance views like V$SQLAREA and V$SQL_PLAN for collecting SQL statistics. It also depends on internal dbWatch procedures and tables such as dbw_sql_internal_size_proc and dbw_match_similarity_proc for managing size and matching similarities in SQL statements.

Procedures and Scripts

Main Procedures Used: dbw_sql_stat_proc (collects and manages SQL statement statistics) and dbw_sql_internal_size_proc (manages internal DB object sizes).
Critical Scripts:

ALTER DATABASE datafile ''autoextend on... - Adjusts file configurations to manage space automatically.

DELETE FROM dbw_sql_stat_current WHERE... - Removes outdated records based on custom rules to manage space and relevance.

Key Parameters and Their Roles

Parameter	Description
history threshold	Maximum number of days to keep statistics before they are considered outdated and are deleted.
dbwatch tablespace max size	The maximum allotted space in MB for the dbWatch tablespace, ensuring the dbWatch schema doesn’t grow uncontrollably.
collect execution plan	Determines if execution plans for SQL statements are collected.
collect internal statistics	Frequency in minutes for checking internal object sizes, to maintain and optimize database storage.
SQL repository max size	Maximum allowed storage space usage as a percentage for storing SQL statistics in the dbWatch tablespace.
min buffer gets	Minimum buffer gets per execution for a SQL statement to be considered significant enough to track.
check similarities	Whether to check for similarities between SQL statements, an operation that can help optimize SQL execution strategies but may consume more resources.

Optimization and Maintenance

This job plays a critical role in database optimization by ensuring that only relevant, performance-impacting SQL data is collected and stored, thus aiding in quicker diagnostics and tuning of the database.
It also maintains the dbWatch environment by automating space management tasks, ensuring that the system operates within the defined storage limits and performs optimally.