SQL statistics
Job details
| Name: | SQL statistics |
| Platform: | Oracle |
| Category: | Performance |
| Premium package: | SQL Performance package |
| Description: | Collects SQL statements statistics 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_multitenant=‘0′] |
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
The monitoring job outlined is intended for use with the dbWatch Control Center, specifically designed to manage and monitor SQL statistics in Oracle database instances. This is a comprehensive task that involves various measurements, collections, and reporting functionalities focused on SQL query performance and their associated resources within a database.
Purpose
- Monitors and collects statistics regarding SQL queries, their plans, execution details, and related metrics from Oracle databases.
- Manages the size and growth of internal dbWatch tablespaces to mitigate risks related to space consumption.
- Analyzes SQL statements for similarities, helping optimize the management of database resources.
Why This Job Is Important
- Ensures SQL queries are executing efficiently and using resources optimally, which is crucial for maintaining overall database performance and stability.
- Helps manage space effectively in the database, preventing outages or performance degradation due to space issues.
- Monitoring similarities in SQL statements can help in optimizing query performance and reducing redundant resource usage.
Manual Checking
To manually check SQL statistics directly from the Oracle database, the following SQL queries can be executed:
select * from v$sqlarea;select * from dba_data_files where tablespace_name = 'YOUR_TABLESPACE_NAME';Configuration Details
The job consists of varied parameters and scheduled executions, which are triggered by specific conditions or time intervals as follows:
Key Parameters
- History Threshold: Determines how long statistics are retained.
- dbWatch Tablespace Max Size: Maximum permissible size of the dbWatch specific tablespaces.
- Collect Execution Plan: Whether execution plans should be collected for SQL queries.
- SQL Repository Max Size: Maximum percentage of dbWatch tablespace that can be allocated to storing SQL query statistics.
Dependencies
The job relies on several components within the database:
- SQL text and history tables for storing SQL scripts and execution details.
- Specific procedures that are triggered based on certain conditions or thresholds.
Automated Processes
- SQL queries for collecting statistics are automatically run at regular intervals.
- Storage size checks and cleanup procedures are executed to manage space efficiently.
- Data collection related to SQL query execution plans, buffer gets, and more, are also part of this job’s automated processes.
Reports Generated
A report template is included within the job definition, designed to generate comprehensive insights into:
- Segment sizes within the database specifically associated with SQL statistics.
- Active, inactive, new, and historical SQL handles and their execution times.
- Internal data regarding table sizes and their change over time.
Report Feature Details
- Various presentations within the report outline segment size, handle counts, and execution specifics.
- Reports are designed to be detailed and can be reviewed periodically according to a schedule, which can be predefined or customized as per user requirements.
This monitoring job is crucial for any organization using Oracle databases to ensure that their SQL environment is running efficiently, backed by data-driven insights into performance and storage metrics.