Memory session load for RAC

Job details

Name:	Memory session load for RAC
Platform:	Oracle
Category:	Cluster and Replication
Description:	Records the load memory of RAC active sessions over time.
Long description:	Task records the load memory of RAC active sessions over time.
Version:	2.4
Default schedule:	10,30,50 * * *
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

Job Summary

Purpose: The purpose of this job is to record and analyze the memory load of active sessions in Oracle Real Application Clusters (RAC) over time.
Why: This job is critical as it helps in monitoring the distribution and consumption of memory resources among active sessions in Oracle RAC environments. Understanding the memory usage patterns can help in optimizing performance and diagnosing potential issues in a cluster setup.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT a.inst_id AS "Instance number",
NVL(a.username,'(oracle)') AS "Username",
NVL(a.module,' ') AS "Module",
a.program AS "Program",
Trunc(b.value/1024) AS "Memory kb"
FROM gv$session a,
gv$sesstat b,
gv$statname c
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value;

Detailed Description

The task checks for instances that meet specific criteria such as being part of an Oracle cluster and having essential privileges and conditions.
The monitoring script captures total and active session counts, differentiating by instance.
An exception handling mechanism logs and notifies errors to ensure reliability and traceability of the script’s operations.
The task includes a self-dependency, signifying it must successfully execute autonomously for it to rerun as scheduled.

Report Overview

Title: Session Load
Description: Displays session statistics to show memory load, aiding in resource monitoring and planning.
Frequency: Hourly

Table Structure for Reported Data

Column Name	Data Type
Instance number	Number (Int)
Username	String
Module	String
Program	String
Memory kb	Number (Int)

Output Example

Instance number	Username	Module	Program	Memory kb
1	user1	appModule	SQLplus	2048
2	(oracle)	engineTask	JavaApp	1024

Presentation: The report visualizes session statistics through a data table, focusing on the memory in kilobytes used by different sessions, which is crucial for assessments of resource usage.

Installation Details

The job employs a PL/SQL script to monitor and update session metrics specific to Oracle RAC environments.
Replacements or modifications during installation are tightly controlled to preserve the integrity and effectiveness of the monitoring process.

Versioning and Source

Version: 2.4
Development and Maintenance by: dbwatch.com

This job provides critical insights into the distribution and performance of memory across active Oracle RAC sessions, facilitating effective database management and operational planning in complex cluster environments.