Top user memory usage

Job details

Name:	Top user memory usage
Platform:	Oracle
Category:	Performance
Description:	Check can be used to trace memory usage per server-process (session).
Long description:	This task check can be used to trace memory usage per server-process (session).
Version:	2.5
Default schedule:	6,16,26,36,46,56 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & ten_and_above=‘1′]

Parameters

Name	Default value	Description
warning threshold	300	Warning threshold for the amount of memory used by single session (PGA/UGA size) in MB.
alarm threshold	1000	Alarm threshold for the amount of memory used by single session (PGA/UGA size) in MB.
min user memory size	20	The minimum size of user memory usage per session registered in the history table.
delete older than	14	Delete trace statistics older than … days.

Job Summary

Purpose: The primary purpose of this monitoring job is to track and report the memory usage by each server process or session in an Oracle Database 10g and above.
Why: Monitoring memory usage per session is crucial as high memory consumption by individual sessions can lead to performance bottlenecks or even cause the server to run out of memory, potentially leading to server crashes or slowdowns. This job helps in identifying such sessions that consume excessive memory.
Manual checking: You can manually check memory usage per session by executing the following SQL commands:

SELECT t1.sid "SID",
       NVL(t2.username, 'background process') "Username",
       t2.machine "Machine",
       t2.program "Program",
       t2.status "Status",
       ROUND(SUM(t1.value)/1048576, 1) "Memory Usage",
       t3.sql_text "SQL Text"
  FROM v$sesstat t1, v$session t2, v$sqlarea t3
 WHERE t1.statistic# IN (20, 25) AND t1.sid = t2.sid
       AND t2.SQL_ADDRESS = t3.ADDRESS (+)
GROUP BY t1.sid, t2.username, t2.machine, t2.program, t2.status, t3.sql_text
HAVING SUM(t1.value)/1048576 > 8
ORDER BY "Memory Usage" DESC;

Implementation Details

The implementation involves creating a history table to store session memory usage details and executing a stored procedure to populate this table based on the sessions exceeding the configured warning and alarm thresholds for memory usage.
This task includes cleaning up old entries from the history table based on a configured parameter to ensure the database does not get overflowed with historical data.

Dependencies

The job requires:
A custom table named “dbw_user_mem_histr” to store history data.

Output Structure

The output of the job is presented in two main formats:
Real-time current top user memory usage.

Output Category	Description	Methodology
Current Usage	Details about current top sessions by memory usage.	Extracted live from standard Oracle performance views (v$sesstat, v$session, and v$sqlarea).
Historical Usage	Historical records of sessions that were top memory users.	Extracted from “dbw_user_mem_histr” which is populated by the stored procedure based on memory usage exceeding preset thresholds.

Alerts and Notifications

The job has built-in mechanisms to escalate alerts based on warning and alarm thresholds, ensuring that administrators can take timely action when a session’s memory usage crosses critical levels.

This configuration ensures thorough monitoring and manageability of memory usage per session in Oracle databases, aiding in maintaining overall system health and performance.