SQL statistics

Job details

Name:	SQL statistics
Platform:	Oracle
Category:	Performance
Premium package:	SQL Performance package
Description:	Collects SQL statements statistics (for pluggable 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_pluggdb=‘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

Purpose: This monitoring job is primarily designed to manage and analyze SQL statement statistics and tablespace monitoring in Oracle databases, focusing on both current and historical SQL performance data.

Why the Job is Important

The job is crucial for maintaining optimal database performance by:
- Monitoring SQL execution plans and their performance.
- Managing internal statistics and space within the dbWatch environments.
- Keeping a check on similar SQL statements to optimize and avoid redundant resource usage.
With proactive monitoring, potential performance bottlenecks and issues related to SQL execution can be identified and remediated early, thereby helping in maintaining the overall health and performance of the Oracle databases.

Manual Checking

Manual equivalent checks can include querying data from v$sqlarea, dba_data_files, and related performance schemas. Examples include:

select * from v$sqlarea where sql_id='[sample_id]';

select file_name, tablespace_name from dba_data_files;

Parameters Summary (Without details)

History threshold: Defines the number of days to keep SQL statistics.
dbWatch tablespace max size: Sets the maximum size for the dbWatch tablespace.
Collect execution plan: Toggles whether to collect execution plan statistics.
Collect internal statistics: Frequency of internal size checks.
SQL repository max size: Maximum size allowance for SQL repository as a percentage of dbWatch tablespace.
Min buffer gets: Threshold for logging SQL statements based on buffer gets.
Check similarities: Enables similarity check between SQL statements.
Return status: Indicates the different statuses like ALARM, WARNING, or OK based on set thresholds.

Implementation Overview

Creates necessary tables and indices for storing current and historical SQL data.
Executes procedures to collect data on SQL execution, including stats like disk reads, executions, fetches, buffer gets, etc.
Monitors size and growth of critical internal structures and manages space allocation in the database environment, including auto-extension settings.
Manages and purges historical data based on defined thresholds to maintain a balance between data availability and storage requirements.

Dependencies and Scheduled Tasks

This job has multiple dependencies on internal dbWatch tables, procedures, and existing Oracle views like v$sqlarea and v$sql_plan.
Various cleanup and validation tasks are scheduled to ensure data consistency and integrity.

Reporting

A dbWatch report is generated which provides valuable insights into SQL statement performance, space utilization, and internal operations, aiding DBAs in making informed decisions about database tuning and maintenance.

Benefits and Impact

Ensures efficient utilization of resources by monitoring and optimizing SQL executions.
Helps in maintaining a clutter-free and performance-optimized database environment.
Facilitates informed decision-making through comprehensive reporting on SQL activities and database health.