Session load Rac
Job details
| Name: |
Session load Rac |
| Platform: |
Oracle |
| Category: |
Cluster and Replication |
| Description: |
Records the number of active sessions over time (RAC only). |
| Long description: |
Task records the number of active sessions over a period of time (RAC only). Can be configured to give alarms/warnings if the number of total sessions exceeds threshold values. |
| Version: |
2.8 |
| Default schedule: |
5,10,15,20,25,30,35,40,45,50,55 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & iscluster=‘1′ & ten_and_above=‘1′ & _priv_read_gv_session = ‘1′] |
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 sessions over time specifically in Oracle Real Application Clusters (RAC).
- Why: This job is important because it helps in maintaining optimal performance and ensures that the number of sessions does not exceed the configured thresholds, which could potentially lead to performance degradation or system outages.
- Manual checking: You can manually check this information using the following SQL command:
select instance_name as "Instance name", number_of_processes_total as "Total processes", histr_date as "History date" from schema.dbw_session_load_rac_histr hist, gv$instance inst where inst.inst_id=hist.inst_id order by 3;
Job Descriptions and Details
- This monitoring job focuses on Oracle RAC environments to record total and active sessions.
- It provides alarms or warnings if the total sessions count exceeds certain threshold values.
- The scheduled recordings occur at intervals determined by the default schedule set in the configuration.
Tables and Stored Procedures
| Description |
SQL Code |
| Create historic tables for session information |
bc. CREATE TABLE dbw_session_load_rac_histr (inst_id number, number_of_processes_total number, number_of_processes_active number, histr_date date); |
| Create aggregate historic tables for summarized session data |
bc. CREATE TABLE dbw_sess_load_aggr_rac_histr (inst_id number, avg_number_of_processes_total number, avg_number_of_processes_active number, max_number_of_processes_total number, max_number_of_processes_active number, min_number_of_processes_total number, min_number_of_processes_active number, histr_date date); |
| Main procedure to calculate and store session statistics |
bc. CREATE OR REPLACE PROCEDURE dbw_session_load_rac (taskId number) AS… |
Reporting and Visualization
- Reports include various presentations such as session statistics over time, active sessions, and additional data points like host connections and user sessions.
- Visualizations include category charts and tables which help in quick analysis of session trends and outlier detection.
Compatibility and Installation
- This monitoring job is designed for Oracle RAC environments and checks for conditions like the instance being a cluster, having SQL engine capabilities, and specific database version requirements.
- Compatibility is determined via a complex query that checks for cluster configuration, Oracle 10g or above version, and necessary privileges.
Upgrade Procedures
- The upgrade includes enhancements in monitoring procedures to adjust thresholds and check conditions more precisely.
- Error handling and status updates have been improved in the newer version, providing better stability and reliability of session monitoring.
Parameter Checks and Historical Data Cleanup
- The job involves checking for various configurable parameters like historical data maintenance duration and inclusion of DBWatch login sessions.
- Clean-up operations are also part of the procedures to ensure that historical data does not grow unbounded and remains within manageable limits.
This detailed setup makes dbWatch Control Center a robust tool for managing Oracle RAC sessions, ensuring system stability and performance by proactive monitoring and management of database sessions.