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=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”) |
Monitoring Job Summary: SQL Statistics Collection for Oracle
This job is designed to monitor SQL statements intensively to collect statistics about their execution, which enables better performance analysis and capacity planning.
Purpose
This dbWatch job is responsible for:
- Collecting and storing SQL execution and plan statistics into its repository.
- Monitoring and managing the size and growth of the statistics repository to prevent excessive storage use.
- Analyzing similarities between SQL statements to identify possible reuses and optimizations.
Importance of the Job
- Ensuring optimal performance of SQL executions by keeping an eye on various execution metrics.
- Proactively managing storage usage dedicated to SQL statistics, which is crucial for maintaining system responsiveness and avoiding unexpected storage overflows.
- Analyzing SQL statement similarities can surface opportunities for optimization, potentially reducing system load and improving response times.
Manual Checking
To verify the collected SQL statistics manually, you can run the following SQL command directly on the Oracle database:
SELECT * FROM v$sqlarea;Job Configuration and Parameters
The job uses a collection of parameters to tailor its operation, these include thresholds for history retention, size limits for tablespace, and controls over whether execution plans and internal statistics should be collected.
- Example parameter settings:
- History retention set to 7 days.
- Max tablespace size set to 5000 MB.
- Execution plan collection enabled.
Data Dependencies and Cleanup
The job relies on several internal and external dependencies to function:
- Internal procedures for collecting and cleaning data.
- Standard Oracle views like v$sqlarea and v$sql_plan.
- Custom tables and indexes to store historical data.
Upon failure, the cleanup procedures are triggered to maintain system integrity and prevent partial data leftovers.
Reporting and Visualization
- The dbWatch Control Center provides a reporting module to visualize the collected data:
- Trend reports on internal segment sizes.
- Detailed views on active, inactive, and archived SQL handles.
- Space usage reports for different categories, like current SQL statistics, historical data, and similarities metrics.
Implementation Details
- SQL statements are persisted into custom tables designed to retain various metrics over time, such as the number of executions, disk reads, and buffer gets.
- Indexes are created on critical columns to speed up data retrieval and analysis operations.
- Procedures are in place for both collecting new statistics and removing outdated entries beyond retention thresholds.
Additional Considerations
The job’s performance itself may impact the overall system; therefore, monitoring the load and execution times of the job is essential to ensure it does not negatively impact the database performance it is meant to optimize.