oracle memory use

admin

How can I see which processes are using memory, from an O/S point of view?
Use the UNIX ps utility to see how much memory processes are actually consuming. For example to get the top 50 processes, sorted by memory size:

Solaris: ps -eo user,pid,ppid,vsz,rss,time,comm | sort +4 -5 -n -r | head -50
Linux:   ps aux | sort +5 -6 -n -r | head -50
AIX:     ps aux | sort +5 -6 -n -r | head -50
HP-UX:   ps -elf | sort +9 -10 -n -r | head -50

This ps output will list the top 50 memory consumers in descending order as seen by the OS. The memory sizes are not absolute, and we only use this output to get a relative size for comparison.

A sample output from a Linux system shows:

           PID                    RSS
ortest1  28748 2.4 50.4  300712 8279392 ? Ds 13:04 2:30 oracleTEST1 (LOCAL=NO)
ortest1  16625 0.0 50.3  280848 8261072 ? Ds Mar27 2:08 oracleTEST1 (LOCAL=NO)
ortest1  19891 0.0 50.2  319568 8257780 ? Ss Mar27 1:03 oracleTEST1 (LOCAL=NO)
ortest1  16664 0.0 50.2  324056 8256152 ? Ss Mar27 0:41 oracleTEST1 (LOCAL=NO)
ortest1    569 0.0 50.2  300028 8255480 ? Ss Mar27 1:11 oracleTEST1 (LOCAL=NO)
ortest1  26639 0.0 50.2  322684 8255032 ? Ss Mar27 0:48 oracleTEST1 (LOCAL=NO)
ortest1  16611 0.0 50.2  321608 8253936 ? Ss Mar27 2:10 oracleTEST1 (LOCAL=NO)
ortest1   7609 0.1 50.2  318360 8253040 ? Ss Mar27 2:11 oracleTEST1 (LOCAL=NO)
ortest1  16596 0.0 50.2  255000 8252428 ? Ss Mar27 2:10 oracleTEST1 (LOCAL=NO)
ortest1  12800 0.1 50.2  313172 8252324 ? Ss Mar27 2:31 oracleTEST1 (LOCAL=NO)
ortest1  19469 0.2 50.2  259144 8252320 ? Ss Mar27 4:13 oracleTEST1 (LOCAL=NO)
ortest1  16658 0.0 50.2  320484 8252160 ? Ss Mar27 0:51 oracleTEST1 (LOCAL=NO)
. . .


This sample report tells us that on this system Oracle processes are the top memory consumers.

How can I see which processes are using memory, from an Oracle point of view?
For this you can query V$PROCESS:

SET LINESIZE 120
SET PAGESIZE 120
COLUMN spid HEADING 'OSpid' FORMAT a8
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a8
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 99,999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 99,999,999,999
COLUMN username HEADING 'Oracle user' FORMAT a12
COLUMN osuser HEADING 'OS user' FORMAT a12
COLUMN program HEADING 'Program' FORMAT a20
SELECT p.spid,
       p.pid,
       s.sid,
       s.serial#,
       s.status,
       p.pga_alloc_mem,
       p.pga_used_mem,
       s.username,
       s.osuser,
       s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
ORDER BY p.pga_alloc_mem DESC;

This query will list processes and the amount of PGA allocated to them, in descending order of memory usage, as in:

OSpid    Orapid  Sess id Serial# Status   PGA alloc       PGA used        Oracle user  OS user      Program
-------- ------- ------- ------- -------- --------------- --------------- ------------ ------------ --------------------
16625        118    1875       7 ACTIVE        36,715,965      35,609,413 APPS         aptest1      JDBC Thin Client
22034        328    1667    4287 ACTIVE        33,635,773      27,922,125 APPS         aptest1      JDBC Thin Client
19891        371    1623      31 INACTIVE      30,686,653      26,436,021 APPS         aptest1      JDBC Thin Client
19476        512    1538    1448 INACTIVE      30,031,293      25,923,861 APPS         aptest1      JDBC Thin Client
16664        126    1869      31 INACTIVE      29,507,005      25,660,613 APPS         aptest1      JDBC Thin Client
19510        312    1683      33 INACTIVE      29,048,253      24,998,445 APPS         aptest1      JDBC Thin Client
7609         353    1692    3122 INACTIVE      28,655,037      24,164,101 APPS         aptest1      JDBC Thin Client
26639        386    1609      61 INACTIVE      28,523,965      23,437,949 APPS         aptest1      JDBC Thin Client
16658        124    1870       1 INACTIVE      28,458,429      24,178,893 APPS         aptest1      JDBC Thin Client
. . .

Next to this, summarize the total PGA usage:

SELECT SUM(pga_alloc_mem)/1024/1024 AS "Mbytes allocated", SUM(pga_used_mem)/1024/1024 AS "Mbytes used" FROM v$process;
Mbytes allocated Mbytes used
---------------- -----------
      5564.67496  4597.52962

This will give the total PGA currently allocated. This value plus the SGA size forms the total Oracle memory footprint.

How can I reconcile the O/S memory users to the Oracle Memory users?
The following areas can be checked to see if Oracle memory consumption is excessive and/or inefficiently used.

Note: the PID in the ps output relates to the OSPID column of V$PROCESS.


Are the highest O/S memory consumers also the highest PGA consumers?
Not by default. This must be investigated external to Oracle as well as in V$PGASTAT or V$PROCESS. The PGA is only part of the total Oracle memory consumption. Large Oracle memory consumption doesn't automatically mean large PGA consumption (take the SGA size into consideration as well).
Is a single Oracle process responsible for the majority of the memory allocation?
If a single process consumes excessive memory, analysis mechanisms such as heap dumps can be used to see if a bug situation is encountered.
To obtain a heap dump, obtain the OSpid for the offending process using the above queries, and then issue:
sqlplus /nolog
CONNECT / AS SYSDBA
ALTER SYSTEM SET max_dump_file_size=unlimited;
ALTER SYSTEM SET EVENTS '10235 trace name context forever, level 65536';
CONNECT / AS SYSDBA
oradebug setospid <enter OSpid here>
oradebug unlimit
oradebug dump errorstack 10
oradebug dump heapdump 536870917
oradebug tracefile_name -- This shows the location of the trace file generated
oradebug close_trace -- This closes the trace file
ALTER SYSTEM SET EVENTS '10235 trace name context off';
EXIT

Analyze the heap dump, or open a Service Request to continue the investigation.
Are there multiple Oracle processes consuming all memory?
If multiple Oracle processes consume all available memory, then it can be that we are just overwhelming memory. Check if there is a need for all currently running processes (e.g. check if JOB_QUEUE_PROCESSES or MIN_PARALLEL_SERVERS is not set to an excessive value, eating up valuable memory) and set any Oracle instance parameters to values needed for operating the instance.
Are there a large number of INACTIVE jobs in the PGA report?
This is indicating there is some job that is not logging off the database instance when the task is complete. In the above PGA output, this was the case.


Also compute total SGA and PGA allocations, and compare this to the physical memory size. Ideally all Oracle memory should fit in physical memory to prevent excessive swapping from occurring. For example, if an SGA of 6GB is defined, and the PGA allocations reach 4GB, and there is 12GB of physical memory installed in the server, then only 2GB of memory remains for use by the OS and other non-Oracle processes. This might be too small and as such investigation should be done on whether the SGA and/or PGAs are too large configured for the system (based on swap statistics, such as seen in e.g. vmstat).

What about PL/SQL?
When PL/SQL allocates memory for its collection types (VARRAYS, nested tables, etc.), this memory is external to the PGA work areas (such as sort area, hash area, etc.) and as such can exceed the PGA_AGGREGATE_TARGET set for the instance. As such, this memory can exhaust free memory on the server as well. This is something to be aware of.