Session load

Job details

Name:	Session load
Platform:	Oracle
Category:	Performance
Description:	Records the number of active and inactive sessions over time.
Long description:	Records the number of active and inactive sessions over time. Can be configured to give alarms/warnings if the number of total sessions exceeds threshold values.
Version:	3.5
Default schedule:	10,30,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & ten_and_above=‘1′]

Parameters

Name	Default value	Description
keep data for	5	Number of days to provide statistics for.
exclude dbwatch login	YES	Exclude the dbwatch login from being collected in history tables.
return status (max total sessions)	0	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when number of sessions per instance exceeds parameter value “max total sessions” through a time period defined by parameter “time threshold (max total sessions”
time threshold (max total sessions)	120	A status value (ALARM – 2, WARNING – 1, or OK – 0) is returned when number of sessions per instance exceeds parameter value “max total sessions” through a time period defined by this parameter (in minutes).
max total sessions	10000	A status value (ALARM – 2, WARNING – 1, or OK – 0) is returned when number of sessions per instance exceeds this value through a time period defined by “time threshold (max total sessions)” parameter.

Job Summary

Purpose: The purpose of this job is to monitor the session load on Oracle databases by recording the number of active and inactive sessions over time.
Why: This job is essential to maintain optimal performance and stability of database operations. Monitoring sessions helps in identifying potential bottlenecks or overloads that may affect database performance. Alarms and warnings can be triggered if the number of sessions exceeds predefined threshold values, allowing timely intervention to prevent service disruptions.
Manual Checking: To manually check the session load, you can execute SQL to view active and inactive sessions in the database:
bc. SELECT status, COUNT FROM v$session GROUP BY status;

Technical Details

Scheduled Execution: The job runs at 10, 30, and 50 minutes past every hour by default.
Dependencies:
- Ensures the presence of specific historical and aggregation tables and procedures to handle data.
- Grants necessary permissions for monitoring via a predefined procedure.
Algorithm:
- The job involves SQL operations to track session count, filtering by DBWatch user sessions if configured.
- Data is stored in historical tables, and maintenance tasks ensure the clean-up of data beyond a specified retention period.
- Session statistics are aggregated daily into a separate table for trends analysis.
Monitoring Parameters: configurable parameters allow setting thresholds for triggering alarms or warnings if the number of sessions exceeds set limits.

Reports and Visualization

Session Statistics: Shows detailed logs on the number of sessions, distinguishing between total and active sessions across configured intervals.
- Aggregation Tables: offer insights into average, maximum, and minimum session counts, aiding in long-term analysis.
Charts: Visual representations of session trends over time facilitate quick understanding and responsiveness to emerging issues.

Implementation Notes

The job uses procedural code in Oracle’s PL/SQL for checking and updating session counts periodically.
Provisions are in place to handle exceptions and log errors securely, ensuring the robustness of the monitoring task.
Upgrade scripts are provided for seamless integration with different versions of DBWatch, assuring backward compatibility and streamlined updates to newer versions.

This job constitutes a critical part of database performance monitoring by providing essential data and alerts that support database administrators in proactive management and optimization of Oracle systems.