you are a new oracle dba and you would like to find the size of an oracle process. you already know that oracle starts lot of background processes when the database is started and whenever someone connects to the database a new session is created in the database which can be found in v$session table and a corresponding operating system process also exists which can be found in the v$process table. On your production database due to memory leaks your oracle process sizes could keep on increasing and increasing and you could get the ora-4030 error or you could find that oracle is occupying most of the resources on your server and your manager could ask you to check if there is any memory leak. so now you would like to know how to find the size of a process and how to determine whether there is a memory leak.
Below article explains in detail about how to find out the size of a oracle process by using operating system commands , which operating system commands can give you wrong information and which oracle dictionary views should you use to find out details about your process sizes etc. at the end you can also find examples on how you can set up trace for a process.
| UNIX: Determining the Size of an Oracle Process [ID 174555.1] | |||
|
|
|||
| Modified 24-JUL-2010 Type BULLETIN Status PUBLISHED | |||
***Checked for relevance on 25-Jul-2010***
Purpose
~~~~~~~
DBAs are often alarmed by the perceived size of an Oracle process.
This note describes the various aspects of measuring the actual size.
Scope & Application
~~~~~~~~~~~~~~~~~~~
It is intended to clarify the various measurements that can be obtained, and
how to proceed further.
Determining the Size of an Oracle Process:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INTRODUCTION
On UNIX platforms, the size of a process is often measured using operating
system utilities such as 'ps' or 'top'. Such utilities can imply alarmingly
large process sizes.
WHY CAN PROCESS SIZES BE REPORTED INCORRECTLY?
==============================================
The two main reason for incorrect reporting of process memory usage are:
inclusion of non-private (i.e. shared) memory in the process memory figure,
and the operating system not reclaiming freed memory. These topics are
covered in more detail below.
Inclusion of non-private memory
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A process in memory is composed of several parts:
shared memory (the SGA)
shared libraries (both public, and private)
private data (referred to as DATA, or heap)
the executable (referred to as TEXT)
N.B. The SGA and TEXT regions are shared by all Oracle processes. These are
mapped only once into memory, and not once per process. As such, these are not
part of the incremental cost of spawning a new Oracle process.
Unfortunately, commands such as 'ps' and 'top' include TEXT sizes when
measuring private data. Further, the SGA size can also be included, giving a
wildly inaccurate figure for the private data of a server process.
Some operating systems provide better tools for measuring process sizes. For
example, Solaris has /usr/proc/bin/pmap, and HP has glance (view the process'
virtual memory map) or the kernel debugger q4.
Here is an example of using 'ps', 'top' and 'pmap' on Solaris, against an
Oracle server process (pid 3254):
% ps -eafl | grep 3254
F S UID PID PPID C PRI NI ADDR SZ .... CMD
8 S usupport 3254 3253 0 55 20 62f4a0b8 18985 .... oracleV817
% top
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
3254 usupport 1 59 0 148M 124M sleep 0:00 0.05% oracle
% /usr/proc/bin/pmap -x 3254
3254: oracleV817 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Address Kbytes Resident Shared Private Permissions Mapped File
00010000 26744 9368 8416 952 read/exec oracle <--TEXT
01A3C000 272 272 208 64 read/write/exec oracle <--TEXT
01A80000 152 144 - 144 read/write/exec [ heap ] <--private DATA
80000000 114928 114928 - 114928 read/write/exec/shared [shmid=0x191] <--SGA
...<deleted some output here>....
-------- ------ ------ ------ ------
total Kb 148024 127424 10896 116528
From the above,
'ps' shows the SZ value as 18985 or approaching 19MB.
'top' shows the size as approx 148MB.
'pmap' shows the total size to be 148MB, but gives a breakdown by region:
The TEXT size is approx 28MB
DATA area is 150kB
SGA is approx 115MB. The shared memory ID is given as [shmid=0x191]
The shared memory segment identifier is 0x191=401.
This correlates with ipcs:
%ipcs -m
T ID KEY MODE OWNER GROUP
m 401 0xe265699 c --rw-r----- usupport dba
^^^
This highlights that the private data area of approx 150kB cannot be measured
accurately from standard operating system utilities such as 'ps' and 'top'
whereas 'pmap' allows a more accurate figure to be determined.
Operating system not reclaiming memory
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In general, after a process has freed some memory, it is not returned
immediately to the operating system's free pool. Instead, this memory is still
associated with the process, until such time that the operating system runs low
on free memory, and starts to take freed pages back. Consequently, operating
system utilities can report process sizes to be larger than they really are.
HOW MUCH MEMORY IS ORACLE ACTUALLY USING?
=========================================
From an Oracle perspective, a server process' private memory is composed of
several Oracle 'heaps'. In Oracle terminology, a heap is a managed area of
memory. To the operating system, this is just another piece of memory allocated
to an application. The heap names of interest are PGA and UGA. The purpose of
these heaps is unimportant in the context of this note.
The amount of memory that Oracle currently has, and has ever had in these heaps
is obtained from the following statistics:
select statistic#, name
from v$statname
where name like '%ga memory%';
15 session uga memory
16 session uga memory max
20 session pga memory
21 session pga memory max
Oracle 10G also includes:
SQL> select statistic#, name from v$statname where name like '%pga%';
STATISTIC# NAME
---------- ------------------
25 session pga memory
26 session pga memory max
To check the heap sizes for all sessions:
select value, n.name|| '('||s.statistic#||')' , sid
from v$sesstat s , v$statname n
where s.statistic# = n.statistic#
and n.name like '%ga memory%'
order by value;
A large PGA/UGA does not necessarily indicate a problem. For example, the
following init.ora parameters affect the size:
Parameter:SORT_AREA_SIZE
Parameter:SORT_AREA_RETAINED_SIZE
Parameter:HASH_AREA_SIZE
IS THERE A PROBLEM?
===================
Firstly, standard operating system utilities such as 'ps' and 'top' should not
be used to get absolute figures, but used instead to observe trends (for
example, is a process size increasing or decreasing), for the reasons
previously stated. A specialist utility such as 'pmap' should be used to get a
more accurate figure. Even this may be misleading, as the operating system may
not have reclaimed previously freed memory.
Always query the database, using a query similar to the v$sesstat SQL given
above, to determine how much memory Oracle thinks it is using.
If such a query shows a session's memory usage constantly increasing over time,
without leveling off, then there may be a leak of some description. For example,
a process may be allocating memory for a particular repeated operation, but not
freeing it in between. A leak would typically result in a large process size
that cannot be explained by the related init.ora parameter settings.
If a process size keeps growing, then it may eventually fail with an ORA-4030
"out of process memory when trying to allocate %s bytes" error if the operating
system is exhausted of memory, or the memory size hits some operating system
defined limit (such as maxdsiz on HP-UX).
To start diagnosing a problem with the process size, such as a suspected leak,
a heapdump of the offending process is required:
$ svrmgrl or sqlplus
SQL> connect internal (or '/ as sysdba')
SQL> oradebug setospid <pid>
SQL> oradebug unlimit
SQL> oradebug dump heapdump 5 <--this dumps PGA and UGA heaps
The following information should be provided to Oracle Support Services:
1. Identify the problem session
2. Run the following to demonstrate the memory growth:
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
select value, n.name|| '('||s.statistic#||')'
from v$sesstat s , v$statname n
where s.statistic# = n.statistic#
and n.name like '%ga memory%'
and sid= <sid of problem session>;
These should be run periodically, from near the start of the session, and
must show the increase in memory.
3. Once the memory usage starts to increase, get multiple heapdumps (say a
maximum of five), separated by a time period that will highlight the
increase. Note that all heapdumps will write to the same trace file in
the user_dump_dest (for non-background processes). This will potentially
lead to a large trace file, so be aware of this when deciding when to dump
the heaps, and how often.
4. An indication of what the client process is doing.
For example, Pro*Lang code
PL/SQL procedure
Related Documents
~~~~~~~~~~~~~~~~~
Note:17094.1 TECH: Unix Virtual Memory, Paging & Swapping explained
Note:2060096.6 MONITORING MEMORY USE
Note:61896.1 Virtual Memory on Solaris
Search Words:
~~~~~~~~~~~~~
ORA-04030
| Related
Products
Keywords
Errors
|
