Max processes

Job details

Parameters

Job Summary

• Purpose: This job monitors the maximum number of processes used in an Oracle database instance to manage capacity and performance.
• Why: Effective monitoring of process usage helps prevent the database from exceeding its configured limits, potentially leading to performance degradation or system halts. By keeping track of both user and background processes, it ensures the database operates within safe process limits.
• Manual checking: You can check the maximum number of processes manually by querying the Oracle view and tables involved:

SELECT * FROM v$parameter WHERE name = 'processes';

SELECT count(*) FROM v$session WHERE username IS NULL; -- For background processes

SELECT count(*) FROM v$session WHERE username IS NOT NULL; -- For user processes

Tables and Procedures Definition

• Tables Created:
• dbw_pros_load_histr_#instance~id#: Holds historical data about user and background processes.

• Procedures:
• Procedure dbw_pros_load_#instance~id#: This procedure is responsible for collecting data about current processes, storing historical records, maintaining the history by cleaning up old records, generating daily aggregates, and evaluating the thresholds to issue alerts.

Implementation Details and Process Flow

This job includes the execution of several SQL commands to:

• Create necessary tables to store historical and aggregated data about process usage.
• Use PL/SQL blocks to manage data collection, aggregation, and cleanup involving these tables:

Process Flow:

1. Gather parameters such as retention period (keep data for), warning, and alarm thresholds.
2. Fetch current configuration of the maximum processes allowed (from v$parameter).
3. Record current counts of user and background processes (from v$session).
4. Store the records into historical table dbw_pros_load_histr_#instance~id#.
5. Clean up historical data beyond the retention period.
6. Generate daily aggregates (previous day) if not already done.
7. Insert aggregated data into the table dbw_pros_load_aggr_histr_#instance~id#.
8. Evaluate current process load against the defined thresholds and update the job status accordingly to reflect potential warnings or alarms.

Dependency Handling

• Dependencies: Ensures that historical and aggregated data tables as well as the main procedure exist and are operational.
• If any of the creation or execution steps fail, the job is designed to clean up inconsistent or partial data to prevent database pollution.

Reporting Capabilities

• Reports Generated:
• Process history: Visualizes detailed history data of user and background processes, showcasing trends over time.

Each report leverages data visualizations through charts categorized by date and differentiated by process types (user vs. background), providing intuitive insights into the database’s operational status.