How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)

Last updated on AUGUST 03, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 12.1.0.2 [Release 10.2 to 12.1]
Oracle Database - Standard Edition - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Information in this document applies to any platform.

Goal

How to use V$PROCESS_MEMORY and V$PROCESS_MEMORY_DETAIL to identify where the memory is growing.

In Oracle 10.2 and higher exist 2 new views that can be used to find where the memory continue to grow. This views can be used instead of heap dump to find where the memory is growing:

 - V$PROCESS_MEMORY:

     This view displays dynamic PGA memory usage by named component categories for each Oracle process. This view will contain up to six rows for each Oracle process, one row for:
      - Java
      - PL/SQL
      - OLAP
      - SQL
      - Freeable
      - Other

 - V$PROCESS_MEMORY_DETAIL
    Contain break down of memory allocation for each component.
    - To activate this view can one of following commands executed:
       SQL> alter session set events'immediate trace name PGA_DETAIL_GET level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG SETMYPID;
       SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>;
    - To remove all rows in the view run following command:
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms