Max processes

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the number of active processes in an Oracle database instance, and to alarm when the number approaches the maximum limit set within the database.
• Why: This job is important because it helps prevent a scenario where the database reaches its maximum allowable processes, which could potentially lead to denial of service or degradation in performance. By monitoring and alerting on thresholds, proactive actions can be taken to mitigate any risks associated with reaching the maximum number of processes.
• Manual checking: You can check the maximum processes manually in the Oracle database by issuing these SQL commands:

select to_number(p.value), to_number((select count(*) from v$process)) from v$parameter p where upper(p.name) like 'PROCESSES';

Functionality and Detection Logic

• This job executes SQL queries to determine the current number of processes and compares it to the maximum configured (‘PROCESSES’ parameter).
• The logic implemented in JavaScript further analyzes the fetched data to decide if a warning or alarm should be emitted based on predefined thresholds for remaining allowable processes.
• Warnings are issued when the process count is within 20 processes of the max limit; alarms are triggered when within 5 processes of the max limit.

Execution and Scheduling

• The job is scheduled to run every 15 minutes, ensuring frequent checks and timely detection of potential issues.
• Compatibility for this job is specifically tied to instances of Oracle databases without additional schema requirements (i.e., hasengine=‘NO’).

Reporting and Output

• The primary output of the job is a status report, with information on the current and maximum processes.
• Triggered statuses within the report could range from normal operation (status 0), near maximum processes with a warning (status 1), or exceeding safe limits with an alarm (status 2).

Details Table Display and Error Handling

• The job includes error handling where any encountered exceptions during the execution of the assessment logic will generate an alarm with the error message.
• A corresponding report template structured into chapters and presentations is used to present detailed results in table format:

• The table elucidated in the report provides an immediate visual overview of the max processes status based on live data fetched during each run.

By maintaining this job, database administrators ensure they can proactively manage and scale their Oracle environments effectively, avoiding potential outages or slow-downs caused by maxing out available database processes.