Skip to content

How to find oracle process size – knowledge article for the new oracle dba




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


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition

Keywords


HEAP

Errors


MAXDSIZ; ORA-4030

 

Back to top

Rate this document 

Article Rating

Rate this document
Excellent
Good
Poor
 
Did this document help you?
Yes
No
Just browsing
 
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

 

  Comments

 
Cancel    

 

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.