Session load
Job details
| Name: | Session load | |
| Platform: | Oracle | |
| Category: | Performance | |
| Description: | Records the number of active sessions over time. | |
| Long description: | Task records the number of active sessions over a period of time. | |
| Version: | 1.7 | |
| Default schedule: | 10,30,50 * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘8%’ | version like ‘9%’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| keep data for | 14 | Number of days to provide statistics for. |
| exclude dbwatch login | YES | Exclude the dbwatch login from being collected in history tables. |
Job Summary
- Purpose: The purpose of this job is to monitor and record the number of active Oracle database sessions over time, specifically targeting instances running Oracle versions 8 and 9.
- Why: This job is critical as it helps in analyzing the workload and performance of the database by keeping track of session counts. High numbers of active sessions can indicate heavy usage or potential performance bottlenecks, affecting overall database efficiency and user experience.
- Manual checking: To check this manually in the database, you can use the following SQL command:
SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE';Job Description
The “Session load” monitoring job within the dbWatch Control Center is designed to gather and maintain historical data about Oracle database sessions, particularly focusing on:
- Total sessions
- Active sessions
The job is set to execute every 20 minutes (at 10, 30, and 50 minutes past each hour). The recorded session metrics are maintained for a configurable number of days (default is 14 days) and are exclusive of sessions initiated by dbWatch itself, unless configured otherwise.
Job Components and their Functions
Data Tables and Aggregated History Tables
- dbw_session_load_histr_#instance~id#
- This table holds the raw session data gathered during each job execution.
- dbw_session_load_aggr_histr_#instance~id#
- This table aggregates session statistics for meaningful historical insight, including average, maximum, and minimum session counts per day.
Dependency Management
- The job ensures clean-up operations on derivative tables are tightly managed on job failures, thereby maintaining data integrity and consistency.
Pre-implementation Steps
- Ensuring necessary privileges are granted for dbWatch to read from system views.
Job Implementation
- Session data is collected and processed either excluding or including the dbWatch initiated sessions based on configuration. Aggregation of historical data happens only if the last aggregation was done more than a day ago.
- The job also updates task status in dbWatch, and manages error logging if exceptions occur.
Reporting Capabilities
The job includes comprehensive reporting features within dbWatch:
- Tabular and graphical representation of session trends over time.
- Current session details by host, users, and programs, showcasing the distribution and potential hotspots within the database environment.
Example Reports
| Report Title | Description |
|---|---|
| Session statistics | Details total and active session trends over the selected period. |
| Current connections from hosts | Lists host machines with the current highest number of sessions. |
| Current connection per username (schema) | Shows which database users are generating the most sessions. |
| Current application types | Highlights the client applications contributing to session counts. |
| Aggregated Logon history | Summarizes daily statistics like average and peak sessions. |
This detailed monitoring and reporting setup provided by the “Session load” job are vital in proactive database health checks and performance optimization initiatives, making it a critical component in the arsenal of tools available in the dbWatch Control Center for Oracle databases.